[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 indexGEN_CLUST_INDEXof tabletest.renotrx 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 indexGEN_CLUST_INDEXof tabletest.renotrx 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 indexGEN_CLUST_INDEXof tabletest.renotrx 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 indexGEN_CLUST_INDEXof tabletest.renotrx 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 indexGEN_CLUST_INDEXof tabletest.renotrx 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 upselect * 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 indexexpiresof tabletest.reno1trx 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 upselect * 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 indexPRIMARYof tabletest.reno1trx 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 indexPRIMARYof tabletest.reno1trx 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 indexPRIMARYof tabletest.reno1trx 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 indexPRIMARYof tabletest.reno1trx 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 indexexpiresof tabletest.reno1trx 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 upselect * 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'的间隙中插入数据的
浙公网安备 33010602011771号