• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    MySQL语句加锁的实现分析

    摘要: MySQL两条SQL语句锁的分析

    看一下下面的SQL语句加什么锁

    SLQ1:select * from t1 where id = 10;
    SQL2:delete from t1 where id = 10;
    

    (1)id 是不是主键

    (2)当前系统的隔离级别是什么

    (3)id列如果不是主键,那么id列上有索引吗

    (4)id列上如果有二级索引,那么这个索引是二级索引吗

    (5)两个SQL的执行计划是什么?索引扫描还是全表扫描

    实际的执行计划需要根据MySQL的输出为准

    组合一:id列是主键,RC隔离级别
    组合二:id列是二级唯一索引,RC隔离级别
    组合三:id列是二级非唯一索引,RC隔离级别
    组合四:id列没有索引,RC隔离级别
    组合五:id列是主键,RR隔离级别
    组合六:id列是二级唯一索引,RR隔离级别
    组合七:id列是二级非唯一索引,RR隔离级别
    组合八:id列上没有索引,RR隔离级别

    Serializable隔离级别

    在RR RC隔离级别下,SQL1:select 均不加锁,采用的是快照读;以下仅讨论SQL2:delete操作的加锁
    Percona

    组合一:id主键+RC
    Percona

    ---TRANSACTION 1286310, ACTIVE 9 sec
    2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
    MySQL thread id 341, OS thread handle 0x7f4d540d0700, query id 4510972 localhost root cleaning up
    TABLE LOCK table `test`.`t1` trx id 1286310 lock mode IX
    RECORD LOCKS space id 29 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 1286310 lock_mode X locks rec but not gap
    

    MySQL

    ---TRANSACTION 5936, ACTIVE 171 sec
    2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
    MySQL thread id 2, OS thread handle 0x7f5677201700, query id 364 localhost root
    TABLE LOCK table `test`.`t1` trx id 5936 lock mode IX
    RECORD LOCKS space id 6 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 5936 lock_mode X locks rec but not gap
    Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
     0: len 4; hex 8000000a; asc   ;;
     1: len 6; hex 000000001730; asc   0;;
     2: len 7; hex 26000001550110; asc   U ;;
     3: len 1; hex 61; asc a;;
    

    组合二:id唯一索引+RC
    在唯一索引上的更新需要两个X锁,一个对应唯一索引id=10 记录,一个对应于聚簇索引name='d'的记录
    Percona

    ---TRANSACTION 1286327, ACTIVE 3 sec
    3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
    MySQL thread id 344, OS thread handle 0x7f4d5404e700, query id 4510986 localhost root cleaning up
    TABLE LOCK table `test`.`t2` trx id 1286327 lock mode IX
    RECORD LOCKS space id 30 page no 4 n bits 80 index `id` of table `test`.`t2` trx id 1286327 lock_mode X locks rec but not gap
    RECORD LOCKS space id 30 page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 1286327 lock_mode X locks rec but not gap
    

    MySQL

    ---TRANSACTION 5938, ACTIVE 3 sec
    3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
    MySQL thread id 2, OS thread handle 0x7f5677201700, query id 374 localhost root
    TABLE LOCK table `test`.`t2` trx id 5938 lock mode IX
    RECORD LOCKS space id 7 page no 4 n bits 80 index `id` of table `test`.`t2` trx id 5938 lock_mode X locks rec but not gap
    Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
     0: len 4; hex 8000000a; asc   ;;
     1: len 1; hex 64; asc d;;
    
    RECORD LOCKS space id 7 page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 5938 lock_mode X locks rec but not gap
    Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
     0: len 1; hex 64; asc d;;
     1: len 6; hex 000000001732; asc   2;;
     2: len 7; hex 27000001560110; asc '  V ;;
     3: len 4; hex 8000000a; asc   ;;
    
    

    组合三:id非唯一索引+RC
    ID列为普通索引,那么对应的所有满足SQL查询条件的记录,都会被加锁;同时,这些记录在主键索引上的记录,也会被加锁
    Percona

    ---TRANSACTION 1286339, ACTIVE 9 sec
    3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2
    MySQL thread id 347, OS thread handle 0x7f4b67fff700, query id 4511015 localhost root cleaning up
    TABLE LOCK table `test`.`t3` trx id 1286339 lock mode IX
    RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1286339 lock_mode X locks rec but not gap
    RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 1286339 lock_mode X locks rec but not gap
    

    MySQL

    ---TRANSACTION 5940, ACTIVE 3 sec
    3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2
    MySQL thread id 2, OS thread handle 0x7f5677201700, query id 378 localhost root
    TABLE LOCK table `test`.`t3` trx id 5940 lock mode IX
    RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5940 lock_mode X locks rec but not gap
    Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
     0: len 4; hex 8000000a; asc   ;;
     1: len 1; hex 62; asc b;;
    
    Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
     0: len 4; hex 8000000a; asc   ;;
     1: len 1; hex 64; asc d;;
    
    RECORD LOCKS space id 8 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 5940 lock_mode X locks rec but not gap
    Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
     0: len 1; hex 62; asc b;;
     1: len 6; hex 000000001734; asc   4;;
     2: len 7; hex 28000001570110; asc (  W ;;
     3: len 4; hex 8000000a; asc   ;;
    
    Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
     0: len 1; hex 64; asc d;;
     1: len 6; hex 000000001734; asc   4;;
     2: len 7; hex 28000001570132; asc (  W 2;;
     3: len 4; hex 8000000a; asc   ;;
    
    

    组合四:id无索引+RC
    Percona

    ---TRANSACTION 1286373, ACTIVE 5 sec
    2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2
    MySQL thread id 348, OS thread handle 0x7f4d54193700, query id 4511037 localhost root cleaning up
    TABLE LOCK table `test`.`t4` trx id 1286373 lock mode IX
    RECORD LOCKS space id 33 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 1286373 lock_mode X locks rec but not gap
    

    MySQL

    ---TRANSACTION 5946, ACTIVE 2 sec
    2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2
    MySQL thread id 2, OS thread handle 0x7f5677201700, query id 382 localhost root
    TABLE LOCK table `test`.`t4` trx id 5946 lock mode IX
    RECORD LOCKS space id 9 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 5946 lock_mode X locks rec but not gap
    Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
     0: len 1; hex 62; asc b;;
     1: len 6; hex 00000000173a; asc   :;;
     2: len 7; hex 2b0000015a0110; asc +  Z ;;
     3: len 4; hex 8000000a; asc   ;;
    
    Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
     0: len 1; hex 64; asc d;;
     1: len 6; hex 00000000173a; asc   :;;
     2: len 7; hex 2b0000015a012c; asc +  Z ,;;
     3: len 4; hex 8000000a; asc   ;;
    
    

    组合五:id主键+RR
    参考 组合一

    组合六:id唯一索引+RR
    参考 组合二

    组合七:id非唯一索引+RR
    Percona

    ---TRANSACTION 1592633, ACTIVE 24 sec
    4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2
    MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801799 localhost root cleaning up
    Trx read view will not see trx with id >= 1592634, sees  1592634
    TABLE LOCK table `test`.`t3` trx id 1592633 lock mode IX
    RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1592633 lock_mode X
    RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 1592633 lock_mode X locks rec but not gap
    RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1592633 lock_mode X locks gap before rec
    

    MySQL

    ---TRANSACTION 5985, ACTIVE 7 sec
    4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2
    MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 500 localhost root
    TABLE LOCK table `test`.`t3` trx id 5985 lock mode IX
    RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5985 lock_mode X
    Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
     0: len 4; hex 8000000a; asc   ;;
     1: len 1; hex 64; asc d;;
    
    Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
     0: len 4; hex 8000000a; asc   ;;
     1: len 1; hex 62; asc b;;
    
    RECORD LOCKS space id 8 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 5985 lock_mode X locks rec but not gap
    Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
     0: len 1; hex 64; asc d;;
     1: len 6; hex 000000001761; asc   a;;
     2: len 7; hex 3f0000016d0132; asc ?  m 2;;
     3: len 4; hex 8000000a; asc   ;;
    
    Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
     0: len 1; hex 62; asc b;;
     1: len 6; hex 000000001761; asc   a;;
     2: len 7; hex 3f0000016d0110; asc ?  m ;;
     3: len 4; hex 8000000a; asc   ;;
    
    RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5985 lock_mode X locks gap before rec
    Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
     0: len 4; hex 8000000b; asc   ;;
     1: len 1; hex 66; asc f;;
    
    

    组合八:id无索引+RR
    Percona

    ---TRANSACTION 1592639, ACTIVE 4 sec
    2 lock struct(s), heap size 360, 7 row lock(s), undo log entries 2
    MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801804 localhost root cleaning up
    TABLE LOCK table `test`.`t4` trx id 1592639 lock mode IX
    RECORD LOCKS space id 33 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 1592639 lock_mode X
    

    MySQL

    ---TRANSACTION 6000, ACTIVE 3 sec
    2 lock struct(s), heap size 360, 7 row lock(s), undo log entries 2
    MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 546 localhost root
    TABLE LOCK table `test`.`t4` trx id 6000 lock mode IX
    RECORD LOCKS space id 9 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 6000 lock_mode X
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
     0: len 1; hex 61; asc a;;
     1: len 6; hex 000000001722; asc   ";;
     2: len 7; hex 9e0000014e0110; asc   N ;;
     3: len 4; hex 8000000f; asc   ;;
    
    Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
     0: len 1; hex 62; asc b;;
     1: len 6; hex 000000001770; asc   p;;
     2: len 7; hex 47000001730110; asc G  s ;;
     3: len 4; hex 8000000a; asc   ;;
    
    Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
     0: len 1; hex 63; asc c;;
     1: len 6; hex 000000001722; asc   ";;
     2: len 7; hex 9e0000014e0122; asc   N ";;
     3: len 4; hex 80000006; asc   ;;
    
    Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
     0: len 1; hex 64; asc d;;
     1: len 6; hex 000000001770; asc   p;;
     2: len 7; hex 4700000173012c; asc G  s ,;;
     3: len 4; hex 8000000a; asc   ;;
    
    Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
     0: len 1; hex 66; asc f;;
     1: len 6; hex 000000001722; asc   ";;
     2: len 7; hex 9e0000014e0134; asc   N 4;;
     3: len 4; hex 8000000b; asc   ;;
    
    Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
     0: len 2; hex 7a7a; asc zz;;
     1: len 6; hex 000000001722; asc   ";;
     2: len 7; hex 9e0000014e013d; asc   N =;;
     3: len 4; hex 80000002; asc   ;;
    
    

    组合九:Serializable

    针对前面提到的简单的SQL,最后一个情况:Serializable隔离级别。对于SQL2:delete from t1 where id = 10; 来说,Serializable隔离级别与Repeatable Read隔离级别完全一致,因此不做介绍。

    Serializable隔离级别,影响的是SQL1:select * from t1 where id = 10; 这条SQL,在RC,RR隔离级别下,都是快照读,不加锁。但是在Serializable隔离级别,SQL1会加读锁,也就是说快照读不复存在,MVCC并发控制降级为Lock-Based CC。

    结论:在MySQL/InnoDB中,所谓的读不加锁,并不适用于所有的情况,而是隔离级别相关的。Serializable隔离级别,读不加锁就不再成立,所有的读操作,都是当前读。

    您可能感兴趣的文章:
    • PHP+MySQL高并发加锁事务处理问题解决方法
    • Mysql事务并发问题解决方案
    • MySQL 数据库如何解决高并发问题
    • mysql多版本并发控制MVCC的实现
    • MySQL并发更新数据时的处理方法
    • Tomcat+Mysql高并发配置优化讲解
    • PHP利用Mysql锁解决高并发的方法
    • MySQL 加锁控制并发的方法
    上一篇:mysql 启动1067错误及修改字符集重启之后复原无效问题
    下一篇:详解mysql中的冗余和重复索引
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    MySQL语句加锁的实现分析 MySQL,语句,加锁,的,实现,