MySQL锁机制简介（行级锁定、页级锁定和表级锁定）

MySQL 与其他数据库在锁定机制方面最大的不同之处在于，对于不同的存储引擎支持不同的锁定机制。例如：

• InnoDB 存储引擎支持行级锁（row-level locking），也支持表级锁（table-level locking），默认的情况下是采用行级锁；
• MyISAM 和 MEMORY 存储引擎采用的是表级锁；
• BDB 存储引擎采用的是页面锁（page-level locking），同时也支持表级锁。

总的来说，MySQL 各存储引擎使用了三种级别的锁定机制：行级锁定、页级锁定和表级锁定。下面分析一下这三种级别的锁机制的特点。

MySQL行级锁定

行级锁最大的特点是锁定对象的颗粒度很小，发生锁定资源争用的概率也很小，能够给予应用程序尽可能大的并发处理能力，从而提高一些需要高并发的应用系统的整体性能。

虽然能够在并发处理能力上有较大的优势，但是行级锁也存在不少弊端。由于行级锁的颗粒度比较小，所以每次获取锁和释放锁会消耗比较大，加锁比较慢，很容易发生死锁。

行级锁定不是 MySQL 自己实现的锁定方式，而是由其他存储引擎所实现的，比如 InnoDB 存储引擎。InnoDB 实现了两种类型的行级锁，包括共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB 使用了两种内部使用的意向锁，也就是意向共享锁和意向排他锁。

各个锁的含义如下：

• 共享锁（S）：允许一个事务读取一行数据时，阻止其他事务读取相同数据的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务读取相同数据的共享锁和排他锁。
• 意向共享锁（IS）：事务打算给数据行加行共享锁。事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁。事务在给一个数据行加排他锁前必须先取得该表的IX锁。

上面这 4 种锁的共存逻辑关系如下表所示：

表：4 种锁的共存逻辑关系表

共享锁（S）	排他锁（X）	意向共享锁（IS）	意向排他锁（IX）
兼容	冲突	兼容	冲突
冲突	冲突	冲突	冲突
兼容	冲突	兼容	兼容
冲突	冲突	兼容	兼容

如果一个事务请求的锁模式与当前的锁模式兼容，InnoDB 就将请求的锁授予该事务；如果两者不兼容，那么该事务就要等待锁释放。

意向锁是 InnoDB 存储引擎自动加的，对于普通 SELECT 语句，InnoDB 不会加任何锁，对于 INSERT、UPDATE、DELETE 语句，InnoDB 会自动给涉及的数据加排他锁，InnoDB 可以通过以下语句添加共享锁和排他锁。

1) 添加共享锁（S）的语句如下：

SELECT * FROM table_name WHERE … LOCK IN SHARE MODE

2) 添加排他锁（X）的语句如下：

SELECT * FROM table_name WHERE … FOR UPDATE

共享锁和排他锁的详细用法将在后续章节详细讲解。

MySQL表级锁定

与行级锁不同的是，表级锁的锁定机制的颗粒度最大，该锁定机制的最大特点是系统开销比较小，由于实现逻辑非常简单，所以带来的系统的负面影响也最小。由于表级锁一次性将整个表锁定，因此可以很好地避免死锁的问题。

同时，表级锁定机制也存在一定的缺陷，由于表级锁的锁定机制颗粒很大，所以发生锁冲突的概率也最高，表级锁定机制下并发度也最低。

MySQL 数据库的表级锁定主要分为两种类型，一种是读锁定，另一种是写锁定。MySQL 数据库提供了 4 种队列来维护这两种锁，这 4 种队列间接地说明了数据库表级锁的四种状态，这 4 种队列如下：

• Current read lock queue (lock read)；
• Padding read lock queue (lock read wait)；
• Current write lock queue (lock write)；
• Padding write lock queue (lock write wait)。

其中 Current read lock queue 中存放的是当前持有读锁的所有线程，而正在等待资源的信息则存放在 Padding read lock queue 中。

同样，Current write lock queue 中存放的是当前持有写锁的所有线程，而正在等待对资源写操作的信息则存放在 Padding write lock queue中。

MySQL 内部实现读锁和写锁有多达 11 种具体的锁定类型，由系统中一个枚举类型变量（thr_lock_type）定义，具体各种锁定类型如下表所示。

表：各种锁定类型的含义

锁定类型	含义
IGNORE	当发生锁请求的时候内部交互使用，在锁定结构和队列中并不会有任何信息存储
UNLOCK	释放锁定请求的交互用锁类型
READ	普通读锁定
WRITE	普通写锁定
READ_WITH_SHARED_LOCKS	在 InnoDB 中使用到，语法为：SELECT...LOCK IN SHARE MODE
READ_HIGH_PRIORITY	高优先级读锁定
READ_NO_INSERT	不允许 Concurrent Insert 的锁定
WRITE_ALLOW_WRITE	这个类型实际上就是由存储引擎自行处理锁定的时候，MySQL 允许其他线程再获取读或者写锁定，因为即使资源冲突，存储引擎自行处理这种锁定发生在对表 DDL 操作的时候，MySQL 可以允许其他线程获取读锁定，因为 MySQL 是通过重建整个表然后再 RENAME 而实现该功能的，所在整个过程中表依然可以提供读服务
WRITE_ALLOW_READ	正在进行 Concurrent Insert 时锁使用的锁定方式，该锁定进行的时候，除了 READ_NO_INSERT 之外的其他任何读锁定请求都不会被阻塞
WRITE_CONCURRENT_INSERT	在使用 INSERT DELAYED 时的锁定类型
WRITE_DELAYED	声明的低级别锁定方式，通过设置 LOW_PRIORITY_UPDATE=1 而产生
WRITE_ONLY	当在操作过程中某个锁定异常中断之后，系统内部需要进行 CLOSE TABLE 操作，在这个过程中出现的锁定类型就是 WRITE_ONLY

对于 MySQL 数据库读锁和写锁的加锁方式，通常使用 LOCK TABLE 和 UNLOCK TABLE 实现对表的加锁和解锁，下表是一个获得表锁和释放表锁的详细过程。

表：一个获得表锁和释放表锁的例子

Session1		Session2
创建数据表 lock_table_test 并插入演示数据，命令如下： mysql>create table lock_table_test( id int, data varchar(20)); mysql>insert into lock_table_test values(1,'t'); 获得表 lock_table_test 的 READ 锁，命令如下： mysql> lock table lock_table_test read; Query OK, 0 rows affected (0.00 sec)
当前 Session1 可以查询出该表的记录，命令如下： mysql> select * from lock_table_test limit 0,1; +----+------+ | id | data | +----+------+ | 1 | t | +----+------+ 1 row in set (0.00 sec)		Session2 也查询出该表的记录，命令如下： mysql> select *from lock_table_test limit 0,1; +----+------+ | id | data | +----+------+ | 1 | t | +----+------+ 1 row in set (0.00 sec)
		Session2 更新锁定表将会发生等待以获得锁，命令如下： mysql> update lock_table_test set data='ttest' where id = 1; 等待
释放锁，命令如下： mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)		等待
		Session2 获得锁，更新操作执行完成，命令如下： mysql> update lock_table_test set data='ttest' where id = 1; Query OK, 1 rows affected (1 min 1.77 sec) Rows matched: 1 Changed: 1 WARNINGS: 0

MySQL页级锁定

页级锁定在 MySQL 中是比较特殊的一种锁定机制，页级锁定的特点是颗粒度介于行级锁定与表级锁定之间，所以获取锁定所需要的资源开销，以及锁提供的并发处理的能力也介于表级锁定和行级锁定之间。

在数据库实现资源锁定的过程中，锁定机制的粒度越小，数据库实现的算法越复杂，数据库所消耗的内存也越大。不过，随着锁机制粒度越来越小，应用的并发发生锁等待的机率也越来越小，系统整体性能也随之增高。

MySQL 使用写队列和读队列来完成数据库的读和写操作，所以说，MySQL 数据库存在读锁和写锁的概念：

• 对于写锁而言，如果表没有加入写锁，那么对其表加写锁；如果表已经加了写锁，此时会将写操作的请求放入写锁的队列中；
• 对于读锁而言，如果没有加入读锁，那么请求会加入一个读操作的锁，其他读操作的请求会放到读锁的队列中。

下面通过一个简单的例子来说明读写操作，具体操作步骤如下：

1) 首先创建表 content 并插入数据，语句如下：

mysql> create table content(id int,content varchar(20));
Query OK, 0 rows affected (0.53 sec)
mysql> insert into content values(1,'wangfei');

2) 向表 content 里面添加大量的数据，数据越多，效果越明显。重复多次执行以下语句即可：

mysql> insert into content select * from content;
Query OK, 16384 rows affected (0.06 sec)
Records: 16384  Duplicates: 0  Warnings: 0

mysql> insert into content select * from content;
Query OK, 32768 rows affected (0.12 sec)
Records: 32768  Duplicates: 0  Warnings: 0

3) 此时，准备工作已经完成，可以根据下表所示运行读写操作，理解 MySQL 读写队列运行的过程。

表：运行 MySQL 读写队列的过程

Session1	Session2
mysql>select count(*) from content;
	此时执行: mysql> update content set content = 'test2' where id = 1;
	等待
查询结束
	mysql> update content set content = 'test2' where id = 1; Query OK, 0 rows affected (5.16 sec) Rows matched: 1048576 Changed: 0 WARNINGS: 0
Session1 关闭
	mysql> update content set content = 'test2' where id = 1; Query OK, 0 rows affected (3.14 sec) Rows matched: 1048576 Changed: 0 WARNINGS: 0

• 对于 Session1，此时表没有加锁，那么此时 Session1 操作会对表加上一个读锁；
• 对于 Session2，此时表 content 因为已经加上了读锁，所以会将 update 请求放到锁定队列中。

从上述特点可见，很难笼统地说哪种锁定机制好，只能根据具体应用的特点来选择哪种锁定机制更合适。

仅从锁的角度来看，表级锁更适合以查询为主，只有少量按索引条件更新数据的应用。而行级锁更适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。