MySQL的InnoDB锁机制解说
发布时间：2022-01-16 00:18:22 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍MySQL的InnoDB锁机制介绍，在日常操作中，相信很多人在MySQL的InnoDB锁机制介绍问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答MySQL的InnoDB锁机制介绍的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
|
这篇文章主要介绍“MySQL的InnoDB锁机制介绍”，在日常操作中，相信很多人在MySQL的InnoDB锁机制介绍问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL的InnoDB锁机制介绍”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ 一 背景 MySQL锁机制是一个极其复杂的实现，为数据库并发访问和数据一致提供保障。这里仅仅针对MySQL访问数据的三种锁做介绍，加深自己对锁方面的掌握。 二 常见的锁机制 我们知道对于InnoDB存储引擎而言,MySQL 的行锁机制是通过在索引上加锁来锁定要目标数据行的。常见的有如下三种锁类型，本文未声明情况下都是在RR 事务隔离级别下的描述。 2.1 Record Locks 记录锁实际上是索引上的锁，锁定具体的一行或者多行记录。当表上没有创建索引时，InnoDB会创建一个隐含的聚族索引，并且使用该索引锁定数据。通常我们可以使用 show innodb status 看到行锁相关的信息。 2.2 Gap Locks 间隙锁是锁定具体的范围，但是不包含行锁本身。比如 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 主键/唯一键 “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: select * from tab where id=100 for update” 就是说当语句通过主键或者唯一键访问数据的时候，Innodb会使用Record lock锁住记录本身，而不是使用间隙锁锁定范围。 需要注意以下两种情况: 1 通过主键或则唯一索引访问不存在的值，也会产生GAP锁。 [session1] >begin; Query OK, 0 rows affected (0.00 sec) [session1] >select * from lck_primarkey where id=7 for update; Empty set (0.00 sec) [session2] >insert into lck_primarkey values(8,13); ^CCtrl-C -- sending "KILL QUERY 303042481" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_primarkey values(5,13); ^CCtrl-C -- sending "KILL QUERY 303042481" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_primarkey values(3,13); Query OK, 1 row affected (0.00 sec) [session2] >insert into lck_primarkey values(10,13); 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 非唯一键 CREATE TABLE `lck_secondkey` ( `id` int(11) NOT NULL, KEY `idx_id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into lck_secondkey values(2),(4),(9),(14) 会话1 [session1] >begin ; Query OK, 0 rows affected (0.00 sec) [session1] >select * from lck_secondkey; +----+ | id | +----+ | 2 | | 3 | | 4 | | 9 | | 14 | +----+ 5 rows in set (0.00 sec) [session1] >select * from lck_secondkey where id=9 for update; +----+ | id | +----+ | 9 | +----+ 1 row in set (0.00 sec) 会话2 [session2] >begin; Query OK, 0 rows affected (0.00 sec) [session2] >insert into lck_secondkey values(3); Query OK, 1 row affected (0.00 sec) [session2] >insert into lck_secondkey values(4); ^CCtrl-C -- sending "KILL QUERY 303040567" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_secondkey values(5); ^CCtrl-C -- sending "KILL QUERY 303040567" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_secondkey values(6); ^CCtrl-C -- sending "KILL QUERY 303040567" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_secondkey values(7); ^CCtrl-C -- sending "KILL QUERY 303040567" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_secondkey values(8); ^CCtrl-C -- sending "KILL QUERY 303040567" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_secondkey values(9); ^CCtrl-C -- sending "KILL QUERY 303040567" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_secondkey values(10); ^CCtrl-C -- sending "KILL QUERY 303040567" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_secondkey values(11); ^CCtrl-C -- sending "KILL QUERY 303040567" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_secondkey values(12); ^CCtrl-C -- sending "KILL QUERY 303040567" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_secondkey values(13); ^CCtrl-C -- sending "KILL QUERY 303040567" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_secondkey values(14); 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的值去可以插入有疑问？可以看接下来的例子 [session1] >select * from lck_primarkey; +----+-----+ | id | val | +----+-----+ | 2 | 3 | | 4 | 5 | | 9 | 8 | | 14 | 13 | +----+-----+ 4 rows in set (0.00 sec) [session1] >begin; Query OK, 0 rows affected (0.00 sec) [session1] >select * from lck_primarkey where val=8 for update; +----+-----+ | id | val | +----+-----+ | 9 | 8 | +----+-----+ 1 row in set (0.00 sec) 会话2 [session2] >begin; Query OK, 0 rows affected (0.00 sec) [session2] >insert into lck_primarkey values(3,5); Query OK, 1 row affected (0.00 sec) [session2] >insert into lck_primarkey values(15,13); Query OK, 1 row affected (0.00 sec) [session2] >select * from lck_primarkey; +----+-----+ | id | val | +----+-----+ | 2 | 3 | | 3 | 5 | | 4 | 5 | | 9 | 8 | | 14 | 13 | | 15 | 13 | +----+-----+ 6 rows in set (0.00 sec) [session2] >insert into lck_primarkey values(16,12); ^CCtrl-C -- sending "KILL QUERY 303040567" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_primarkey values(16,6); ^CCtrl-C -- sending "KILL QUERY 303040567" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted [session2] >insert into lck_primarkey values(16,5); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction [session2] > [session2] >insert into lck_primarkey values(1,5); 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值在被锁的范围内，故不能插入。 到此，关于“MySQL的InnoDB锁机制介绍”的学习就结束了，希望能够解决大家的疑惑。  （编辑：好传媒网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
