[MySQL] 锁/死锁问题一例
MySQL锁/死锁问题
在MySQL中, 不同事务隔离级别下, 锁的情况表现是不同的, 另外表的设计上有无索引也是一个因素.
做一个小的实验测试InnoDB锁表现 -😃
-
说明
-
事务隔离级别
READ-COMMITED和REPEATABLE-READ
-
测试语句
SELECT .. FOR UPDATE(LIMIT)
INSERT/UPDATE
-
-
测试数据准备
-
建表
CREATE TABLE RENO (name VARCHAR(10), cycle INT, expires DATE, session VARCHAR(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8; -
初始化数据
INSERT INTO reno VALUES ('reno1', 1, '2016-03-06', 'session1'); INSERT INTO reno VALUES ('reno2', 1, '2016-04-06', 'session1'); INSERT INTO reno VALUES ('reno3', 1, '2016-05-06', 'session1'); INSERT INTO reno VALUES ('reno4', 1, '2016-06-06', 'session1'); INSERT INTO reno VALUES ('reno5', 1, '2016-07-06', 'session1');
-
-
CASE 1
事务隔离级别是READ-COMMITTED, expires上无索引.
Time Session 1
(thread_id: 5690860
tx_id: 6497569)Session 2
(thread_id: 5690839
tx_id: 6497636)Status 1 SET tx_isolation='READ-COMMITTED'; START TRANSACTION; SET tx_isolation='READ-COMMITTED'; START TRANSACTION; 2 SELECT * FROM reno WHERE expires < '2016-05-06' ORDER BY expires LIMIT 1 FOR UPDATE; 3 INSERT INTO reno VALUES ('reno6', 1, '2016-08-06', 'session2'); Success 4 INSERT INTO reno VALUES ('reno6', 1, '2016-02-06', 'session2'); Success 5 INSERT INTO reno VALUES ('reno6', 1, '2016-04-20', 'session2'); Success 6 INSERT INTO reno VALUES ('reno6', 1, '2016-03-20', 'session2'); Success 7 INSERT INTO reno VALUES ('reno6', 1, '2016-03-06', 'session2'); Success 8 SELECT * FROM reno WHERE expires < '2016-09-06' ORDER BY expires LIMIT 1 FOR UPDATE; Hang
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction9 SELECT * FROM reno WHERE expires < '2016-09-06' ORDER BY expires LIMIT 1 FOR UPDATE; SELECT * FROM reno WHERE expires < '2016-09-06' ORDER BY expires LIMIT 1 FOR UPDATE; Session 1: Deadlock
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction------------------------ LATEST DETECTED DEADLOCK ------------------------ 2016-03-06 14:21:10 2b6720080700 *** (1) TRANSACTION: TRANSACTION 6497569, ACTIVE 383 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1184, 7 row lock(s) MySQL thread id 5690860, OS thread handle 0x2b6716080700, query id 21623406 localhost root Creating sort index SELECT * FROM reno WHERE expires < '2016-09-06' ORDER BY expires LIMIT 1 FOR UPDATE *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 13 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`reno` trx id 6497569 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000200; asc ;; 1: len 6; hex 000000632148; asc c!H;; 2: len 7; hex a3000001460110; asc F ;; 3: len 5; hex 72656e6f31; asc reno1;; 4: len 4; hex 80000001; asc ;; 5: len 3; hex 8fc066; asc f;; 6: len 8; hex 73657373696f6e31; asc session1;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000201; asc ;; 1: len 6; hex 000000632159; asc c!Y;; 2: len 7; hex ac000001c30110; asc ;; 3: len 5; hex 72656e6f32; asc reno2;; 4: len 4; hex 80000001; asc ;; 5: len 3; hex 8fc086; asc ;; 6: len 8; hex 73657373696f6e31; asc session1;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000202; asc ;; 1: len 6; hex 000000632161; asc c!a;; 2: len 7; hex b1000001c40110; asc ;; 3: len 5; hex 72656e6f33; asc reno3;; 4: len 4; hex 80000001; asc ;; 5: len 3; hex 8fc0a6; asc ;; 6: len 8; hex 73657373696f6e31; asc session1;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000203; asc ;; 1: len 6; hex 000000632163; asc c!c;; 2: len 7; hex b2000001c50110; asc ;; 3: len 5; hex 72656e6f34; asc reno4;; 4: len 4; hex 80000001; asc ;; 5: len 3; hex 8fc0c6; asc ;; 6: len 8; hex 73657373696f6e31; asc session1;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000204; asc ;; 1: len 6; hex 000000632164; asc c!d;; 2: len 7; hex b3000001c60110; asc ;; 3: len 5; hex 72656e6f35; asc reno5;; 4: len 4; hex 80000001; asc ;; 5: len 3; hex 8fc0e6; asc ;; 6: len 8; hex 73657373696f6e31; asc session1;; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 13 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`reno` trx id 6497569 lock_mode X locks rec but not gap waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000206; asc ;; 1: len 6; hex 000000632564; asc c%d;; 2: len 7; hex b6000001e00110; asc ;; 3: len 5; hex 72656e6f36; asc reno6;; 4: len 4; hex 80000001; asc ;; 5: len 3; hex 8fc106; asc ;; 6: len 8; hex 73657373696f6e32; asc session2;; *** (2) TRANSACTION: TRANSACTION 6497636, ACTIVE 199 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1 MySQL thread id 5690839, OS thread handle 0x2b6720080700, query id 21623519 localhost root Creating sort index SELECT * FROM reno WHERE expires < '2016-09-06' ORDER BY expires LIMIT 1 FOR UPDATE *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 13 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`reno` trx id 6497636 lock_mode X locks rec but not gap Record lock, heap no 7 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000206; asc ;; 1: len 6; hex 000000632564; asc c%d;; 2: len 7; hex b6000001e00110; asc ;; 3: len 5; hex 72656e6f36; asc reno6;; 4: len 4; hex 80000001; asc ;; 5: len 3; hex 8fc106; asc ;; 6: len 8; hex 73657373696f6e32; asc session2;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 13 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`reno` trx id 6497636 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000200; asc ;; 1: len 6; hex 000000632148; asc c!H;; 2: len 7; hex a3000001460110; asc F ;; 3: len 5; hex 72656e6f31; asc reno1;; 4: len 4; hex 80000001; asc ;; 5: len 3; hex 8fc066; asc f;; 6: len 8; hex 73657373696f6e31; asc session1;; *** WE ROLL BACK TRANSACTION (1) select * from INNODB_TRX; +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | 6497734 | LOCK WAIT | 2016-03-06 14:21:48 | 6497734:13:3:2 | 2016-03-06 14:21:48 | 2 | 5690860 | SELECT * FROM reno WHERE expires < '2016-09-06' ORDER BY expires LIMIT 1 FOR UPDATE | starting index read | 1 | 1 | 2 | 360 | 1 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 10000 | 0 | 0 | | 6497636 | RUNNING | 2016-03-06 14:17:51 | NULL | NULL | 4 | 5690839 | NULL | NULL | 0 | 0 | 3 | 360 | 6 | 1 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 10000 | 0 | 0 | +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ select * from INNODB_LOCKS; +----------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+----------------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +----------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+----------------+ | 6497734:13:3:2 | 6497734 | X | RECORD | `test`.`reno` | GEN_CLUST_INDEX | 13 | 3 | 2 | 0x000000000200 | | 6497636:13:3:2 | 6497636 | X | RECORD | `test`.`reno` | GEN_CLUST_INDEX | 13 | 3 | 2 | 0x000000000200 | +----------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+----------------+ select * from INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 6497734 | 6497734:13:3:2 | 6497636 | 6497636:13:3:2 | +-------------------+-------------------+-----------------+------------------+从以上的测试过程以及InnoDB状态可以看出:
- 在READ-COMMITTED隔离级别下, 即使Session 1锁住了纪录expires < '2016-05-06', Session 2可以插入任何范围的记录(Session使用的是lock_mode X locks rec but not gap);
- 在Session 1视图请求更高范围的锁(expires < '2016-09-06')时, 则会hang住, 如果接下来Session 2做同样的请求, 那么Session 1则会出现Deadlock而被回滚;
- 当InnoDB表无主键时, InnoDB默认会创建自己的cluster index(GEN_CLUST_INDEX);
-
CASE 2
事务隔离级别是REPEATABLE-READ, expires上无索引.
Time Session 1
(thread_id: 5690860
tx_id: 6498890)Session 2
(thread_id: 5690839
tx_id: 6498893)Status 1 SET tx_isolation='REPEATABLE-READ'; START TRANSACTION; SET tx_isolation='REPEATABLE-READ'; START TRANSACTION; 2 SELECT * FROM reno WHERE expires < '2016-05-06' ORDER BY expires LIMIT 1 FOR UPDATE; 3 INSERT INTO reno VALUES ('reno6', 1, '2016-08-06', 'session2'); Hang
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction------------ TRANSACTIONS ------------ Trx id counter 6498920 Purge done for trx's n:o < 6498920 undo n:o < 0 state: running but idle History list length 3502 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 6498893, ACTIVE 72 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 2 row lock(s) MySQL thread id 5690839, OS thread handle 0x2b6720080700, query id 21628179 localhost root update INSERT INTO reno VALUES ('reno6', 1, '2016-08-06 12:00:00', 'session2') ------- TRX HAS BEEN WAITING 51 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 13 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`reno` trx id 6498893 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; ------------------ ---TRANSACTION 6498890, ACTIVE 83 sec 2 lock struct(s), heap size 360, 6 row lock(s) MySQL thread id 5690860, OS thread handle 0x2b6716080700, query id 21628134 localhost root cleaning up select * from INNODB_TRX; +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | 6498893 | LOCK WAIT | 2016-03-06 15:14:10 | 6498893:13:3:1 | 2016-03-06 15:14:31 | 2 | 5690839 | INSERT INTO reno VALUES ('reno6', 1, '2016-08-06', 'session2') | inserting | 1 | 1 | 2 | 360 | 2 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 10000 | 0 | 0 | | 6498890 | RUNNING | 2016-03-06 15:13:59 | NULL | NULL | 2 | 5690860 | NULL | NULL | 0 | 0 | 2 | 360 | 6 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 10000 | 0 | 0 | +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ select * from INNODB_LOCKS; +----------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+------------------------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +----------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+------------------------+ | 6498893:13:3:1 | 6498893 | X | RECORD | `test`.`reno` | GEN_CLUST_INDEX | 13 | 3 | 1 | supremum pseudo-record | | 6498890:13:3:1 | 6498890 | X | RECORD | `test`.`reno` | GEN_CLUST_INDEX | 13 | 3 | 1 | supremum pseudo-record | +----------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+------------------------+ select * from INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 6498893 | 6498893:13:3:1 | 6498890 | 6498890:13:3:1 | +-------------------+-------------------+-----------------+------------------+从以上的测试过程以及InnoDB状态可以看出:
- 在REPEATABLE-READ隔离级别下, 当Session 1锁住了纪录expires < '2016-05-06', Session 2是无法插入任何记录的(lock_mode X insert intention waiting);
- 在Session 1使用了gap lock, insert intention也是一种gap lock;
- 当InnoDB表无主键时, InnoDB默认会创建自己的cluster index(GEN_CLUST_INDEX);
-
CASE 3
事务隔离级别是READ-COMMITTED, name有主键, expires有索引.
CREATE TABLE RENO1 (name VARCHAR(10), cycle INT, expires DATE, session VARCHAR(10), PRIMARY KEY (name), INDEX(expires)) ENGINE=InnoDB DEFAULT CHARSET=utf8;Time Session 1
(thread_id: 5690860
tx_id: 6499370)Session 2
(thread_id: 5690839
tx_id: 6499379)Status 1 SET tx_isolation='READ-COMMITTED'; START TRANSACTION; SET tx_isolation='READ-COMMITTED'; START TRANSACTION; 2 SELECT * FROM reno1 WHERE expires < '2016-05-06' ORDER BY expires LIMIT 1 FOR UPDATE; 3 INSERT INTO reno1 VALUES ('reno6', 1, '2016-08-06', 'session2'); Success 4 SELECT * FROM reno1 WHERE expires < '2016-08-06' ORDER BY expires LIMIT 1 FOR UPDATE; Hang
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction5 SELECT * FROM reno1 WHERE expires < '2016-09-06' ORDER BY expires LIMIT 1 FOR UPDATE; Success 6 INSERT INTO reno1 VALUES ('reno7', 1, '2016-02-06', 'session2'); Success 7 INSERT INTO reno1 VALUES ('reno8', 1, '2016-03-12', 'session2'); Success 8 INSERT INTO reno1 VALUES ('reno9', 1, '2016-04-12', 'session2'); Success 9 INSERT INTO reno1 VALUES ('reno10', 1, '2016-05-12', 'session2'); Success 10 INSERT INTO reno1 VALUES ('reno11', 1, '2016-03-06', 'session2'); Success ------------ TRANSACTIONS ------------ Trx id counter 6499386 Purge done for trx's n:o < 6499386 undo n:o < 0 state: running but idle History list length 3669 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 6499379, ACTIVE 15 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1 MySQL thread id 5690839, OS thread handle 0x2b6720080700, query id 21629889 localhost root Sending data SELECT * FROM reno1 WHERE expires < '2016-08-06' ORDER BY expires LIMIT 1 FOR UPDATE ------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 14 page no 4 n bits 80 index `expires` of table `test`.`reno1` trx id 6499379 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 3; hex 8fc066; asc f;; 1: len 5; hex 72656e6f31; asc reno1;; ------------------ ---TRANSACTION 6499370, ACTIVE 36 sec 3 lock struct(s), heap size 360, 2 row lock(s) MySQL thread id 5690860, OS thread handle 0x2b6716080700, query id 21629845 localhost root cleaning up select * from INNODB_TRX; +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | 6499379 | LOCK WAIT | 2016-03-06 15:33:57 | 6499379:14:4:2 | 2016-03-06 15:34:06 | 3 | 5690839 | SELECT * FROM reno1 WHERE expires < '2016-08-06' ORDER BY expires LIMIT 1 FOR UPDATE | starting index read | 1 | 1 | 2 | 360 | 1 | 1 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 10000 | 0 | 0 | | 6499370 | RUNNING | 2016-03-06 15:33:36 | NULL | NULL | 3 | 5690860 | NULL | NULL | 0 | 0 | 3 | 360 | 2 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 10000 | 0 | 0 | +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ select * from INNODB_LOCKS; +----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+------------------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+------------------+ | 6499379:14:4:2 | 6499379 | X | RECORD | `test`.`reno1` | expires | 14 | 4 | 2 | 1032294, 'reno1' | | 6499370:14:4:2 | 6499370 | X | RECORD | `test`.`reno1` | expires | 14 | 4 | 2 | 1032294, 'reno1' | +----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+------------------+ select * from INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 6499379 | 6499379:14:4:2 | 6499370 | 6499370:14:4:2 | +-------------------+-------------------+-----------------+------------------+从以上的测试过程以及InnoDB状态可以看出:
- 在READ-COMMITTED隔离级别下, 当expires有索引时, Session 1可以获取更高范围的锁查询;
- 但Session 2无法获取锁, 因为Session 1已经持有范围锁资源;
这里把LIMIT去掉看看有什么现象
Time Session 1
(thread_id: 5690860
tx_id: 6500454)Session 2
(thread_id: 5690839
tx_id: 6500459)Status 1 SET tx_isolation='READ-COMMITTED'; START TRANSACTION; SET tx_isolation='READ-COMMITTED'; START TRANSACTION; 2 SELECT * FROM reno1 WHERE expires < '2016-05-06' ORDER BY expires FOR UPDATE; 3 INSERT INTO reno1 VALUES ('reno6', 1, '2016-08-06', 'session2'); Success 4 SELECT * FROM reno1 WHERE expires < '2016-08-06' ORDER BY expires FOR UPDATE; Hang
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction5 SELECT * FROM reno1 WHERE expires < '2016-08-06' ORDER BY expires FOR UPDATE; SELECT * FROM reno1 WHERE expires < '2016-08-06' ORDER BY expires FOR UPDATE; Session 2: Deadlock
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction5 INSERT INTO reno1 VALUES ('reno7', 1, '2016-02-06', 'session2'); Success 6 INSERT INTO reno1 VALUES ('reno8', 1, '2016-03-12', 'session2'); Success 7 INSERT INTO reno1 VALUES ('reno9', 1, '2016-04-12', 'session2'); Success 8 INSERT INTO reno1 VALUES ('reno10', 1, '2016-05-12', 'session2'); Success 9 INSERT INTO reno1 VALUES ('reno11', 1, '2016-03-06', 'session2'); Success ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2016-03-06 16:16:14 2b6720080700 *** (1) TRANSACTION: TRANSACTION 6500454, ACTIVE 52 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 9 row lock(s) MySQL thread id 5690860, OS thread handle 0x2b6716080700, query id 21633511 localhost root Creating sort index SELECT * FROM reno1 WHERE expires < '2016-08-06' ORDER BY expires FOR UPDATE *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 16 page no 3 n bits 80 index `PRIMARY` of table `test`.`reno1` trx id 6500454 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 5; hex 72656e6f31; asc reno1;; 1: len 6; hex 000000632f38; asc c/8;; 2: len 7; hex b4000002080110; asc ;; 3: len 4; hex 80000001; asc ;; 4: len 3; hex 8fc066; asc f;; 5: len 8; hex 73657373696f6e31; asc session1;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 5; hex 72656e6f32; asc reno2;; 1: len 6; hex 000000632f39; asc c/9;; 2: len 7; hex b5000002090110; asc ;; 3: len 4; hex 80000001; asc ;; 4: len 3; hex 8fc086; asc ;; 5: len 8; hex 73657373696f6e31; asc session1;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 5; hex 72656e6f33; asc reno3;; 1: len 6; hex 000000632f3c; asc c/<;; 2: len 7; hex b70000020a0110; asc ;; 3: len 4; hex 80000001; asc ;; 4: len 3; hex 8fc0a6; asc ;; 5: len 8; hex 73657373696f6e31; asc session1;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 5; hex 72656e6f34; asc reno4;; 1: len 6; hex 000000632f44; asc c/D;; 2: len 7; hex bc000001da0110; asc ;; 3: len 4; hex 80000001; asc ;; 4: len 3; hex 8fc0c6; asc ;; 5: len 8; hex 73657373696f6e31; asc session1;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 5; hex 72656e6f35; asc reno5;; 1: len 6; hex 000000632f4c; asc c/L;; 2: len 7; hex c0000001de0110; asc ;; 3: len 4; hex 80000001; asc ;; 4: len 3; hex 8fc0e6; asc ;; 5: len 8; hex 73657373696f6e31; asc session1;; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 16 page no 3 n bits 80 index `PRIMARY` of table `test`.`reno1` trx id 6500454 lock_mode X locks rec but not gap waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 5; hex 72656e6f36; asc reno6;; 1: len 6; hex 00000063306b; asc c0k;; 2: len 7; hex d2000001540110; asc T ;; 3: len 4; hex 80000001; asc ;; 4: len 3; hex 8fc106; asc ;; 5: len 8; hex 73657373696f6e32; asc session2;; *** (2) TRANSACTION: TRANSACTION 6500459, ACTIVE 41 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1 MySQL thread id 5690839, OS thread handle 0x2b6720080700, query id 21633522 localhost root Creating sort index SELECT * FROM reno1 WHERE expires < '2016-08-06' ORDER BY expires FOR UPDATE *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 16 page no 3 n bits 80 index `PRIMARY` of table `test`.`reno1` trx id 6500459 lock_mode X locks rec but not gap Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 5; hex 72656e6f36; asc reno6;; 1: len 6; hex 00000063306b; asc c0k;; 2: len 7; hex d2000001540110; asc T ;; 3: len 4; hex 80000001; asc ;; 4: len 3; hex 8fc106; asc ;; 5: len 8; hex 73657373696f6e32; asc session2;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 16 page no 3 n bits 80 index `PRIMARY` of table `test`.`reno1` trx id 6500459 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 5; hex 72656e6f31; asc reno1;; 1: len 6; hex 000000632f38; asc c/8;; 2: len 7; hex b4000002080110; asc ;; 3: len 4; hex 80000001; asc ;; 4: len 3; hex 8fc066; asc f;; 5: len 8; hex 73657373696f6e31; asc session1;; *** WE ROLL BACK TRANSACTION (2) select * from INNODB_TRX; +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | 6500512 | RUNNING | 2016-03-06 16:17:59 | NULL | NULL | 7 | 5690839 | NULL | NULL | 0 | 0 | 2 | 360 | 2 | 5 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 10000 | 0 | 0 | | 6500454 | LOCK WAIT | 2016-03-06 16:15:22 | 6500454:16:3:14 | 2016-03-06 16:19:10 | 5 | 5690860 | SELECT * FROM reno1 WHERE expires < '2016-08-06' ORDER BY expires FOR UPDATE | fetching rows | 1 | 1 | 5 | 1184 | 9 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 10000 | 0 | 0 | +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ select * from INNODB_LOCKS; +-----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +-----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+ | 6500454:16:3:14 | 6500454 | X | RECORD | `test`.`reno1` | PRIMARY | 16 | 3 | 14 | 'reno10' | | 6500512:16:3:14 | 6500512 | X | RECORD | `test`.`reno1` | PRIMARY | 16 | 3 | 14 | 'reno10' | +-----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+ select * from INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 6500454 | 6500454:16:3:14 | 6500512 | 6500512:16:3:14 | +-------------------+-------------------+-----------------+------------------+当去掉LIMIT 1时, 该逻辑即发生死锁.
-
CASE 4
事务隔离级别是REPEATABLE-READ, name有主键, expires有索引.
Time Session 1
(thread_id: 5690860
tx_id: 6500940)Session 2
(thread_id: 5690839
tx_id: 6500945)Status 1 SET tx_isolation='REPEATABLE-READ'; START TRANSACTION; SET tx_isolation='REPEATABLE-READ'; START TRANSACTION; 2 SELECT * FROM reno1 WHERE expires < '2016-05-06' ORDER BY expires LIMIT 1 FOR UPDATE; 3 INSERT INTO reno1 VALUES ('reno6', 1, '2016-08-06', 'session2'); Success 4 SELECT * FROM reno1 WHERE expires < '2016-08-06' ORDER BY expires LIMIT 1 FOR UPDATE; Success 5 SELECT * FROM reno1 WHERE expires < '2016-08-06' ORDER BY expires FOR UPDATE; Session 2: Hang
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction5 INSERT INTO reno1 VALUES ('reno7', 1, '2016-02-06', 'session2'); Session 2: Hang
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction6 INSERT INTO reno1 VALUES ('reno8', 1, '2016-03-12', 'session2'); Success 7 INSERT INTO reno1 VALUES ('reno9', 1, '2016-04-12', 'session2'); Success 8 INSERT INTO reno1 VALUES ('reno10', 1, '2016-05-12', 'session2'); Success 9 INSERT INTO reno1 VALUES ('reno11', 1, '2016-03-06', 'session2'); Success ------------ TRANSACTIONS ------------ Trx id counter 6501148 Purge done for trx's n:o < 6501148 undo n:o < 0 state: running but idle History list length 3739 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 6500945, ACTIVE 474 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 6 MySQL thread id 5690839, OS thread handle 0x2b6720080700, query id 21635931 localhost root update INSERT INTO reno1 VALUES ('reno7', 1, '2016-02-06', 'session2') ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 16 page no 4 n bits 88 index `expires` of table `test`.`reno1` trx id 6500945 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 3; hex 8fc066; asc f;; 1: len 5; hex 72656e6f31; asc reno1;; ------------------ ---TRANSACTION 6500940, ACTIVE 483 sec 3 lock struct(s), heap size 360, 2 row lock(s) MySQL thread id 5690860, OS thread handle 0x2b6716080700, query id 21635410 localhost root cleaning up select * from INNODB_TRX; +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | 6500945 | LOCK WAIT | 2016-03-06 16:36:21 | 6500945:16:4:2 | 2016-03-06 16:44:10 | 8 | 5690839 | INSERT INTO reno1 VALUES ('reno7', 1, '2016-02-06', 'session2') | inserting | 1 | 1 | 2 | 1184 | 5 | 6 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 10000 | 0 | 0 | | 6500940 | RUNNING | 2016-03-06 16:36:12 | NULL | NULL | 3 | 5690860 | NULL | NULL | 0 | 0 | 3 | 360 | 2 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 10000 | 0 | 0 | +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ select * from INNODB_LOCKS; +----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+------------------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+------------------+ | 6500945:16:4:2 | 6500945 | X,GAP | RECORD | `test`.`reno1` | expires | 16 | 4 | 2 | 1032294, 'reno1' | | 6500940:16:4:2 | 6500940 | X | RECORD | `test`.`reno1` | expires | 16 | 4 | 2 | 1032294, 'reno1' | +----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+------------------+ select * from INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 6500945 | 6500945:16:4:2 | 6500940 | 6500940:16:4:2 | +-------------------+-------------------+-----------------+------------------+从以上的测试过程以及InnoDB状态可以看出:
- 在REPEATABLE-READ隔离级别下, 当Session 1使用expires < '2016-08-06' LIMIT 1后, Session 2可以插入任何大于最小的expires值的记录, 但是无法插入小于最小的expires值的记录, 因为无法获得gap的意向锁(lock_mode X locks gap before rec insert intention waiting);
这里把LIMIT去掉看看有什么现象
Time Session 1
(thread_id: 5690860
tx_id: 6500454)Session 2
(thread_id: 5690839
tx_id: 6500459)Status 1 SET tx_isolation='REPEATABLE-READ'; START TRANSACTION; SET tx_isolation='REPEATABLE-READ'; START TRANSACTION; 2 SELECT * FROM reno1 WHERE expires < '2016-05-06' ORDER BY expires FOR UPDATE; 3 INSERT INTO reno1 VALUES ('reno6', 1, '2016-08-06', 'session2'); Success 4 INSERT INTO reno1 VALUES ('reno7', 1, '2016-02-06', 'session2'); Hang
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction5 INSERT INTO reno1 VALUES ('reno8', 1, '2016-03-12', 'session2'); Hang
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction6 INSERT INTO reno1 VALUES ('reno9', 1, '2016-04-12', 'session2'); Hang
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction7 INSERT INTO reno1 VALUES ('reno10', 1, '2016-05-12', 'session2'); Success 8 INSERT INTO reno1 VALUES ('reno11', 1, '2016-10-06', 'session2'); Success 9 SELECT * FROM reno1 WHERE expires < '2016-08-06' ORDER BY expires FOR UPDATE; Hang
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction10 SELECT * FROM reno1 WHERE expires < '2016-08-06' ORDER BY expires FOR UPDATE; Hang
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction从以上的测试过程以及InnoDB状态可以看出:
- 在REPEATABLE-READ隔离级别下, 不使用LIMIT情况下, Session 1使用的是间隙锁(gap lock), Session 2是无法在expires < '2016-05-06'的间隙中插入数据的
Love truth but pardon errors
