mysql锁问题分析

Reading time ~2 minutes

共享锁：允许事物读取数据，阻止其它事物获取该数据集的排他锁排他锁：允许获取锁的事物更新数据集，阻止其它事物获取数据集的共享锁和排他锁。

根据锁定数据集的大小，mysql的排他分为表锁和行锁

在InnoDB，行锁是通过给索引上的索引项加锁来实现的，也就是说，只有通过索引条件检索数据，才能使用行锁，否则会使用表锁。

表锁：无索引

session1	session2
set autocommit = false
update lock_test set fkey = ‘A’ where c = 1
Rows matched: 2 Changed: 2 Warnings: 0
	update lock_test set fkey = ‘C’ where c = 2
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

行锁：

session1	session2
set autocommit = false
update lock_test set fkey = ‘A’ where c = 1
Rows matched: 2 Changed: 2 Warnings: 0
		update lock_test set fkey = ‘C’ where c = 2
		Rows matched: 2 Changed: 2 Warnings: 0

使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

session1	session2
set autocommit = false
update lock_test set fkey = ‘A’ where c = 1 and fkey = ‘D’
Rows matched: 1 Changed: 1 Warnings: 0
		update lock_test set fkey = ‘C’ where c = 2 and fkey = ‘M’
		ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB对索引项加锁，尽管更新两条不同的数据，但使用相同的索引项

注意：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	lock_test	NULL	range	idx_lock	idx_lock	8	const	2	100.00	Using where; Using temporary

rows对应的数据写操作均会被锁定

当使用范围查询时，InnoDB不仅会对存在的数据加锁，也会对不存在的空隙加锁，即间隙锁（max（id）＝10 ｜ where id > 9 对10加锁｜对 >10空隙加锁
这种加锁的目的是为了防止幻读，防治写操作过程中出现其它session提交了id > 10的数据。

间隙锁：

session1	session2
set autocommit = false
update lock_test set fkey = ‘A’ where c < 6
Rows matched: 1 Changed: 1 Warnings: 0
		insert into lock_test(c, fkey) values (2, ‘T’);
		ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session1	session2
set autocommit = false
update lock_test set fkey = ‘A’ where c < 6
Rows matched: 1 Changed: 1 Warnings: 0
		update lock_test set fkey = ‘A’ where id = 2; 注释： id ＝ 2 and c = 3
		ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session1	session2
set autocommit = false
update lock_test set fkey = ‘A’ where c < 6
Rows matched: 1 Changed: 1 Warnings: 0
		insert into lock_test(c, fkey) values (7, ‘CA’);
		Rows matched: 1 Changed: 1 Warnings: 0