InnoDB Lock浅谈
数据库使用锁是为了支持更好的并发,提供数据的完整性和一致性。InnoDB是一个支持行锁的存储引擎,锁的类型有:共享锁(S)、排他锁(X)、意向共享(IS)、意向排他(IX)。为了提供更好的并发,InnoDB提供了非锁定读:不需要等待访问行上的锁释放,读取行的一个快照。该方法是通过InnoDB的一个特性:MVCC来实现的。
当一个事务获取了行r的共享锁,那么另外一个事务也可以立即获取行r的共享锁,因为读取并未改变行r的数据,这种情况就是锁兼容。但是如果有事务想获得行r的排它锁,则它必须等待事务释放行r上的共享锁—这种情况就是锁不兼容,二者兼容性如下表格所示:
排它锁和共享锁的兼容性 |
||
|
X 排它锁 |
S 共享锁 |
X 排它锁 |
冲突 |
冲突 |
S 共享锁 |
冲突 |
兼容 |
InnoDB锁的扩展,由于InnoDB支持的是行级别锁,所以意向锁其实不太会阻塞全表scan以下的任何请求。共享锁、排它锁、意向共享锁、意向排它锁相互之间都是有兼容/互斥关系的,它们之间的兼容关系如下表:
|
X 排它锁 |
S 共享锁 |
IX 意向排它锁 |
IS 意向共享锁 |
X 排它锁 |
冲突 |
冲突 |
冲突 |
冲突 |
S 共享锁 |
冲突 |
兼容 |
冲突 |
兼容 |
IX 意向排它锁 |
冲突 |
冲突 |
兼容 |
兼容 |
IS 意向共享锁 |
冲突 |
兼容 |
兼容 |
兼容 |
InnoDB有三种行锁的算法:
1,Record Lock:单个行记录上的锁。
2,Gap Lock:间隙锁,锁定一个范围,但不包括记录本身。
3,Next-Key Lock:1+2(Gap Lock+Record Lock),锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题。
Record Lock 总是会去锁住索引记录,如果innodb存储引擎表在建立的时候没有设置任何一个索引,而且查询的时候没有使用到索引,那么这时就会导致表锁。
下面做下测试,这样能让大家更好理解各种锁算法:
测试一:
在session A操作:
mysql> create table t1 ( id int primary key); Query OK, 0 rows affected (0.06 sec) mysql> insert into t1 values(1),(3),(5),(8),(11); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from t1; +----+ | id | +----+ | 1 | | 3 | | 5 | | 8 | | 11 | +----+ 5 rows in set (0.00 sec)
查看当前隔离级别:
mysql> show variables like '%iso%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec)
在A session执行相关测试操作
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1 where id=8 for update; +----+ | id | +----+ | 8 | +----+ 1 row in set (0.03 sec)
在B session操作:
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 select 4; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t1 select 6; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t1 select 7; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t1 select 9; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
分析:InnoDB对于行的查询都是采用了Next-Key Lock的算法,锁定的不是单个值,而是一个范围,是由于id是主键且唯一,当查询的索引含有唯一属性的时候,Next-Key Lock 会进行优化,将其降级为Record Lock,即仅锁住索引本身,不是范围。所以在B会话中,插入4、6、7、9的值不会阻塞,而且成功插入,锁住记录本身,从而提高并发性。
测试2:
在A seesion 操作:
mysql> create table t2 ( id int, vid int, primary key (id), key(vid)); Query OK, 0 rows affected (0.06 sec) mysql> insert into t2 select 1,1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t2 select 3,1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t2 select 5,3; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t2 select 7,6; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t2 select 10,8; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
在t2中,vid列是辅助索引,还是在A会话执行以下操作:
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t2 where vid=3 for update; +----+------+ | id | vid | +----+------+ | 5 | 3 | +----+------+ 1 row in set (0.01 sec)
在B seesion操作(为了加快测试,先设置下):
mysql> set global innodb_lock_wait_timeout=3; Query OK, 0 rows affected (0.00 sec) mysql> set global lock_wait_timeout=3; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like "%lock_wait_timeout%"; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 3 | | lock_wait_timeout | 3 | +--------------------------+-------+ 2 rows in set (0.00 sec)
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t2 where id=5 lock in share mode; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into t2 select 4,2; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into t2 select 6,5; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t2 select 6,6; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
分析:这时的SQL语句通过索引列vid进行查询,因此将会使用传统的Next-Key Locking技术加锁,并且由于有两个索引,需要分别进行锁定,对于聚集索引,仅对列id等于5的索引加上Record Lock,即只锁住5这个记录,但对于辅助索引列,则会用上Next-Key Lock算法,上面索引有1,1,3,6,8,被Next-Key Locking的区间为:(-∞,1],(1,1],(1,3],(3,6],(6,8],(8,+∞),因此它会锁住范围是(1,3],特别需要注意的是,InnoDB存储引擎还会对辅助索引下一个键值加上gap lock,因为还会锁住范围(3,6]。辅助索引列实际会锁住的值有2,3,4,5,6。
在B seesion操作:
mysql>insert into t2 select 2,1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t2 select 8,6; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t2 select 6,7; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
可以看到,没在锁定范围的,可以正常插入。
测试3:
在session A操作:
mysql> create table t3 ( id int, name char(20)); Query OK, 0 rows affected (0.03 sec) mysql> insert into t3 select 1,'aa'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t3 select 2,'bb'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t3 select 3,'cc'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show create table t3\G *************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE `t3` ( `id` int(11) DEFAULT NULL, `name` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.03 sec)
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t3 set name='BB' where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
在B session操作:
mysql> begin; Query OK, 0 rows affected (0.03 sec) mysql> update t3 set name='CC' where id=3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update t3 set name='AA' where id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
分析:可以看出,只有通过索引检索数据,innodb才会采用行锁,否则,innodb将会使用表锁。生产环境一定要注意。
测试四:
在A seesion操作:
mysql> create table t4 ( id int , uid int, unique key(id,uid))engine=innodb; Query OK, 0 rows affected (0.05 sec) mysql> insert into t4 select 1,2; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t4 select 1,3; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t4 select 1,5; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t4 select 1,8; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
表t4创建了一个唯一索引。
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t4 where uid=5 for update; +------+------+ | id | uid | +------+------+ | 1 | 5 | +------+------+ 1 row in set (0.00 sec)
在B session操作:
mysql> begin; Query OK, 0 rows affected (0.03 sec) mysql> insert into t4 select 1,1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into t4 select 1,4; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into t4 select 1,7; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>
解释:可见依然采用的是Next-Key Lock进行锁定的
总结:
1、对于Next-key Lock算法,锁定的是范围,包含记录本身,对辅助索引下一个键值加上Gap Lock
2、对于唯一索引,其加上的是Record Lock,仅锁住记录本身。但也有特别情况,那就是唯一索引由多个列组成,而查询仅是查找多个唯一索引列中的其中一个,那么加锁的情况依然是Next-key Lock。
3、innodb存储引擎是通过给索引上的索引项加锁来实现,这意味着:只有通过索引条件检索数据,innodb才会使用行锁,否则,innodb将使用表锁
参考资料
大牛何登成的博客:http://hedengcheng.com/?p=771
<<MySQL技术内幕--InnoDB存储引擎第2版>>