mysql version:5.7.23-log
isolation level:read committed
mysql data
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for app_record_lock_test
-- ----------------------------
DROP TABLE IF EXISTS `app_record_lock_test`;
CREATE TABLE `app_record_lock_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hash` bigint(20) NOT NULL DEFAULT '0',
`cluster` varchar(256) NOT NULL,
`namespace` varchar(256) NOT NULL DEFAULT '',
`service` varchar(256) NOT NULL DEFAULT '',
`pod` varchar(256) NOT NULL DEFAULT '',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `cluster_hash` (`cluster`,`hash`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=120236025 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of app_record_lock_test
-- ----------------------------
INSERT INTO `app_record_lock_test` VALUES ('120236012', '1', 'cluster', 'namespace', 'service', 'pod', '2022-02-18 14:14:59', '2022-02-09 10:00:00');
INSERT INTO `app_record_lock_test` VALUES ('120236013', '2', 'cluster', 'namespace', 'service', 'pod', '2022-02-18 14:14:59', '2022-02-09 10:00:00');
INSERT INTO `app_record_lock_test` VALUES ('120236014', '3', 'cluster', 'namespace', 'service', 'pod', '2022-02-18 14:14:59', '2022-02-09 10:00:00');
session 1:
START TRANSACTION;
SELECT HASH
FROM
app_record_lock_test
WHERE
cluster = 'cluster' FOR UPDATE;
INSERT INTO app_record_lock_test ( HASH, cluster, namespace, service, pod, updated_at )
VALUES
( 1, 'cluster', 'namespace', 'service', 'pod', '2022-02-09 10:00:00' )
ON DUPLICATE KEY UPDATE updated_at = '2022-02-09 11:00:01';
COMMIT;
session 2:
START TRANSACTION;
SELECT HASH
FROM
app_record_lock_test
WHERE
cluster = 'cluster' FOR UPDATE;
INSERT INTO app_record_lock_test ( HASH, cluster, namespace, service, pod, updated_at )
VALUES
( 1, 'cluster', 'namespace', 'service', 'pod', '2022-02-09 10:00:00' )
ON DUPLICATE KEY UPDATE updated_at = '2022-02-09 11:00:01';
COMMIT;
Steps to reproduce
1、session 1 first execute select for update
2、session 2 execute select for update
3、session 1 executes insert，This is session 2 and an error will be reported
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
why is this?
show engine innodb status,before insert:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-02-21 16:13:48 0x7f2026049700
*** (1) TRANSACTION:
TRANSACTION 421085334, 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 41539545, OS thread handle 139773723899648, query id 20729357470 10.190.176.247 console Sending data
/* ApplicationName=DBeaver 21.3.5 - SQLEditor <Script-8.sql> */ SELECT HASH
FROM
app_record_lock_test
WHERE
cluster = 'cluster' FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12005 page no 4 n bits 72 index cluster_hash of table `console`.`app_record_lock_test` trx id 421085334 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 7; hex 636c7573746572; asc cluster;;
1: len 8; hex 8000000000000001; asc ;;
2: len 4; hex 872aa7ec; asc * ;;
*** (2) TRANSACTION:
TRANSACTION 421085333, ACTIVE 13 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 41539561, OS thread handle 139776053516032, query id 20729357538 10.190.176.247 console update
/* ApplicationName=DBeaver 21.3.5 - SQLEditor <Script-5.sql> */ INSERT INTO app_record_lock_test ( HASH, cluster, namespace, service, pod, updated_at )
VALUES
( 1, 'cluster', 'namespace', 'service', 'pod', '2022-02-09 10:00:00' )
ON DUPLICATE KEY UPDATE updated_at = '2022-02-09 11:00:01'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 12005 page no 4 n bits 72 index cluster_hash of table `console`.`app_record_lock_test` trx id 421085333 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 7; hex 636c7573746572; asc cluster;;
1: len 8; hex 8000000000000001; asc ;;
2: len 4; hex 872aa7ec; asc * ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12005 page no 4 n bits 72 index cluster_hash of table `console`.`app_record_lock_test` trx id 421085333 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 7; hex 636c7573746572; asc cluster;;
1: len 8; hex 8000000000000001; asc ;;
2: len 4; hex 872aa7ec; asc * ;;
*** WE ROLL BACK TRANSACTION (1)
after insert:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-02-22 09:26:16 0x7f216925a700
*** (1) TRANSACTION:
TRANSACTION 421416755, ACTIVE 22 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 41565582, OS thread handle 139778307692288, query id 20731658463 10.190.176.247 console Sending data
/* ApplicationName=DBeaver 21.3.5 - SQLEditor <Script-8.sql> */ SELECT HASH
FROM
app_record_lock_test
WHERE
cluster = 'cluster' FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12005 page no 4 n bits 72 index cluster_hash of table `console`.`app_record_lock_test` trx id 421416755 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 7; hex 636c7573746572; asc cluster;;
1: len 8; hex 8000000000000001; asc ;;
2: len 4; hex 872aa7ec; asc * ;;
*** (2) TRANSACTION:
TRANSACTION 421416754, ACTIVE 26 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1
MySQL thread id 41565555, OS thread handle 139781474723584, query id 20731658615 10.190.176.247 console update
/* ApplicationName=DBeaver 21.3.5 - SQLEditor <Script-5.sql> */ INSERT INTO app_record_lock_test ( HASH, cluster, namespace, service, pod, updated_at )
VALUES
( 1, 'cluster', 'namespace', 'service', 'pod', '2022-02-09 10:00:00' )
ON DUPLICATE KEY UPDATE updated_at = '2022-02-09 11:00:01'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 12005 page no 4 n bits 72 index cluster_hash of table `console`.`app_record_lock_test` trx id 421416754 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 7; hex 636c7573746572; asc cluster;;
1: len 8; hex 8000000000000001; asc ;;
2: len 4; hex 872aa7ec; asc * ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 7; hex 636c7573746572; asc cluster;;
1: len 8; hex 8000000000000002; asc ;;
2: len 4; hex 872aa7ed; asc * ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 7; hex 636c7573746572; asc cluster;;
1: len 8; hex 8000000000000003; asc ;;
2: len 4; hex 872aa7ee; asc * ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12005 page no 4 n bits 72 index cluster_hash of table `console`.`app_record_lock_test` trx id 421416754 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 7; hex 636c7573746572; asc cluster;;
1: len 8; hex 8000000000000001; asc ;;
2: len 4; hex 872aa7ec; asc * ;;
*** WE ROLL BACK TRANSACTION (1)