mysql锁对象 lock_type
LOCK_TABLE 表锁
LOCK_REC 行锁
mysql锁模式lock_mode
LOCK_IS
LOCK_IX
LOCK_S
LOCK_X
LOCK_AUTO_INC
mysql锁类型
LOCK_ORDINARY :lock_mode x/s
LOCK_GAP : lock_mode x locks gap before rec
LOCK_REC_NOT_GAP lock_mode x/s locks rec but not gap
LOCK_INSERT_INTENTION
数据准备
create database testdb;
use testdb;
CREATE TABLE `lock_test` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;
insert into lock_test values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
select * from lock_test;
打开4个session,开启RR隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
在第四个session查看加锁情况
select * from performance_schema.data_lock_waits;
select * from performance_schema.data_locks
show engine innodb status G;
1.无索引
等值查询
查询值存在
| 时间 | session1 | session2 | session3 |
|---|---|---|---|
| T1 | begin;update lock_test set b=b+1 where b=5; | ||
| T2 | begin;insert into lock_test values(8,8,8);(blocking) | ||
| T3 | begin;update lock_test set b=b+1 where b=10; | ||
| T4 | rollback | (blocking) |
T1:表上加lock mode IX，主键索引上所有行上加“lock_mode X locks rec”
T2: session2等待“lock_mode X locks gap before rec insert intention waiting”
T3: session3等待"lock_mode X waiting"
T4:session1 rollback ,session3进入执行持有所有“lock_mode X”，session 2等待“lock_mode X locks gap before rec insert intention waiting”
说明：
a.无索引情况下更新操作，按主键锁定所有记录
b.session3比session2先进入执行，“插入意向” 不阻塞 “gap”,gap阻塞 “插入意向”
查询值不存在
| 时间 | session1 | session2 | session3 |
|---|---|---|---|
| T1 | begin;update lock_test set b=b+1 where b=7; | ||
| T2 | begin;insert into lock_test values(8,8,8); (blocking) | ||
| T3 | begin;update lock_test set b=b+1 where b=10; (blocking) |
T1:表上加lock mode IX，主键索引上所有行上加“lock_mode X”
T2: session2等待“lock_mode X locks gap before rec insert intention waiting”
T3: session3等待"lock_mode X waiting"
范围查询
查询值存在
| 时间 | session1 | session2 | session3 |
|---|---|---|---|
| T1 | begin;select * from lock_test where b11 for update; | ||
| T2 | begin;insert into lock_test values(8,8,8);(blolcking) | ||
| T3 | begin;update lock_test set b=b+1 where b=10; (blocking) |
T1:表上加lock mode IX，主键索引上所有行上加“lock_mode X locks rec”
T2: session2等待“lock_mode X locks gap before rec insert intention waiting”
T3: session3等待"lock_mode X waiting"
结论：处于RR隔离级别下，无索引查询会锁定所有记录，不区分范围查询，等值查询
2.唯一索引
等值查询
查询值存在
| 时间 | session1 | session2 | session3 |
|---|---|---|---|
| T1 | begin;select * from lock_test where id=5 for update; | ||
| T2 | begin;insert into lock_test values(8,8,8); | ||
| T3 | begin;update lock_test set b=b+1 where id=10; |
T1:表上加lock mode IX，主键索引上所有行上加“lock_mode X locks rec but not gap”
T2: session2 表上加lock mode IX 不阻塞；插入后意向锁释放
T3: session3 表上加lock mode IX,“lock_mode X locks rec but not gap” 不阻塞
结论：2.等值查询，唯一索引，行锁
查询值不存在
| 时间 | session1 | session2 | session3 |
|---|---|---|---|
| T1 | begin;select * from lock_test where id=7 for update; | ||
| T2 | begin;insert into lock_test values(7,7,7); (blocking) | ||
| T3 | begin;update lock_test set b=b+1 where id=10; |
T1:在id=10的位置加 “lock_mode X locks gap before rec”
T2:阻塞，在id=10的位置 “lock_mode X locks gap before rec insert intention waiting”，间隙锁阻塞插入意向锁
T3:不阻塞，session 3加 “lock_mode X locks rec but not gap”
结论：2.等值查询，唯一索引，行不存在间隙锁
唯一索引记录存在只加记录锁，不存在下一个键位置上加间隙锁 lock_mode X locks gap before rec insert intention waiting
范围查询
查询值存在
| 时间 | session1 | session2 | session3 |
|---|---|---|---|
| T1 | begin;select * from lock_test where id>=5 and id=6 and id=6 and id
 大树
|
