锁测试记录

登博： http://hedengcheng.com/?p=771

兰春：https://keithlan.github.io/2017/06/05/innodb_locks_show_engine/

运维那点事：

########################################

CREATE TABLE `a` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `idx_b` (`b`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> select * from a;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    3 |    5 |    7 |
| 3 |    5 |    7 |    9 |
| 5 |    7 |    9 |   11 |
| 7 |    9 |   11 |   13 |
+---+------+------+------+
4 rows in set (0.00 sec)

select * from a where a,b,c,d=? for update;

1.1、RR隔离级别+主键

　　2 lock struct(s), heap size 1136, 1 row lock(s) 通过主键加行锁，查询不存在的记录，会加间隙锁。

1.2、RR隔离级别+unique key

　　3 lock struct(s), heap size 1136, 2 row lock(s) 通过唯一索引加行锁,主键加行锁，查询不存在的记录，会加间隙锁。

1.3、RR隔离级别+index key

　　4 lock struct(s), heap size 1136, 3 row lock(s) 通过唯一索引加next-key锁,二级索引的列是多条的。（（1，55），【3，7】）,主键加行锁, 间隙锁范围（(3,7),(5,9),）, 阻塞insert，防止幻读。

selet c=8 for update会让gap范围改变。

1.4、RR隔离级别+无索引列

　　2 lock struct(s), heap size 1136, 6 row lock(s) next-key锁 ,supermax ,所有行加锁，相当于表锁。

RECORD LOCKS space id 65 page no 3 n bits 72 index PRIMARY of table `zhijun`.`a` trx id 15178 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;;

1.5、　RR隔离级别+index key+delete不存在的值加的gap锁

1.6、RR隔离级别+index key+delete不存在的值且值很大， supermax

2.1、RC隔离级别+主键

　　2 lock struct(s), heap size 1136, 1 row lock(s) 通过主键加行锁

2.2、RC隔离级别+unique key

　　3 lock struct(s), heap size 1136, 2 row lock(s) 通过唯一索引加行锁,主键加行锁。

2.3、RC隔离级别+index key

　　3 lock struct(s), heap size 1136, 2 row lock(s) 通过索引加行锁,主键加行锁。

2.4、RC隔离级别+无索引列

　　2 lock struct(s), heap size 1136, 1 row lock(s) 通过主键加行锁，所有记录都有加锁操作，不符合条件的解锁，看备注2.4.

#######################

2.4注：

注：在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的

#########################################################

auto lock

insert into sbtest100 (id,k,c,pad) select null,k,c,pad from sbtest1 逐行加锁？
innodb_autoinc_lock_mode=2 单调递增，可能不连续。

------------
TRANSACTIONS
------------
Trx id counter 2813226
Purge done for trx's n:o < 2813226 undo n:o < 0 state: running but idle
History list length 1083
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421367557935616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421367557932880, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2813221, ACTIVE 4 sec inserting
mysql tables in use 2, locked 2
1397 lock struct(s), heap size 139472, 103115 row lock(s), undo log entries 101722
MySQL thread id 3, OS thread handle 139891582662400, query id 23 localhost root Sending data
insert into sbtest100 (id,k,c,pad) select null,k,c,pad from sbtest1
--------


------------
TRANSACTIONS
------------
Trx id counter 2813226
Purge done for trx's n:o < 2813226 undo n:o < 0 state: running but idle
History list length 1083
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421367557935616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421367557932880, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2813221, ACTIVE 6 sec fetching rows
mysql tables in use 2, locked 2
2295 lock struct(s), heap size 237776, 169517 row lock(s), undo log entries 167225
MySQL thread id 3, OS thread handle 139891582662400, query id 23 localhost root Sending data
insert into sbtest100 (id,k,c,pad) select null,k,c,pad from sbtest1
--------

　　insert into ...select * from 出现锁等待

---TRANSACTION 2813292, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 6, OS thread handle 139891582129920, query id 64 localhost root statistics
select id from sbtest1 where id =1 for update
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37 page no 5 n bits 144 index PRIMARY of table `sbtest`.`sbtest1` trx id 2813292 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 0000000afa17; asc       ;;
 2: len 7; hex b50000012a0110; asc     *  ;;
 3: len 4; hex 004bf0e4; asc  K  ;;
 4: len 30; hex 32353831373234373834322d36393531393533323036352d353635373630; asc 25817247842-69519532065-565760; (total 120 bytes);
 5: len 30; hex 37343038353831333335332d37373739373339373237302d363735393933; asc 74085813353-77797397270-675993; (total 60 bytes);

------------------
---TRANSACTION 2813278, ACTIVE (PREPARED) 30 sec committing
mysql tables in use 2, locked 2
7172 lock struct(s), heap size 696528, 530516 row lock(s), undo log entries 523346
MySQL thread id 3, OS thread handle 139891582662400, query id 58 localhost root query end
insert into sbtest101 (k,c,pad) select k,c,pad from sbtest1

---TRANSACTION 2813315, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 6, OS thread handle 139891582129920, query id 72 localhost root updating
delete from sbtest1 where id=1
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37 page no 5 n bits 144 index PRIMARY of table `sbtest`.`sbtest1` trx id 2813315 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 0000000afa17; asc       ;;
 2: len 7; hex b50000012a0110; asc     *  ;;
 3: len 4; hex 004bf0e4; asc  K  ;;
 4: len 30; hex 32353831373234373834322d36393531393533323036352d353635373630; asc 25817247842-69519532065-565760; (total 120 bytes);
 5: len 30; hex 37343038353831333335332d37373739373339373237302d363735393933; asc 74085813353-77797397270-675993; (total 60 bytes);

------------------
---TRANSACTION 2813310, ACTIVE 17 sec fetching rows
mysql tables in use 2, locked 2
4221 lock struct(s), heap size 418000, 312061 row lock(s), undo log entries 307844
MySQL thread id 3, OS thread handle 139891582662400, query id 70 localhost root Sending data
insert into sbtest100 (id,k,c,pad) select null,k,c,pad from sbtest1

session1:
insert into sbtest100 (id,k,c,pad) select null,k,c,pad from sbtest1
session2:
delete from sbtest1 where id=1

insert语句会将sbtest1将扫描过的行加读锁？

#####################################################

rr隔离级别：

CREATE TABLE `a` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `idx_b` (`b`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#####################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from a where a=3;
Query OK, 1 row affected (0.00 sec)

mysql> delete from a where b=5;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from a where c=7;
Query OK, 0 rows affected (0.00 sec)

#############################
---TRANSACTION 16159, ACTIVE 312 sec
8 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 139782642915072, query id 589 localhost root cleaning up
TABLE LOCK table `zhijun`.`a` trx id 16159 lock mode IX
RECORD LOCKS space id 65 page no 3 n bits 72 index PRIMARY of table `zhijun`.`a` trx id 16159 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000003f1f; asc     ? ;;
 2: len 7; hex 37000001490c3d; asc 7   I =;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

RECORD LOCKS space id 65 page no 4 n bits 72 index idx_b of table `zhijun`.`a` trx id 16159 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 65 page no 4 n bits 72 index idx_b of table `zhijun`.`a` trx id 16159 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 65 page no 4 n bits 72 index idx_b of table `zhijun`.`a` trx id 16159 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 65 page no 5 n bits 80 index idx_c of table `zhijun`.`a` trx id 16159 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 65 page no 5 n bits 80 index idx_c of table `zhijun`.`a` trx id 16159 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 65 page no 5 n bits 80 index idx_c of table `zhijun`.`a` trx id 16159 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000005; asc     ;;
#####################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from a where b=5;
Query OK, 1 row affected (0.00 sec)
################################
---TRANSACTION 421258607697520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421258607696608, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 16161, ACTIVE 4 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 139782642915072, query id 593 localhost root cleaning up
TABLE LOCK table `zhijun`.`a` trx id 16161 lock mode IX
RECORD LOCKS space id 65 page no 4 n bits 72 index idx_b of table `zhijun`.`a` trx id 16161 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 65 page no 3 n bits 72 index PRIMARY of table `zhijun`.`a` trx id 16161 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000003f21; asc     ?!;;
 2: len 7; hex 38000001810949; asc 8     I;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;
##############################################

posted on 2017-12-27 17:48 星期六男爵阅读(236) 评论(0) 收藏举报

刷新页面返回顶部

龙二他爹

锁测试记录

导航

公告