Mysql 锁机制全面解析

并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，就会有并发控制的问题。
我们以 unix 系统的email box 为例。典型的 mbox 文件格式是非常简单的。一个mbox中所有的邮件都串行在一起，彼此首尾相连。这种格式对于读取和分析邮件信息非常友好，同时投递邮件也很容易，只要在文件末尾附加新的邮件内容即可。

那如果两个进程同时投递邮件会怎样？可想而知，如果不增加锁机制，邮件内容势必会乱序追加，这肯定是不合理的。
like this…

那如果一个进程正在读邮件，而另一个进程要删除邮件，又该怎么办？结果是不可预知的，可能是读取到错误的内容，也可能会报错退出。

当然涉及良好的邮箱投递系统会通过锁（lock）来防止上述问题的发生。

读写锁

那如果我们把数据库中的表类比成Unix 的 email box ，数据中的的一行数据比作一封邮件。就不难发现，数据库也会存在这样的并发问题。解决这类经典问题的方法就是并发控制，而加锁就是一种途径。

再回到我们的邮件系统遇到的问题上:

如果A进程发邮件的同时，B进程也要发邮件，怎么才能避免乱序追加呢。那就是加锁，在A进程发邮件的时候，给邮件系统加锁，禁止其他进程修改或查看邮件。只有在A进程处理完之后，其他进程才可以介入。这种加锁方式就是写锁，因为这种加锁方式是完全占有，排斥其他进程的任何操作，所以也叫排他锁。

如果A进程读邮件的同时，B进程又要修改邮件呢。那当然还是加锁了，A进程读邮件的时候，先加锁，但是这个锁是区别于写锁的，因为如果B进程也是来读邮件，是不会影响A的，但是要修改肯定是不行的。所以这种加锁方式就叫做读锁,因为这种加锁方式并非完全占有，可以跟其他进程共享读操作，所以也叫共享锁。

在实际的数据库系统中，每时每刻都在发生锁，当用户在修改某一部分数据时，Mysql会通过加锁来防止其他用户读取同一数据。

锁粒度

我们在来聊一下锁粒度的问题。所谓锁粒度，简单来理解就是锁定的数据范围。

一种提高并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是全部数据。更理想的方式是，只对会修改的数据便进行精确的锁定。

任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

那是不是在任何时候把锁粒度调到最精确就好呢？当然不是

系统需要花费资源时间来管理锁，如果系统把大量的精力放到锁上，那势必会对系统性能造成影响。

所谓的锁策略就是在锁的开销和数据的安全性之间需求平衡。

而Mysql提供了多种选择。每种存储引擎都可以实现自己的锁策略和锁粒度。将锁粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能，但同时会失去对另外一些应用场景的良好支持。

表锁

表锁是Mysql中最基本的锁策略，并且是开销最小的策略.

特点是：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低

一个用户在表进行写操作（插入、删除、更新等）前，需要先获得写锁，这会阻塞其他用户的所有读写操作。

只有没有写锁时，其他用户才能获取读锁，读锁之间是不相互阻塞的。

尽管存储引擎可以管理自己的锁，Mysql 本身还是会使用各种有效的表锁来实现不同的目的。例如，服务器会为诸如 ALTER TABLE 之类的语句使用表锁，而忽略存储引擎的锁机制。

行级锁

行级锁可以最大程度地支持并发处理，当然也带来了最大的锁开销。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

页级锁

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

不同存储引擎的锁粒度支持

• MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）
• BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁
• InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁

加锁问题排查

本节涉及的都是innode引擎
首先我们可以用 show processlist 查看当前是否有阻塞的任务。

在5.5中，information_schema库中增加了三个关于锁的表，可以用来排查加锁问题。
innodb_trx ## 当前运行的所有事务
innodb_locks ## 当前出现的锁
innodb_lock_waits ## 锁等待的对应关系

innodb_trx 当前运行的事务
这个表里记录了当前运行的事务详细信息。

mysql> use information_schema;
Database changed
mysql>
mysql> desc innodb_trx;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field                      | Type                | Null | Key | Default             | Extra | 
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id                     | varchar(18)         | NO   |     |                     |       |#事务ID
| trx_state                  | varchar(13)         | NO   |     |                     |       |#事务状态
| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |#事务开始时间
| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |#innodb_locks.lock_id
| trx_wait_started           | datetime            | YES  |     | NULL                |       |#事务开始等待的时间
| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |# 事务权重
| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |# 线程id
| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |# 具体SQL语句
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |#事务当前操作状态
| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |# 事务中有多少个表被使用
|
| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |# 事务拥有多少个锁
| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |# 
| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |# 事务锁住的内存大小（B）
| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |# 加锁的行数
| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |# 事务更改的行数
| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |#事务的隔离级别
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |#是否唯一性检查
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |#是否外键检查
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |
| trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |
| trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_is_read_only           | int(1)              | NO   |     | 0                   |       |
| trx_autocommit_non_locking | int(1)              | NO   |     | 0                   |       |
+----------------------------+---------------------+------+-----+---------------------+-------+
24 rows in set (0.01 sec)

---

innodb_locks 当前出现的锁
当出现锁等待的时候，这个表里才会有记录。

mysql> desc innodb_locks;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id     | varchar(81)         | NO   |     |         |       |#锁id
| lock_trx_id | varchar(18)         | NO   |     |         |       |#事务id
| lock_mode   | varchar(32)         | NO   |     |         |       |#锁模式
| lock_type   | varchar(32)         | NO   |     |         |       |#锁类型
| lock_table  | varchar(1024)       | NO   |     |         |       |#被加锁的表
| lock_index  | varchar(1024)       | YES  |     | NULL    |       |#被锁的索引
| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的表空间号
| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的页号
| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的记录号
| lock_data   | varchar(8192)       | YES  |     | NULL    |       |#被锁的数据
+-------------+---------------------+------+-----+---------+-------+
10 rows in set (0.01 sec)

---

innodb_lock_waits 所等待的对应关系
当出现锁等待的时候，我们可以从这个表里查到是哪个事务在持有锁，哪个事务在等待。

mysql> desc innodb_lock_waits;
+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO   |     |         |       |#请求锁的事务ID
| requested_lock_id | varchar(81) | NO   |     |         |       |#请求锁的锁ID
| blocking_trx_id   | varchar(18) | NO   |     |         |       |#当前拥有锁的事务ID
| blocking_lock_id  | varchar(81) | NO   |     |         |       |#当前拥有锁的锁ID
+-------------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

---

现在我们通过具体的案例来分析一下：
首先，我们建一张测试表:

CREATE TABLE `lock_test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `lenth` int(10) NOT NULL DEFAULT '-1' COMMENT '长度',
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT '书名',
  `auth` varchar(255) NOT NULL DEFAULT '' COMMENT '作者',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

在加几条测试数据

INSERT INTO `lock_test` (`id`, `lenth`, `name`, `auth`)
VALUES
	(1, 100, '平凡的世界', '陈忠实'),
	(2, 100, '解忧杂货店', '');

---

然后我们需要打开多个窗口来连接Mysql.
下面的我会用 connection_id来区分不同的窗口：

# connection_id 281
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update lock_test set lenth=200 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# connection_id 312
mysql> select * from  innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 5485848
                 trx_state: RUNNING
               trx_started: 2020-06-16 17:45:01
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 281  #事务执行的客户端线程id
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 360
           trx_rows_locked: 1		#当前事务锁了1行数据
         trx_rows_modified: 1    #当前事务更新了1行数据
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ  # 事务的隔离级别是可重复读
         trx_unique_checks: 1  
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

我们还能看到事务锁定的行数，更新的行数，以及事务隔离级别等信息，这对排查问题有很大帮助。

现在我们在新的窗口，修改同样的资源试一下。

#connection_id 282
mysql> update lock_test set lenth=500 where id=1;

这时候就阻塞住了，因为在 id 为 1 的数据的写锁被 281 持有，这期间是禁止其他任何读写操作的。

我们再来看下具体的加锁信息：

#connection_id  312
mysql>
mysql> select * from  innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 5485857
                 trx_state: LOCK WAIT  #只能等上一个事务释放锁才能继续执行
               trx_started: 2020-06-16 18:09:05   # 事务开始时间
     trx_requested_lock_id: 5485857:5668:3:4  # innode_locks 表的id
          trx_wait_started: 2020-06-16 18:09:05 # 事务开始等待的时间
                trx_weight: 2
       trx_mysql_thread_id: 282
                 trx_query: update lock_test set lenth=500 where id=1  # 还可以看到具体的sql哦
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 360
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 5485856
                 trx_state: RUNNING  #由于正在执行，是不允许其他的写操作介入的
               trx_started: 2020-06-16 18:09:02
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 281
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 360
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

由于前一个事务还在 running ，新进来的事务只能等待写锁释放。

mysql> select * from innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 5485857  # 请求的事务id
requested_lock_id: 5485857:5668:3:4  
  blocking_trx_id: 5485856   # 当前持有锁的事务id
 blocking_lock_id: 5485856:5668:3:4
1 row in set (0.00 sec)

可以看到锁等待的关系，到底是谁在等谁！

mysql> select * from innodb_locks\G;
*************************** 1. row ***************************
    lock_id: 5485857:5668:3:4
lock_trx_id: 5485857
  lock_mode: X
  lock_type: RECORD
 lock_table: `nc_collection`.`lock_test`
 lock_index: PRIMARY
 lock_space: 5668
  lock_page: 3
   lock_rec: 4
  lock_data: 1
*************************** 2. row ***************************
    lock_id: 5485856:5668:3:4
lock_trx_id: 5485856
  lock_mode: X
  lock_type: RECORD
 lock_table: `nc_collection`.`lock_test`
 lock_index: PRIMARY
 lock_space: 5668
  lock_page: 3
   lock_rec: 4
  lock_data: 1
2 rows in set (0.00 sec)