RR级别
mysql V5.6 debug
set global innodb_purge_stop_now=1;
测试1
会话1：
mysql> create table a( a int primary key,b varchar(30)); Query OK, 0 rows affected (0.04 sec) mysql> insert into a values(1,"a"); Query OK, 1 row affected (0.17 sec) mysql> insert into a values(2,"b"); Query OK, 1 row affected (0.18 sec) mysql> insert into a values(3,"c"); Query OK, 1 row affected (0.01 sec) mysql> insert into a values(4,"d"); Query OK, 1 row affected (0.17 sec) mysql> delete from a where a=3; Query OK, 1 row affected (0.01 sec) mysql> select * from a; +---+------+ | a | b | +---+------+ | 1 | a | | 2 | b | | 4 | d | +---+------+ 3 rows in set (0.00 sec)
mysql> begin; Query OK, 0 rows affected (0.17 sec) mysql> select * from a where a<=3 for update; +---+------+ | a | b | +---+------+ | 1 | a | | 2 | b | +---+------+ 2 rows in set (0.01 sec)
会话2：
---TRANSACTION 107908, ACTIVE 65 sec 2 lock struct(s), heap size 376, 4 row lock(s) MySQL thread id 5, OS thread handle 0x2ab31a1d2940, query id 136 localhost root cleaning up Trx read view will not see trx with id >= 107909, sees < 107909 TABLE LOCK table `test`.`a` trx id 107908 lock mode IX RECORD LOCKS space id 240 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx id 107908 lock_mode X Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000001a575; asc u;; 2: len 7; hex c6000001cd0110; asc ;; 3: len 1; hex 61; asc a;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 00000001a576; asc v;; 2: len 7; hex c7000002310110; asc 1 ;; 3: len 1; hex 62; asc b;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000001a581; asc ;; 2: len 7; hex 4e000001f618a5; asc N ;; 3: len 1; hex 63; asc c;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 00000001a57c; asc |;; 2: len 7; hex cb0000016b0110; asc k ;; 3: len 1; hex 64; asc d;;
测试2：
会话1：
mysql> create table a( a int primary key,b varchar(30)); Query OK, 0 rows affected (0.20 sec) mysql> insert into a values(1,"a"); Query OK, 1 row affected (0.02 sec) mysql> insert into a values(2,"b"); Query OK, 1 row affected (0.18 sec) mysql> insert into a values(3,"c"); Query OK, 1 row affected (0.17 sec) mysql> insert into a values(4,"d"); Query OK, 1 row affected (0.19 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from a where a=3; Query OK, 1 row affected (0.01 sec)
会话2：
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into a select 3,"c";
等待
会话3：
---TRANSACTION 107978, ACTIVE 30 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 8, OS thread handle 0x2ab31a190940, query id 187 localhost root executing insert into a select 3,"c" ------- TRX HAS BEEN WAITING 30 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 242 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx id 107978 lock mode S locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000001a5c8; asc ;; 2: len 7; hex 780000018d0d16; asc x ;; 3: len 1; hex 63; asc c;; ------------------ TABLE LOCK table `test`.`a` trx id 107978 lock mode IX RECORD LOCKS space id 242 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx id 107978 lock mode S locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000001a5c8; asc ;; 2: len 7; hex 780000018d0d16; asc x ;; 3: len 1; hex 63; asc c;; ---TRANSACTION 107976, ACTIVE 79 sec 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 MySQL thread id 5, OS thread handle 0x2ab31a1d2940, query id 184 localhost root cleaning up TABLE LOCK table `test`.`a` trx id 107976 lock mode IX RECORD LOCKS space id 242 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx id 107976 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 4; hex 80000003; asc ;; 1: len 6; hex 00000001a5c8; asc ;; 2: len 7; hex 780000018d0d16; asc x ;; 3: len 1; hex 63; asc c;;
插入一个记录：对该记录加 lock mode S locks rec
删除一条记录：对该记录加lock_mode X locks rec
测试3：
set global innodb_purge_stop_now=1;
mysql> select * from t; +---+ | a | +---+ | 1 | | 3 | +---+ 2 rows in set (0.01 sec) mysql> show create table t; CREATE TABLE `t` ( `a` int(11) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk
mysql> delete from t where a=2; Query OK, 1 row affected (0.00 sec)
mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> select * from t where a=2 for update; Empty set (0.00 sec)
---TRANSACTION 109372, ACTIVE 16 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x2b2a48081940, query id 37 localhost root cleaning up TABLE LOCK table `test`.`t` trx id 109372 lock mode IX RECORD LOCKS space id 248 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 109372 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 //已经删掉了,加了 记录锁 0: len 4; hex 80000002; asc ;; 1: len 6; hex 00000001ab36; asc 6;; 2: len 7; hex 25000001571dd2; asc % W ;;
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> insert into t select 2; Query OK, 1 row affected (0.19 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> select * from t where a<3 for update; +---+ | a | +---+ | 1 | | 2 | +---+ 2 rows in set (0.01 sec)
---TRANSACTION 109375, ACTIVE 5 sec 2 lock struct(s), heap size 376, 4 row lock(s) MySQL thread id 1, OS thread handle 0x2b2a48081940, query id 44 localhost root cleaning up TABLE LOCK table `test`.`t` trx id 109375 lock mode IX
RECORD LOCKS space id 248 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 109375 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 3; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000001ab2f; asc /;; 2: len 7; hex a0000001ea0110; asc ;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 原来的 2已丢失了 0: len 4; hex 80000002; asc ;; 1: len 6; hex 00000001ab3d; asc =;; 2: len 7; hex 29000001ae186e; asc ) n;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000001ab35; asc 5;; 2: len 7; hex a4000001ac0110; asc ;;