MySQL行级锁、表级锁、页级锁详细介绍

最新推荐文章于 2025-07-15 00:59:08 发布

Kevin_lew

最新推荐文章于 2025-07-15 00:59:08 发布

阅读量823

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： MySQL-行级锁 sql执行原理 innodb的MMVC

本文链接：https://blog.youkuaiyun.com/Kevin_lew/article/details/77968242

mysql 专栏收录该内容

3 篇文章

订阅专栏

MySQL行级锁、表级锁、页级锁详细介绍

页级（page-level）

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

表级（table-level）

直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作，可以同时读。

如果你是写锁，则其它进程则读也不允许，
表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。
使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

行级（row-level）

单独的一行记录加锁。

行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
使用行级锁定的主要是InnoDB存储引擎

总的来说，MySQL这3种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

注意：

InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，因为mysql优化器发现：即使使用了索引，还是要做全表扫描，故而放弃了索引，也就没有使用行锁，却使用了表锁。可以理解为MySQL不确定要扫描的范围时，优化器直接表锁，表锁所花的代价比行锁更优。以上问题即便你使用了 force index 强制索引，结果还是一样，永远都是表锁。**

 update table set num=1 where id>50

表级锁模式

表共享读锁（Table Read Lock）

不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

表独占写锁（Table Write Lock）

会阻塞其他用户对同一表的读和写操作；

行级锁定

共享锁

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁

排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁.
共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE;
排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE;

注：

对于排他锁大家的理解可能就有些差别，排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。不是锁住一行数据后，其他事务就不能读取和修改该行数据

select name from class where id in (select id from use where id <5) for update; 
select name from class where id in (1,2,3,4,5) for update;

大部分会认为结果一样没什么区别，其实差别大了，区别就是第一条 sql 语句会产生表锁，而第二个 sql 语句是行锁，为什么呢？因为第一个 sql 语句用了子查询外围查询故而没使用索引，导致表锁。

Mysql的工作原理

一个mysql的连接请求发起，创建mysql的连接（connectors）。
连接池（contention_pool）：接到请求后，会暂时保存到连接池，状态为等待队列，由管理器管理。
SQL接口（SQL Interface）：当请求进入处理队列时，将交由SQL接口处理，SQL接口接受到请求后，会先将请求进行hash处理并与缓存中的结果进行精准匹配，如果完全命中则直接从缓存读取数据返回给到程序并终止。否则继续将请求交给到解释器，
解释器（Parser）：会对sql语句的进行判断，错误则终止报错，否则会将其转化为数据结构，
解析器（Optimizer）：处理完的数据结构交由优化器，它会产生多种执行计划，最终选择它认为最优的执行计划，主要基于基于开销判断，包括索引、锁的粒度等等众多综合因素，选择整体开销最小的执行计划。
存储引擎（Engine）：最优的执行计划交由存储引擎处理，存储引擎将会到后段的存储设备上取得对应的数据并返回给到程序

MylSAM的读比Innodb高效

缓存的数据块较小：Innodb要缓存数据块而MylSAM只缓存索引块，对应带来的一些其它的减少；
定位比InnoDB要快：InnoDB寻址要映射到块，再到行，MylSAM记录的直接是文件的OFFSET。
维护较少：InnoDB还需要维护MVCC(Multi-Version Concurrency Control)多版本并发控制一致；即使没使用到，但他还是需要去检查和维护

MVCC最大的作用是实现了非阻塞的读操作,写操作也只锁定了必要的行，而每一个事务在启动的时候，都有一个唯一的递增的版本号。

InnoDB的MVCC是通过在每行记录后面添加两个额外隐藏的列来实现的,一列保存了行的创建时间,一列保存了行的过期时间(或删除时间).但它们都存储的是系统版本号。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统事务版本号。每个查询必须去检查每行数据的系统版本号与事务的系统版本号是否相同。