赞
踩
数据库mysql 在可重复读、序列化隔离级别下使用的是间隙锁或者临键锁(间隙锁+行锁)
加锁规则:(隔离级别可重复、序列化)
主键索引或者唯一索引查询具体的值(如a=“value”)且value值存在时,可只加行锁,若不存在需要加间隙锁;
主键索引或者唯一索引进行范围查询(如a>"value")时,需要加间隙锁或者临键锁(a>value,如果value存在,锁定a>value,不锁value值);
非唯一索引需要在可重复读和序列化隔离级别下需要加间隙锁或者临键锁
如果条件列上没有索引,在主键索引上加表锁
说明:可重复读隔离级别下,读数据读取的是数据快照(可在查询语句中加 for update、lock in share mode语句进行加锁操作),序列化级别下读数据使用的是共享锁
表:test,字段:id(主键)、name、age(非唯一索引)
隔离级别读取、设置:以下测试均基于可重复读隔离级别(mysql默认隔离级别)
- select @@transaction_isolation;
- select @@global.transaction_isolation;
-
- set session transaction isolation level repeatable read;
- set global transaction isolation level repeatable read;
************
读取相同行记录
事务1
- start transaction;
- select sleep(4);
- SELECT now();
- select * from test where id=2 for update;
- select sleep(20);
- SELECT NOW();
- commit;
事务2
- start TRANSACTION;
- select SLEEP(8);
- select now();
- select * from test where id=2 for UPDATE;
- select SLEEP(10);
- commit;
事务1执行状态
- start transaction
- > OK
- > 时间: 0s
-
-
- select sleep(4)
- > OK
- > 时间: 4s
-
-
- SELECT now()
- > OK
- > 时间: 0.001s
-
-
- select * from test where id=2 for update
- > Affected rows: 0
- > 时间: 0.001s
-
-
- select sleep(20)
- > OK
- > 时间: 20.002s
-
-
- SELECT NOW()
- > OK
- > 时间: 0.001s
-
-
- commit
- > OK
- > 时间: 0.001s
事务2执行状态
- start TRANSACTION
- > OK
- > 时间: 0s
-
-
- select SLEEP(8)
- > OK
- > 时间: 8s
-
-
- select now()
- > OK
- > 时间: 0s
-
-
- select * from test where id=2 for UPDATE
- > Affected rows: 0
- > 时间: 14.232s
-
-
- select SLEEP(10)
- > OK
- > 时间: 10.001s
-
-
- commit
- > OK
- > 时间: 0.001s
说明:事务2中select * from test where id=2 for update执行时间为14.232s,表明事务2读取数据时在等待事务1释放行记录锁
************
读取不同行记录
事务1
- start transaction;
- select sleep(4);
- SELECT now();
- select * from test where id=2 for update;
- select sleep(20);
- SELECT NOW();
- commit;
事务2
- start TRANSACTION;
- select SLEEP(8);
- select now();
- select * from test where id=3 for UPDATE;
- select SLEEP(10);
- commit;
事务1执行状态
- start transaction
- > OK
- > 时间: 0s
-
-
- select sleep(4)
- > OK
- > 时间: 4s
-
-
- SELECT now()
- > OK
- > 时间: 0s
-
-
- select * from test where id=2 for update
- > Affected rows: 0
- > 时间: 0s
-
-
- select sleep(20)
- > OK
- > 时间: 20.001s
-
-
- SELECT NOW()
- > OK
- > 时间: 0s
-
-
- commit
- > OK
- > 时间: 0s
事务2执行状态
- start TRANSACTION
- > OK
- > 时间: 0s
-
-
- select SLEEP(8)
- > OK
- > 时间: 8s
-
-
- select now()
- > OK
- > 时间: 0s
-
-
- select * from test where id=3 for UPDATE
- > Affected rows: 0
- > 时间: 0s
-
-
- select SLEEP(10)
- > OK
- > 时间: 10.001s
-
-
- commit
- > OK
- > 时间:
说明:事务2中select * from test where id=3 for update执行时间约为0秒,事务1、2的读操作不冲突
事务1
- start TRANSACTION;
- select SLEEP(4);
- select now();
- select * from test where id>3 for UPDATE;
- select SLEEP(10);
- commit;
事务2
- start transaction;
- select sleep(4);
- SELECT now();
- select * from test where id=2 for update;
- select * from test where id=3 for update;
- select * from test where id=4 for update;
- select sleep(10);
- SELECT NOW();
- commit;
事务1执行状态
- start TRANSACTION
- > OK
- > 时间: 0.01s
-
-
- select SLEEP(4)
- > OK
- > 时间: 4.001s
-
-
- select now()
- > OK
- > 时间: 0.001s
-
-
- select * from test where id>3 for UPDATE
- > Affected rows: 0
- > 时间: 0.002s
-
-
- select SLEEP(10)
- > OK
- > 时间: 10s
-
-
- commit
- > OK
- > 时间: 0s
事务2执行状态
- start transaction
- > OK
- > 时间: 0s
-
-
- select sleep(4)
- > OK
- > 时间: 4s
-
-
- SELECT now()
- > OK
- > 时间: 0s
-
-
- select * from test where id=2 for update
- > Affected rows: 0
- > 时间: 0s
-
-
- select * from test where id=3 for update
- > Affected rows: 0
- > 时间: 0s
-
-
- select * from test where id=4 for update
- > Affected rows: 0
- > 时间: 8.427s
-
-
- select sleep(10)
- > OK
- > 时间: 10.002s
-
-
- SELECT NOW()
- > OK
- > 时间: 0.002s
说明:事务1对id>3(不包括行记录3)的数据记录加间隙锁,事务2中id=2、3的读取操作执行时间很短,id=4的读取操作执行时间很长,该读操作等待事务1释放锁
事务1
- start transaction;
- select SLEEP(4);
- select * from test where age=22 for update;
- select * from test where age>23 for update;
- select sleep(20);
- commit;
事务2
- start transaction;
- select sleep(8);
- select * from test where age=20 for update;
- select * from test where age=21 for update;
- select * from test where age=22 for update;
- select * from test where age=23 for update;
- commit;
事务1执行状态
- start transaction
- > OK
- > 时间: 0s
-
-
- select SLEEP(4)
- > OK
- > 时间: 4.001s
-
-
- select * from test where age=22 for update
- > Affected rows: 0
- > 时间: 0.001s
-
-
- select * from test where age>23 for update
- > Affected rows: 0
- > 时间: 0.001s
-
-
- select sleep(20)
- > OK
- > 时间: 20s
-
-
- commit
- > OK
- > 时间: 0.001s
事务2执行状态
- start transaction
- > OK
- > 时间: 0.001s
-
-
- select sleep(8)
- > OK
- > 时间: 8s
-
-
- select * from test where age=20 for update
- > Affected rows: 0
- > 时间: 0.001s
-
-
- select * from test where age=21 for update
- > Affected rows: 0
- > 时间: 0.001s
-
-
- select * from test where age=22 for update
- > Affected rows: 0
- > 时间: 14.158s
-
-
- select * from test where age=23 for update
- > Affected rows: 0
- > 时间: 0.001s
-
-
- commit
- > OK
- > 时间: 0s
说明:事务1在age索引上加间隙锁(age>21),事务2中age=22在等待事务1释放锁
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。