当前位置:   article > 正文

数据库mysql锁测试(间隙锁、临键锁)_select(sleep(4)

select(sleep(4)

数据库mysql 锁测试

          

                   

                                         

mysql 加锁规则

           

数据库mysql 在可重复读、序列化隔离级别下使用的是间隙锁或者临键锁(间隙锁+行锁)

             

加锁规则:(隔离级别可重复、序列化)

主键索引或者唯一索引查询具体的值(如a=“value”)且value值存在时,可只加行锁,若不存在需要加间隙锁

主键索引或者唯一索引进行范围查询(如a>"value")时,需要加间隙锁或者临键锁(a>value,如果value存在,锁定a>value,不锁value值)

非唯一索引需要在可重复读和序列化隔离级别下需要加间隙锁或者临键锁

如果条件列上没有索引,在主键索引上加表锁

说明:可重复读隔离级别下,读数据读取的是数据快照(可在查询语句中加 for update、lock in share mode语句进行加锁操作),序列化级别下读数据使用的是共享锁

        

             

                                         

数据库表基本信息

           

表:test,字段:id(主键)、name、age(非唯一索引

      

             

隔离级别读取、设置:以下测试均基于可重复读隔离级别(mysql默认隔离级别)

  1. select @@transaction_isolation;
  2. select @@global.transaction_isolation;
  3. set session transaction isolation level repeatable read;
  4. set global transaction isolation level repeatable read;

               

                   

                                         

主键索引行锁测试

          

************

读取相同行记录

        

事务1

  1. start transaction;
  2. select sleep(4);
  3. SELECT now();
  4. select * from test where id=2 for update;
  5. select sleep(20);
  6. SELECT NOW();
  7. commit;

               

事务2

  1. start TRANSACTION;
  2. select SLEEP(8);
  3. select now();
  4. select * from test where id=2 for UPDATE;
  5. select SLEEP(10);
  6. commit;

             

事务1执行状态

  1. start transaction
  2. > OK
  3. > 时间: 0s
  4. select sleep(4)
  5. > OK
  6. > 时间: 4s
  7. SELECT now()
  8. > OK
  9. > 时间: 0.001s
  10. select * from test where id=2 for update
  11. > Affected rows: 0
  12. > 时间: 0.001s
  13. select sleep(20)
  14. > OK
  15. > 时间: 20.002s
  16. SELECT NOW()
  17. > OK
  18. > 时间: 0.001s
  19. commit
  20. > OK
  21. > 时间: 0.001s

        

事务2执行状态

  1. start TRANSACTION
  2. > OK
  3. > 时间: 0s
  4. select SLEEP(8)
  5. > OK
  6. > 时间: 8s
  7. select now()
  8. > OK
  9. > 时间: 0s
  10. select * from test where id=2 for UPDATE
  11. > Affected rows: 0
  12. > 时间: 14.232s
  13. select SLEEP(10)
  14. > OK
  15. > 时间: 10.001s
  16. commit
  17. > OK
  18. > 时间: 0.001s

说明:事务2中select * from test where id=2 for update执行时间为14.232s,表明事务2读取数据时在等待事务1释放行记录锁

           

************

读取不同行记录

         

事务1

  1. start transaction;
  2. select sleep(4);
  3. SELECT now();
  4. select * from test where id=2 for update;
  5. select sleep(20);
  6. SELECT NOW();
  7. commit;

         

事务2

  1. start TRANSACTION;
  2. select SLEEP(8);
  3. select now();
  4. select * from test where id=3 for UPDATE;
  5. select SLEEP(10);
  6. commit;

         

事务1执行状态

  1. start transaction
  2. > OK
  3. > 时间: 0s
  4. select sleep(4)
  5. > OK
  6. > 时间: 4s
  7. SELECT now()
  8. > OK
  9. > 时间: 0s
  10. select * from test where id=2 for update
  11. > Affected rows: 0
  12. > 时间: 0s
  13. select sleep(20)
  14. > OK
  15. > 时间: 20.001s
  16. SELECT NOW()
  17. > OK
  18. > 时间: 0s
  19. commit
  20. > OK
  21. > 时间: 0s

        

事务2执行状态

  1. start TRANSACTION
  2. > OK
  3. > 时间: 0s
  4. select SLEEP(8)
  5. > OK
  6. > 时间: 8s
  7. select now()
  8. > OK
  9. > 时间: 0s
  10. select * from test where id=3 for UPDATE
  11. > Affected rows: 0
  12. > 时间: 0s
  13. select SLEEP(10)
  14. > OK
  15. > 时间: 10.001s
  16. commit
  17. > OK
  18. > 时间:

说明:事务2中select * from test where id=3 for update执行时间约为0秒,事务1、2的读操作不冲突

          

                   

                                         

主键索引间隙锁、临键锁

          

事务1

  1. start TRANSACTION;
  2. select SLEEP(4);
  3. select now();
  4. select * from test where id>3 for UPDATE;
  5. select SLEEP(10);
  6. commit;

        

事务2

  1. start transaction;
  2. select sleep(4);
  3. SELECT now();
  4. select * from test where id=2 for update;
  5. select * from test where id=3 for update;
  6. select * from test where id=4 for update;
  7. select sleep(10);
  8. SELECT NOW();
  9. commit;

       

事务1执行状态

  1. start TRANSACTION
  2. > OK
  3. > 时间: 0.01s
  4. select SLEEP(4)
  5. > OK
  6. > 时间: 4.001s
  7. select now()
  8. > OK
  9. > 时间: 0.001s
  10. select * from test where id>3 for UPDATE
  11. > Affected rows: 0
  12. > 时间: 0.002s
  13. select SLEEP(10)
  14. > OK
  15. > 时间: 10s
  16. commit
  17. > OK
  18. > 时间: 0s

         

事务2执行状态

  1. start transaction
  2. > OK
  3. > 时间: 0s
  4. select sleep(4)
  5. > OK
  6. > 时间: 4s
  7. SELECT now()
  8. > OK
  9. > 时间: 0s
  10. select * from test where id=2 for update
  11. > Affected rows: 0
  12. > 时间: 0s
  13. select * from test where id=3 for update
  14. > Affected rows: 0
  15. > 时间: 0s
  16. select * from test where id=4 for update
  17. > Affected rows: 0
  18. > 时间: 8.427s
  19. select sleep(10)
  20. > OK
  21. > 时间: 10.002s
  22. SELECT NOW()
  23. > OK
  24. > 时间: 0.002s

说明:事务1对id>3(不包括行记录3)的数据记录加间隙锁,事务2中id=2、3的读取操作执行时间很短,id=4的读取操作执行时间很长,该读操作等待事务1释放锁

            

             

                                         

非唯一索引间隙锁、临键锁

            

事务1

  1. start transaction;
  2. select SLEEP(4);
  3. select * from test where age=22 for update;
  4. select * from test where age>23 for update;
  5. select sleep(20);
  6. commit;

事务2

  1. start transaction;
  2. select sleep(8);
  3. select * from test where age=20 for update;
  4. select * from test where age=21 for update;
  5. select * from test where age=22 for update;
  6. select * from test where age=23 for update;
  7. commit;

       

事务1执行状态

  1. start transaction
  2. > OK
  3. > 时间: 0s
  4. select SLEEP(4)
  5. > OK
  6. > 时间: 4.001s
  7. select * from test where age=22 for update
  8. > Affected rows: 0
  9. > 时间: 0.001s
  10. select * from test where age>23 for update
  11. > Affected rows: 0
  12. > 时间: 0.001s
  13. select sleep(20)
  14. > OK
  15. > 时间: 20s
  16. commit
  17. > OK
  18. > 时间: 0.001s

         

事务2执行状态

  1. start transaction
  2. > OK
  3. > 时间: 0.001s
  4. select sleep(8)
  5. > OK
  6. > 时间: 8s
  7. select * from test where age=20 for update
  8. > Affected rows: 0
  9. > 时间: 0.001s
  10. select * from test where age=21 for update
  11. > Affected rows: 0
  12. > 时间: 0.001s
  13. select * from test where age=22 for update
  14. > Affected rows: 0
  15. > 时间: 14.158s
  16. select * from test where age=23 for update
  17. > Affected rows: 0
  18. > 时间: 0.001s
  19. commit
  20. > OK
  21. > 时间: 0s

说明:事务1在age索引上加间隙锁(age>21),事务2中age=22在等待事务1释放锁

        

           

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/繁依Fanyi0/article/detail/173674
推荐阅读
相关标签
  

闽ICP备14008679号