MySQL语句加锁的实现分析

2023-09-23 01:57:06 63

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

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

SLQ1：select*fromt1whereid=10;
SQL2：deletefromt1whereid=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隔离级别

在RRRC隔离级别下，SQL1：select均不加锁，采用的是快照读；以下仅讨论SQL2：delete操作的加锁
Percona

组合一：id主键+RC
Percona

---TRANSACTION1286310,ACTIVE9sec
2lockstruct(s),heapsize360,1rowlock(s),undologentries1
MySQLthreadid341,OSthreadhandle0x7f4d540d0700,queryid4510972localhostrootcleaningup
TABLELOCKtable`test`.`t1`trxid1286310lockmodeIX
RECORDLOCKSspaceid29pageno3nbits80index`PRIMARY`oftable`test`.`t1`trxid1286310lock_modeXlocksrecbutnotgap

MySQL

---TRANSACTION5936,ACTIVE171sec
2lockstruct(s),heapsize360,1rowlock(s),undologentries1
MySQLthreadid2,OSthreadhandle0x7f5677201700,queryid364localhostroot
TABLELOCKtable`test`.`t1`trxid5936lockmodeIX
RECORDLOCKSspaceid6pageno3nbits80index`PRIMARY`oftable`test`.`t1`trxid5936lock_modeXlocksrecbutnotgap
Recordlock,heapno5PHYSICALRECORD:n_fields4;compactformat;infobits32
0:len4;hex8000000a;asc;;
1:len6;hex000000001730;asc0;;
2:len7;hex26000001550110;asc&U;;
3:len1;hex61;asca;;

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

---TRANSACTION1286327,ACTIVE3sec
3lockstruct(s),heapsize360,2rowlock(s),undologentries1
MySQLthreadid344,OSthreadhandle0x7f4d5404e700,queryid4510986localhostrootcleaningup
TABLELOCKtable`test`.`t2`trxid1286327lockmodeIX
RECORDLOCKSspaceid30pageno4nbits80index`id`oftable`test`.`t2`trxid1286327lock_modeXlocksrecbutnotgap
RECORDLOCKSspaceid30pageno3nbits80index`PRIMARY`oftable`test`.`t2`trxid1286327lock_modeXlocksrecbutnotgap

MySQL

---TRANSACTION5938,ACTIVE3sec
3lockstruct(s),heapsize360,2rowlock(s),undologentries1
MySQLthreadid2,OSthreadhandle0x7f5677201700,queryid374localhostroot
TABLELOCKtable`test`.`t2`trxid5938lockmodeIX
RECORDLOCKSspaceid7pageno4nbits80index`id`oftable`test`.`t2`trxid5938lock_modeXlocksrecbutnotgap
Recordlock,heapno7PHYSICALRECORD:n_fields2;compactformat;infobits32
0:len4;hex8000000a;asc;;
1:len1;hex64;ascd;;

RECORDLOCKSspaceid7pageno3nbits80index`PRIMARY`oftable`test`.`t2`trxid5938lock_modeXlocksrecbutnotgap
Recordlock,heapno7PHYSICALRECORD:n_fields4;compactformat;infobits32
0:len1;hex64;ascd;;
1:len6;hex000000001732;asc2;;
2:len7;hex27000001560110;asc'V;;
3:len4;hex8000000a;asc;;

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

---TRANSACTION1286339,ACTIVE9sec
3lockstruct(s),heapsize360,4rowlock(s),undologentries2
MySQLthreadid347,OSthreadhandle0x7f4b67fff700,queryid4511015localhostrootcleaningup
TABLELOCKtable`test`.`t3`trxid1286339lockmodeIX
RECORDLOCKSspaceid31pageno4nbits80index`idx_key`oftable`test`.`t3`trxid1286339lock_modeXlocksrecbutnotgap
RECORDLOCKSspaceid31pageno3nbits80index`PRIMARY`oftable`test`.`t3`trxid1286339lock_modeXlocksrecbutnotgap

MySQL

---TRANSACTION5940,ACTIVE3sec
3lockstruct(s),heapsize360,4rowlock(s),undologentries2
MySQLthreadid2,OSthreadhandle0x7f5677201700,queryid378localhostroot
TABLELOCKtable`test`.`t3`trxid5940lockmodeIX
RECORDLOCKSspaceid8pageno4nbits80index`idx_key`oftable`test`.`t3`trxid5940lock_modeXlocksrecbutnotgap
Recordlock,heapno4PHYSICALRECORD:n_fields2;compactformat;infobits32
0:len4;hex8000000a;asc;;
1:len1;hex62;ascb;;

Recordlock,heapno5PHYSICALRECORD:n_fields2;compactformat;infobits32
0:len4;hex8000000a;asc;;
1:len1;hex64;ascd;;

RECORDLOCKSspaceid8pageno3nbits80index`PRIMARY`oftable`test`.`t3`trxid5940lock_modeXlocksrecbutnotgap
Recordlock,heapno4PHYSICALRECORD:n_fields4;compactformat;infobits32
0:len1;hex62;ascb;;
1:len6;hex000000001734;asc4;;
2:len7;hex28000001570110;asc(W;;
3:len4;hex8000000a;asc;;

Recordlock,heapno5PHYSICALRECORD:n_fields4;compactformat;infobits32
0:len1;hex64;ascd;;
1:len6;hex000000001734;asc4;;
2:len7;hex28000001570132;asc(W2;;
3:len4;hex8000000a;asc;;

组合四：id无索引+RC
Percona

---TRANSACTION1286373,ACTIVE5sec
2lockstruct(s),heapsize360,2rowlock(s),undologentries2
MySQLthreadid348,OSthreadhandle0x7f4d54193700,queryid4511037localhostrootcleaningup
TABLELOCKtable`test`.`t4`trxid1286373lockmodeIX
RECORDLOCKSspaceid33pageno3nbits80index`PRIMARY`oftable`test`.`t4`trxid1286373lock_modeXlocksrecbutnotgap

MySQL

---TRANSACTION5946,ACTIVE2sec
2lockstruct(s),heapsize360,2rowlock(s),undologentries2
MySQLthreadid2,OSthreadhandle0x7f5677201700,queryid382localhostroot
TABLELOCKtable`test`.`t4`trxid5946lockmodeIX
RECORDLOCKSspaceid9pageno3nbits80index`PRIMARY`oftable`test`.`t4`trxid5946lock_modeXlocksrecbutnotgap
Recordlock,heapno3PHYSICALRECORD:n_fields4;compactformat;infobits32
0:len1;hex62;ascb;;
1:len6;hex00000000173a;asc:;;
2:len7;hex2b0000015a0110;asc+Z;;
3:len4;hex8000000a;asc;;

Recordlock,heapno5PHYSICALRECORD:n_fields4;compactformat;infobits32
0:len1;hex64;ascd;;
1:len6;hex00000000173a;asc:;;
2:len7;hex2b0000015a012c;asc+Z,;;
3:len4;hex8000000a;asc;;

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

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

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

---TRANSACTION1592633,ACTIVE24sec
4lockstruct(s),heapsize1184,5rowlock(s),undologentries2
MySQLthreadid794,OSthreadhandle0x7f4d5404e700,queryid7801799localhostrootcleaningup
Trxreadviewwillnotseetrxwithid>=1592634,sees<1592634
TABLELOCKtable`test`.`t3`trxid1592633lockmodeIX
RECORDLOCKSspaceid31pageno4nbits80index`idx_key`oftable`test`.`t3`trxid1592633lock_modeX
RECORDLOCKSspaceid31pageno3nbits80index`PRIMARY`oftable`test`.`t3`trxid1592633lock_modeXlocksrecbutnotgap
RECORDLOCKSspaceid31pageno4nbits80index`idx_key`oftable`test`.`t3`trxid1592633lock_modeXlocksgapbeforerec

MySQL

---TRANSACTION5985,ACTIVE7sec
4lockstruct(s),heapsize1184,5rowlock(s),undologentries2
MySQLthreadid12,OSthreadhandle0x7f56770fd700,queryid500localhostroot
TABLELOCKtable`test`.`t3`trxid5985lockmodeIX
RECORDLOCKSspaceid8pageno4nbits80index`idx_key`oftable`test`.`t3`trxid5985lock_modeX
Recordlock,heapno4PHYSICALRECORD:n_fields2;compactformat;infobits32
0:len4;hex8000000a;asc;;
1:len1;hex64;ascd;;

Recordlock,heapno5PHYSICALRECORD:n_fields2;compactformat;infobits32
0:len4;hex8000000a;asc;;
1:len1;hex62;ascb;;

RECORDLOCKSspaceid8pageno3nbits80index`PRIMARY`oftable`test`.`t3`trxid5985lock_modeXlocksrecbutnotgap
Recordlock,heapno4PHYSICALRECORD:n_fields4;compactformat;infobits32
0:len1;hex64;ascd;;
1:len6;hex000000001761;asca;;
2:len7;hex3f0000016d0132;asc?m2;;
3:len4;hex8000000a;asc;;

Recordlock,heapno5PHYSICALRECORD:n_fields4;compactformat;infobits32
0:len1;hex62;ascb;;
1:len6;hex000000001761;asca;;
2:len7;hex3f0000016d0110;asc?m;;
3:len4;hex8000000a;asc;;

RECORDLOCKSspaceid8pageno4nbits80index`idx_key`oftable`test`.`t3`trxid5985lock_modeXlocksgapbeforerec
Recordlock,heapno8PHYSICALRECORD:n_fields2;compactformat;infobits0
0:len4;hex8000000b;asc;;
1:len1;hex66;ascf;;

组合八：id无索引+RR
Percona

---TRANSACTION1592639,ACTIVE4sec
2lockstruct(s),heapsize360,7rowlock(s),undologentries2
MySQLthreadid794,OSthreadhandle0x7f4d5404e700,queryid7801804localhostrootcleaningup
TABLELOCKtable`test`.`t4`trxid1592639lockmodeIX
RECORDLOCKSspaceid33pageno3nbits80index`PRIMARY`oftable`test`.`t4`trxid1592639lock_modeX

MySQL

---TRANSACTION6000,ACTIVE3sec
2lockstruct(s),heapsize360,7rowlock(s),undologentries2
MySQLthreadid12,OSthreadhandle0x7f56770fd700,queryid546localhostroot
TABLELOCKtable`test`.`t4`trxid6000lockmodeIX
RECORDLOCKSspaceid9pageno3nbits80index`PRIMARY`oftable`test`.`t4`trxid6000lock_modeX
Recordlock,heapno1PHYSICALRECORD:n_fields1;compactformat;infobits0
0:len8;hex73757072656d756d;ascsupremum;;

Recordlock,heapno2PHYSICALRECORD:n_fields4;compactformat;infobits0
0:len1;hex61;asca;;
1:len6;hex000000001722;asc";;
2:len7;hex9e0000014e0110;ascN;;
3:len4;hex8000000f;asc;;

Recordlock,heapno3PHYSICALRECORD:n_fields4;compactformat;infobits32
0:len1;hex62;ascb;;
1:len6;hex000000001770;ascp;;
2:len7;hex47000001730110;ascGs;;
3:len4;hex8000000a;asc;;

Recordlock,heapno4PHYSICALRECORD:n_fields4;compactformat;infobits0
0:len1;hex63;ascc;;
1:len6;hex000000001722;asc";;
2:len7;hex9e0000014e0122;ascN";;
3:len4;hex80000006;asc;;

Recordlock,heapno5PHYSICALRECORD:n_fields4;compactformat;infobits32
0:len1;hex64;ascd;;
1:len6;hex000000001770;ascp;;
2:len7;hex4700000173012c;ascGs,;;
3:len4;hex8000000a;asc;;

Recordlock,heapno6PHYSICALRECORD:n_fields4;compactformat;infobits0
0:len1;hex66;ascf;;
1:len6;hex000000001722;asc";;
2:len7;hex9e0000014e0134;ascN4;;
3:len4;hex8000000b;asc;;

Recordlock,heapno7PHYSICALRECORD:n_fields4;compactformat;infobits0
0:len2;hex7a7a;asczz;;
1:len6;hex000000001722;asc";;
2:len7;hex9e0000014e013d;ascN=;;
3:len4;hex80000002;asc;;

组合九：Serializable

针对前面提到的简单的SQL，最后一个情况：Serializable隔离级别。对于SQL2：deletefromt1whereid=10;来说，Serializable隔离级别与RepeatableRead隔离级别完全一致，因此不做介绍。

Serializable隔离级别，影响的是SQL1：select*fromt1whereid=10;这条SQL，在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也就是说快照读不复存在，MVCC并发控制降级为Lock-BasedCC。

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

MySQL语句加锁的实现分析

热门推荐

随机推荐