mysql 各种常见的锁_mysql的锁

数据库锁是用来在并发时控制不同资源的访问策略。锁的分类可以从不同的角度划分有很多种不同的锁。

1、按功能划分

锁按功能划分大致分为两种共享锁(Shared Locks)和排它锁(Exclusive Locks)。共享锁也称为S锁、读锁。排它锁也称为写锁，X锁。

共享锁允许事务读取一行数据。排它锁允许事务更新或删除一行记录。共享锁多个事务可以同时获得，但是一个事务如果想获取行上的排它锁必须要等待其它事务所有锁释放，包括共享锁和排它锁。

2、按控制粒度范围划分

全局锁

全局锁是对整个数据库加锁，一般在数据库备份或恢复时候为了保持数据一致性进行全局锁控制。

表锁

表锁分两种，一种是显示的使用lock tables语句进行表的锁定，另一种是原数据锁(metadata lock)。

显示表锁

锁定表：LOCK TABLES 表名 READ/WRITE; 可以加读锁也可以加写锁。

查看当前表锁：show open TABLES WHERE in_use>0;

释放表锁：UNLOCK TABLES; 会释放当前session锁持有的所有的表锁。

另外在当前session客户端自动端口时也会释放所有的表锁。表锁不仅会影响其它会话对当前表的操作，也影响当前会话对表的操作。

元数据锁(metadata lock)

实际上原数据锁不仅针对表，对数据库中所有对象包括表、schema，存储过程、表空间等。这是为了保护数据的正确性和一致性自动在访问表时自动加上的。

当前数据库中的metadata lock可以通过performance_schema.metadata_locks表来查看。

演示一下元数据锁：

T1时刻开启一个事务，查询表test

查看表test上的metadata lock信息

mysql> SELECT * FROM performance_schema.metadata_locks WHERE object_name='test' \G;
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: db_test
          OBJECT_NAME: test
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281458333731312
            LOCK_TYPE: SHARED_READ
            LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:5947
      OWNER_THREAD_ID: 70
       OWNER_EVENT_ID: 23
1
2
3
4
5
6
7
8
9
10
11
12

这个时候看到就已经对表test加上了metadata lock。

T2时刻session2要对表test进行修改，这里就会阻塞等待session1释放metadata lock。这样能保证session1读取数据的一致性，要不然我当前正在读，你直接把表结构都改了，我还操作个锤子。

T3时刻，session1提交事务，释放metadata lock。session2同时也执行成功。

metadata_lock是S锁，具备S锁特性，可以同时多个事务同时加的。X锁必须要等待所有的S锁释放才可以。所以在我们的表设计时候，特别是核心表尽量的考虑充分，设置可以设计几个冗余字段用来业务扩展，而不是线上该表结构。

意向锁(Intention Locks)

意向锁它表示事务以后对表中的某一行需要哪种类型的锁(X锁还是S锁)。也就是如果事务要获取行锁，首先需要获取表对应类型的意向锁。意向锁分两种：

• 意向共享锁（IS锁）：表示事务打算在表某些行上获取共享锁（读取操作），但并不排斥其他事务也获取共享锁。多个事务可以同时获取意向共享锁，而不会互相阻塞。
• 意向排他锁（IX锁）：表示事务打算在表某些行上获取排他锁（写入操作），但并不排斥其他事务获取共享锁。多个事务可以同时获取意向排他锁，而不会互相阻塞。

例如，SELECT…FOR SHARE设置IS锁，SELECT…FOR UPDATE设置一个IX锁。

行锁

行锁表示在表中某一行记录上加的锁。在5.x版本使用information_schema.innodb_locks。在8中使用performance_schema.data_locks表可以查看当前行锁信息。这里使用data_locks表

data_locks表几个重要字段：

按照锁定行范围分以下几种：

记录锁（Record Locks）

记录锁是在一条索引记录上的锁，也称为索引记录锁。阻止其它事务对当前索引记录进行修改和删除。在事务结束时自动释放。

如下加行锁

>start transaction;
>select * from t1 where id=10 for update;
1
2

查看锁信息

mysql> SELECT engine_transaction_id,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks where object_name='account' \G;
*************************** 1. row ***************************
engine_transaction_id: 12594
          object_name: account
           index_name: NULL
            lock_type: TABLE
            lock_mode: IX
          lock_status: GRANTED
            lock_data: NULL
*************************** 2. row ***************************
engine_transaction_id: 12594
          object_name: account
           index_name: PRIMARY
            lock_type: RECORD
            lock_mode: X,REC_NOT_GAP
          lock_status: GRANTED
            lock_data: 1
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

这里看到在表上加了IX锁，在行上加了记录锁（X,REC_NOT_GAP），锁的数据是主键=1。

同理执行更新

>start transaction;
> update account set balance=1 where id=1;
1
2

加锁信息和上面是一样的。

间隙锁（Gap Locks）

间隙锁是针对数据行之间的间隙进行加锁,锁的是两个数据之间的空隙，用于防止其他事务在该范围内插入新的数据，从而避免幻读的问题。间隙锁的加锁时机是在执行范围查询或者插入数据时。

一个间隙范围可以包含单个索引值，多个索引值甚至可以是空值。间隔锁是性能和并发性之间的一种权衡。只有在重复读这种隔离级别模式下才有效。

如上将会锁住id 在10~20之间的所有行记录，即使某个记录值不存在。如此时另一个事务尝试往表中插入id=15的记录，是会被阻塞等待的。间隙锁在一定程度上解决了幻读问题。

如果查询条件是在唯一索引列上的唯一一个值是不会使用间隙锁的，只会使用行锁。

Next-Key Locks

Next-Key Locks是行锁和间隙锁的结合。
next-key怎么理解呢，就是当前条件值找下一个表中存在的值m。锁定这个m对应往前一个区间：（上一个数据库值，m]。
InnoDB执行行级锁的方式是，当它搜索或扫描一个表索引时，它会在遇到的索引记录上设置共享锁或排他，这就是行锁。索引记录上的next-key锁还会影响该索引记录之前的“间隙”。也就是说，next-key锁是行锁加上索引记录前面的间隙锁。如果一个会话对索引中的记录R具有共享锁或排他锁，则另一个会话不能在索引顺序R之前的空白中插入新的索引记录。

假设索引包含值10、11、13和20。此索引可能的next-key locks覆盖以下区间，其中圆括号表示不包含区间端点，方括号表示包含端点（前开后闭）:

(负无穷, 10]
(10, 11]
(11, 13]
(13, 20]
(20, 上限)
1
2
3
4
5

最后一个区间上限表示一个不存在的值，这个区间表示大于当前最大值的间隙。

插入意向锁（Insert Intention Locks）

插入意向锁是在insert操作是产生的一种特殊间隙锁。当多个事务在同一区间插入位置不同的多条数据时，事务之间不需要互相等待。假设存在两条值分别为 4 和 7 的记录，两个不同的事务分别试图插入值为 5 和 6 的两条记录，每个事务在获取插入行上独占的X锁前，都会获取（4，7）之间的间隙锁，但是因为数据行之间并不冲突，所以两个事务之间并不会产生冲突,不会有锁等待。

行锁综合实例

建一个测试表test有以下数据,其中id是主键。

mysql> select * from test;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  0 |    0 |    0 |
| 10 |   10 |   10 |
| 20 |   20 |   20 |
| 30 |   30 |   30 |
| 40 |   40 |   40

1
2
3
4
5
6
7
8
9
10

session1

执行以下语句：

>start transaction;
mysql> select * from test where id between 10 and 20 for update;
+----+------+------+
| id | a    | b    |
+----+------+------+
| 10 |   10 |   10 |
| 20 |   20 |   20 |
1
2
3
4
5
6
7

这时候查看锁信息

mysql> SELECT engine_transaction_id,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks where object_name='test' \G;
*************************** 1. row ***************************
engine_transaction_id: 12622
          object_name: test
           index_name: NULL
            lock_type: TABLE
            lock_mode: IX
          lock_status: GRANTED
            lock_data: NULL
*************************** 2. row ***************************
engine_transaction_id: 12622
          object_name: test
           index_name: PRIMARY
            lock_type: RECORD
            lock_mode: X,REC_NOT_GAP
          lock_status: GRANTED
            lock_data: 10
*************************** 3. row ***************************
engine_transaction_id: 12622
          object_name: test
           index_name: PRIMARY
            lock_type: RECORD
            lock_mode: X
          lock_status: GRANTED
            lock_data: 20
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

1、首先还是有一个IX表锁

2、然后有一个X,REC_NOT_GAP的行锁，数据是主键=1。

3、还有一个X锁（Next-Key Locks），锁的数据是（10，20]区间

启动session2

尝试插入一个主键为15的记录

>start transaction;
>insert into test(id,a,b) values(15,15,15);
1
2

这个时候会看到插入会被阻塞，然后观察表test上的锁信息：这里只看session2新加的锁信息，session1还是上面的三个。

*************************** 1. row ***************************
engine_transaction_id: 12626
          object_name: test
           index_name: NULL
            lock_type: TABLE
            lock_mode: IX
          lock_status: GRANTED
            lock_data: NULL
*************************** 2. row ***************************
engine_transaction_id: 12626
          object_name: test
           index_name: PRIMARY
            lock_type: RECORD
            lock_mode: X,GAP,INSERT_INTENTION
          lock_status: WAITING
            lock_data: 20
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

这里看到表加IX成功，但是插入语句还会添加间隙锁（X,GAP）和插入意向锁（INSERT_INTENTION）。但是session1已经在（10，20]这个区间加Next-Key Locks锁了，两个锁都是写锁是互斥的，会一直等待，如果获取锁失败，最后插入会报ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction错误。

另外有兴趣的可以观察下以下场景，两个session前后插入相同主键记录值，观察插入意向锁的变化。