MySQL语句加锁的实现分析
程序员文章站
2022-07-05 13:30:24
摘要： mysql两条sql语句锁的分析
看一下下面的sql语句加什么锁
slq1：select * from t1 where id = 10;
sql...
摘要： 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隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。