关于 MySQL InnoDB锁机制

2017-01-10 10:03:49来源:作者:Linux公社人点击

一 背景

MySQL锁机制是一个极其复杂的实现,为数据库并发访问和数据一致提供保障。这里仅仅针对MySQL访问数据的三种锁做介绍,加深自己对锁方面的掌握。

二 常见的锁机制

我们知道对于InnoDB存储引擎而言,MySQL 的行锁机制是通过在索引上加锁来锁定要目标数据行的。常见的有如下三种锁类型,本文未声明情况下都是在RR 事务隔离级别下的描述。

2.1 Record Locks

记录锁实际上是索引上的锁,锁定具体的一行或者多行记录。当表上没有创建索引时,InnoDB会创建一个隐含的聚族索引,并且使用该索引锁定数据。通常我们可以使用 show innodb status 看到行锁相关的信息。

2.2 Gap Locks

间隙锁是锁定具体的范围,但是不包含行锁本身。比如

select *from tab where id>10andid<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 主键/唯一键

CREATE TABLE `lck_primarkey` ( `id` int (11 ) NOTNULL , val int (11 ) notnull default 0 , primary key ( `id` ) , key idx_val (val ) )ENGINE =InnoDB DEFAULT CHARSET =utf8 ; insert intolck_primarkey values (2 ,3 ) , (4 ,5 ) , (9 ,8 ) , (14 ,13 )

会话1

[session1 ] >select *from lck_primarkey ; + - - - - + - - - - - + |id |val | + - - - - + - - - - - + |2 |3 | |4 |5 | |9 |8 | |14 |13 | + - - - - + - - - - - + 4 rows inset (0 .00 sec ) [session1 ] >begin ; Query OK ,0 rows affected (0 .00 sec ) [session1 ] >select *from lck_primarkey where id =9 forupdate ; + - - - - + - - - - - + |id |val | + - - - - + - - - - - + |9 |8 | + - - - - + - - - - - + 1 row inset (0 .00 sec )

会话2

[session2 ] >begin ; Query OK ,0 rows affected (0 .00 sec ) [session2 ] >insert intolck_primarkey values (7 ,6 ) ; Query OK ,1 row affected (0 .00 sec ) [session2 ] >insert intolck_primarkey values (5 ,5 ) ; Query OK ,1 row affected (0 .00 sec ) [session2 ] >insert intolck_primarkey values (13 ,13 ) ; Query OK ,1 row affected (0 .00 sec ) [session2 ] >insert intolck_primarkey values (10 ,9 ) ; Query OK ,1 row affected (0 .00 sec )

分析

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

“Gap locking is notneeded forstatements that lockrows using a unique index to search fora unique row . ( Thisdoes not includethe case that the search condition includes only some columns ofa multiple -column unique index ; inthat case ,gap locking does occur . ) Forexample , ifthe id column has a unique index ,the following statement usesonly an index - record lock forthe row having id value 100 andit does notmatter whether other sessions insert rows inthe preceding gap : select *from tab where id =100 forupdate” 就是说当语句通过主键或者唯一键访问数据的时候,Innodb会使用Record lock锁住记录本身,而不是使用间隙锁锁定范围。

需要注意以下两种情况:

1 通过主键或则唯一索引访问不存在的值,也会产生GAP锁。

[session1 ] >begin ; Query OK ,0 rows affected (0 .00 sec ) [session1 ] >select *from lck_primarkey where id =7 forupdate ; Empty set (0 .00 sec ) [session2 ] >insert intolck_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 intolck_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 intolck_primarkey values (3 ,13 ) ; Query OK ,1 row affected (0 .00 sec ) [session2 ] >insert intolck_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 ) NOTNULL , KEY `idx_id` ( `id` ) )ENGINE =InnoDB DEFAULT CHARSET =utf8 ; insert intolck_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 inset (0 .00 sec ) [session1 ] >select *from lck_secondkey where id =9 forupdate ; + - - - - + |id | + - - - - + |9 | + - - - - + 1 row inset (0 .00 sec )

会话2

[session2 ] >begin ; Query OK ,0 rows affected (0 .00 sec ) [session2 ] >insert intolck_secondkey values (3 ) ; Query OK ,1 row affected (0 .00 sec ) [session2 ] >insert intolck_secondkey values (4 ) ; ^CCtrl -C - -sending "KILL QUERY 303040567"to server . . . Ctrl -C - -query aborted . ERROR 1317 (70100 ) :Query execution was interrupted [session2 ] >insert intolck_secondkey values (5 ) ; ^CCtrl -C - -sending "KILL QUERY 303040567"to server . . . Ctrl -C - -query aborted . ERROR 1317 (70100 ) :Query execution was interrupted [session2 ] >insert intolck_secondkey values (6 ) ; ^CCtrl -C - -sending "KILL QUERY 303040567"to server . . . Ctrl -C - -query aborted . ERROR 1317 (70100 ) :Query execution was interrupted [session2 ] >insert intolck_secondkey values (7 ) ; ^CCtrl -C - -sending "KILL QUERY 303040567"to server . . . Ctrl -C - -query aborted . ERROR 1317 (70100 ) :Query execution was interrupted [session2 ] >insert intolck_secondkey values (8 ) ; ^CCtrl -C - -sending "KILL QUERY 303040567"to server . . . Ctrl -C - -query aborted . ERROR 1317 (70100 ) :Query execution was interrupted [session2 ] >insert intolck_secondkey values (9 ) ; ^CCtrl -C - -sending "KILL QUERY 303040567"to server . . . Ctrl -C - -query aborted . ERROR 1317 (70100 ) :Query execution was interrupted [session2 ] >insert intolck_secondkey values (10 ) ; ^CCtrl -C - -sending "KILL QUERY 303040567"to server . . . Ctrl -C - -query aborted . ERROR 1317 (70100 ) :Query execution was interrupted [session2 ] >insert intolck_secondkey values (11 ) ; ^CCtrl -C - -sending "KILL QUERY 303040567"to server . . . Ctrl -C - -query aborted . ERROR 1317 (70100 ) :Query execution was interrupted [session2 ] >insert intolck_secondkey values (12 ) ; ^CCtrl -C - -sending "KILL QUERY 303040567"to server . . . Ctrl -C - -query aborted . ERROR 1317 (70100 ) :Query execution was interrupted [session2 ] >insert intolck_secondkey values (13 ) ; ^CCtrl -C - -sending "KILL QUERY 303040567"to server . . . Ctrl -C - -query aborted . ERROR 1317 (70100 ) :Query execution was interrupted [session2 ] >insert intolck_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 inset (0 .00 sec ) [session1 ] >begin ; Query OK ,0 rows affected (0 .00 sec ) [session1 ] >select *from lck_primarkey where val =8 forupdate ; + - - - - + - - - - - + |id |val | + - - - - + - - - - - + |9 |8 | + - - - - + - - - - - + 1 row inset (0 .00 sec )

会话2

[session2 ] >begin ; Query OK ,0 rows affected (0 .00 sec ) [session2 ] >insert intolck_primarkey values (3 ,5 ) ; Query OK ,1 row affected (0 .00 sec ) [session2 ] >insert intolck_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 inset (0 .00 sec ) [session2 ] >insert intolck_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 intolck_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 intolck_primarkey values (16 ,5 ) ; ERROR 1205 (HY000 ) : Lock waittimeout exceeded ;try restarting transaction [session2 ] > [session2 ] >insert intolck_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值在被锁的范围内,故不能插入。

四 总结

写本文的目的主要是在于温故而知新,侧重于温故。本文着重介绍了三种锁,其实还有两种锁Insert Intention Locks和AUTO-INC Locks 留作后面继续分析。

更多详情见请继续阅读下一页的精彩内容 : http://www.linuxidc.com/Linux/2017-01/139389p2.htm

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台