关于 MySQL InnoDB锁机制

一  背景
    MySQL锁机制是一个极其复杂的实现,为数据库并发访问和数据一致提供保障。这里仅仅针对MySQL访问数据的三种锁做介绍,加深自己对锁方面的掌握。
二 常见的锁机制
我们知道对于InnoDB存储引擎而言,MySQL 的行锁机制是通过在索引上加锁来锁定要目标数据行的。常见的有如下三种锁类型,本文未声明情况下都是在RR 事务隔离级别下的描述。
2.1 Record Locks 
  记录锁实际上是索引上的锁,锁定具体的一行或者多行记录。当表上没有创建索引时,InnoDB会创建一个隐含的聚族索引,并且使用该索引锁定数据。通常我们可以使用 show innodb status 看到行锁相关的信息。
2.2 Gap Locks
 间隙锁是锁定具体的范围,但是不包含行锁本身。比如

  1. select * from tab where id>10 and id<20;

RR事务隔离级别下会锁定10-20之间的记录,不允许类似15这样的值插入到表里,以便消除“幻读”带来的影响。间隙锁的跨度可以是1条记录(Record low就可以认为是一个特殊的间隙锁 ,多行,或者为空。当访问的字段是唯一键/主键时,间隙锁会降级为Record lock。RR事务隔离级别下访问一个空行 ,也会有间隙锁,后续会举例子说明。
我们可以通过将事务隔离级别调整为RC 模式或者设置innodb_locks_unsafe_for_binlog=1 (该参数已经废弃)来禁用Gap锁。

2.3 Next-Key Locks
  是Record Lock+Gap Locks,锁定一个范围并且包含索引本身。例如索引值包含 2,4,9,14 四个值,其gap锁的区间如下:
  (-∞,2],(2,4],(4,9],(9,14],(14,+∞)
本文着重从主键,唯一键、非唯一索引,不存在值访问四个方面来阐述RR模式下锁的表现。
三 测试案例
3.1 主键/唯一键 

  1. CREATE TABLE `lck_primarkey` (
  2.   `id` int(11) NOT NULL,
  3.    val int(11) not null default 0,
  4.   primary key (`id`),
  5.   key idx_val(val)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  7. insert into lck_primarkey values(2,3),(4,5),(9,8),(14,13)

会话1 

  1. [session1] >select * from lck_primarkey;
  2. +----+-----+
  3. | id | val |
  4. +----+-----+
  5. | 2 | 3 |
  6. | 4 | 5 |
  7. | 9 | 8 |
  8. | 14 | 13 |
  9. +----+-----+
  10. 4 rows in set (0.00 sec)
  11. [session1] >begin;
  12. Query OK, 0 rows affected (0.00 sec)
  13. [session1] >select * from lck_primarkey where id=9 for update;
  14. +----+-----+
  15. | id | val |
  16. +----+-----+
  17. | 9 | 8 |
  18. +----+-----+
  19. 1 row in set (0.00 sec)

会话2 

  1. [session2] >begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3. [session2] >insert into lck_primarkey values(7,6);
  4. Query OK, 1 row affected (0.00 sec)
  5. [session2] >insert into lck_primarkey values(5,5);
  6. Query OK, 1 row affected (0.00 sec)
  7. [session2] >insert into lck_primarkey values(13,13);
  8. Query OK, 1 row affected (0.00 sec)
  9. [session2] >insert into lck_primarkey values(10,9);
  10. Query OK, 1 row affected (0.00 sec)

分析
   从例子看,当访问表的where字段是主键或者唯一键的时候,session2中的插入操作并未被 session1 中的id=8 影响。官方表述

  1. “Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:
  2.    select * from tab where id=100 for update”
  3. 就是说当语句通过主键或者唯一键访问数据的时候,Innodb会使用Record lock锁住记录本身,而不是使用间隙锁锁定范围。

需要注意以下两种情况:
1 通过主键或则唯一索引访问不存在的值,也会产生GAP锁。

  1. [session1] >begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3. [session1] >select * from lck_primarkey where id=7 for update;
  4. Empty set (0.00 sec)
  5. [session2] >insert into lck_primarkey values(8,13);
  6. ^CCtrl-C -- sending "KILL QUERY 303042481" to server ...
  7. Ctrl-C -- query aborted.
  8. ERROR 1317 (70100): Query execution was interrupted
  9. [session2] >insert into lck_primarkey values(5,13);
  10. ^CCtrl-C -- sending "KILL QUERY 303042481" to server ...
  11. Ctrl-C -- query aborted.
  12. ERROR 1317 (70100): Query execution was interrupted
  13. [session2] >insert into lck_primarkey values(3,13);
  14. Query OK, 1 row affected (0.00 sec)
  15. [session2] >insert into lck_primarkey values(10,13);
  16. Query OK, 1 row affected (0.00 sec)

2 通过唯一索引中的一部分字段来访问数据,比如unique key(a,b,c) ,select * from tab where a=x and b=y; 读者朋友可以自己做这个例子。

3.2 非唯一键

  1. CREATE TABLE `lck_secondkey` (
  2.   `id` int(11) NOT NULL,
  3.    KEY `idx_id` (`id`)
  4. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  5. insert into lck_secondkey values(2),(4),(9),(14)

会话1

  1. [session1] >begin ;
  2. Query OK, 0 rows affected (0.00 sec)
  3. [session1] >select * from lck_secondkey;
  4. +----+
  5. | id |
  6. +----+
  7. | 2 |
  8. | 3 |
  9. | 4 |
  10. | 9 |
  11. | 14 |
  12. +----+
  13. 5 rows in set (0.00 sec)
  14. [session1] >select * from lck_secondkey where id=9 for update;
  15. +----+
  16. | id |
  17. +----+
  18. | 9 |
  19. +----+
  20. 1 row in set (0.00 sec)

会话2

  1. [session2] >begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3. [session2] >insert into lck_secondkey values(3);
  4. Query OK, 1 row affected (0.00 sec)
  5. [session2] >insert into lck_secondkey values(4);
  6. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  7. Ctrl-C -- query aborted.
  8. ERROR 1317 (70100): Query execution was interrupted
  9. [session2] >insert into lck_secondkey values(5);
  10. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  11. Ctrl-C -- query aborted.
  12. ERROR 1317 (70100): Query execution was interrupted
  13. [session2] >insert into lck_secondkey values(6);
  14. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  15. Ctrl-C -- query aborted.
  16. ERROR 1317 (70100): Query execution was interrupted
  17. [session2] >insert into lck_secondkey values(7);
  18. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  19. Ctrl-C -- query aborted.
  20. ERROR 1317 (70100): Query execution was interrupted
  21. [session2] >insert into lck_secondkey values(8);
  22. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  23. Ctrl-C -- query aborted.
  24. ERROR 1317 (70100): Query execution was interrupted
  25. [session2] >insert into lck_secondkey values(9);
  26. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  27. Ctrl-C -- query aborted.
  28. ERROR 1317 (70100): Query execution was interrupted
  29. [session2] >insert into lck_secondkey values(10);
  30. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  31. Ctrl-C -- query aborted.
  32. ERROR 1317 (70100): Query execution was interrupted
  33. [session2] >insert into lck_secondkey values(11);
  34. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  35. Ctrl-C -- query aborted.
  36. ERROR 1317 (70100): Query execution was interrupted
  37. [session2] >insert into lck_secondkey values(12);
  38. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  39. Ctrl-C -- query aborted.
  40. ERROR 1317 (70100): Query execution was interrupted
  41. [session2] >insert into lck_secondkey values(13);
  42. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  43. Ctrl-C -- query aborted.
  44. ERROR 1317 (70100): Query execution was interrupted
  45. [session2] >insert into lck_secondkey values(14);
  46. Query OK, 1 row affected (0.00 sec)

分析
  事务1 对id=9进行for update 访问,session2 插入[4,13]的值都是失败的。根据MySQL的锁原理,Innodb 范围索引或者表是通过Next-key locks 算法,RR事务隔离级别下,通过非唯一索引访问数据行并不是锁定唯一的行,而是一个范围。从例子上可以看出来MySQL对 [4,9] 和(9,14]之间的记录加上了锁,防止其他事务对4-14范围中的值进行修改。可能有读者对其中 id=4 不能修改,但是id=14的值去可以插入有疑问?可以看接下来的例子

  1. [session1] >select * from lck_primarkey;
  2. +----+-----+
  3. | id | val |
  4. +----+-----+
  5. | 2 | 3 |
  6. | 4 | 5 |
  7. | 9 | 8 |
  8. | 14 | 13 |
  9. +----+-----+
  10. 4 rows in set (0.00 sec)
  11. [session1] >begin;
  12. Query OK, 0 rows affected (0.00 sec)
  13. [session1] >select * from lck_primarkey where val=8 for update;
  14. +----+-----+
  15. | id | val |
  16. +----+-----+
  17. | 9 | 8 |
  18. +----+-----+
  19. 1 row in set (0.00 sec)

会话2

  1. [session2] >begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3. [session2] >insert into lck_primarkey values(3,5);
  4. Query OK, 1 row affected (0.00 sec)
  5. [session2] >insert into lck_primarkey values(15,13);
  6. Query OK, 1 row affected (0.00 sec)
  7. [session2] >select * from lck_primarkey;
  8. +----+-----+
  9. | id | val |
  10. +----+-----+
  11. | 2 | 3 |
  12. | 3 | 5 |
  13. | 4 | 5 |
  14. | 9 | 8 |
  15. | 14 | 13 |
  16. | 15 | 13 |
  17. +----+-----+
  18. 6 rows in set (0.00 sec)
  19. [session2] >insert into lck_primarkey values(16,12);
  20. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  21. Ctrl-C -- query aborted.
  22. ERROR 1317 (70100): Query execution was interrupted
  23. [session2] >insert into lck_primarkey values(16,6);
  24. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  25. Ctrl-C -- query aborted.
  26. ERROR 1317 (70100): Query execution was interrupted
  27. [session2] >insert into lck_primarkey values(16,5);
  28. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  29. [session2] >
  30. [session2] >insert into lck_primarkey values(1,5);
  31. Query OK, 1 row affected (0.00 sec)

分析
   因为session1 对非唯一键val=8 加上了gap锁 [4,5] -[14,13],非此区间的记录都可以插入表中。记录(1,5),(15,13)不在此gap锁区间,记录(16,12),(16,6),(16,5)中的val值在被锁的范围内,故不能插入。
四  总结
   写本文的目的主要是在于温故而知新,侧重于温故。本文着重介绍了三种锁,其实还有两种锁Insert Intention Locks和AUTO-INC Locks 留作后面继续分析。

相关推荐