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 ;;