MySQL语句加锁的实现分析
摘要: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隔离级别,读不加锁就不再成立,所有的读操作,都是当前读。