mysql - 存储过程中的 MySQL 解除阻塞

Question

我有下表 user_sessions：

CREATE TABLE `user_sessions` (
  `session_key` varchar(36) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `guid` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`session_key`),
  KEY `FK_user_sessions_user_logins` (`user_id`),
  CONSTRAINT `FK_user_sessions_user_logins` FOREIGN KEY (`user_id`) REFERENCES `user_logins` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后我有 SP 正在做一些逻辑。SELECT除了我正在user_sessions使用以下查询进行更新的一个查询之外，此 SP 中的几乎所有语句都除外：

UPDATE user_sessions
    SET updated_at = CURRENT_TIMESTAMP, guid = @guid
WHERE session_key = _token;

在此查询中，我收到此错误：Error Code: 1213. Deadlock found when trying to get lock; try restarting transaction

这是 MySQL v 5.7 的日志

*** (1) TRANSACTION:
TRANSACTION 85234691, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 108 lock struct(s), heap size 24784, 7702 row lock(s), undo log entries 1
MySQL thread id 176542, OS thread handle 46953148479232, query id 66288172 1.1.1.1 db updating
UPDATE user_sessions
                    SET updated_at = CURRENT_TIMESTAMP, guid = @guid
                WHERE session_key = _token
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2614 page no 3 n bits 88 index PRIMARY of table `db`.`user_sessions` trx id 85234691 lock_mode X locks rec but not gap waiting
Record lock, heap no 21 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 30; hex 61373366393238612d313137642d313165632d386638322d306135303537; asc a73f928a-117d-11ec-8f82-0a5057; (total 36 bytes);
 1: len 6; hex 0000051493f0; asc       ;;
 2: len 7; hex bc000001ee0110; asc        ;;
 3: len 8; hex 800000000000000a; asc         ;;
 4: len 1; hex 81; asc  ;;
 5: len 5; hex 99aa92ed4b; asc     K;;
 6: len 5; hex 99aa92ed4b; asc     K;;
 7: len 30; hex 6b6f6c656b746f2d6170692d383164643233366638393865343932666234; asc db-api-81dd236f898e492fb4; (total 44 bytes);

*** (2) TRANSACTION:
TRANSACTION 85234689, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
108 lock struct(s), heap size 24784, 7702 row lock(s), undo log entries 1
MySQL thread id 176541, OS thread handle 46953147946752, query id 66288171 1.1.1.1 db updating
UPDATE user_sessions
                    SET updated_at = CURRENT_TIMESTAMP, guid = @guid
                WHERE session_key = _token
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2614 page no 3 n bits 88 index PRIMARY of table `db`.`user_sessions` trx id 85234689 lock mode S locks rec but not gap
Record lock, heap no 21 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 30; hex 61373366393238612d313137642d313165632d386638322d306135303537; asc a73f928a-117d-11ec-8f82-0a5057; (total 36 bytes);
 1: len 6; hex 0000051493f0; asc       ;;
 2: len 7; hex bc000001ee0110; asc        ;;
 3: len 8; hex 800000000000000a; asc         ;;
 4: len 1; hex 81; asc  ;;
 5: len 5; hex 99aa92ed4b; asc     K;;
 6: len 5; hex 99aa92ed4b; asc     K;;
 7: len 30; hex 6b6f6c656b746f2d6170692d383164643233366638393865343932666234; asc db-api-81dd236f898e492fb4; (total 44 bytes);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2614 page no 3 n bits 88 index PRIMARY of table `kolekto`.`user_sessions` trx id 85234689 lock_mode X locks rec but not gap waiting
Record lock, heap no 21 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 30; hex 61373366393238612d313137642d313165632d386638322d306135303537; asc a73f928a-117d-11ec-8f82-0a5057; (total 36 bytes);
 1: len 6; hex 0000051493f0; asc       ;;
 2: len 7; hex bc000001ee0110; asc        ;;
 3: len 8; hex 800000000000000a; asc         ;;
 4: len 1; hex 81; asc  ;;
 5: len 5; hex 99aa92ed4b; asc     K;;
 6: len 5; hex 99aa92ed4b; asc     K;;
 7: len 30; hex 6b6f6c656b746f2d6170692d383164643233366638393865343932666234; asc kolekto-api-81dd236f898e492fb4; (total 44 bytes);

*** WE ROLL BACK TRANSACTION (2)

我尝试使用捕获死锁的包装函数并再尝试 2 次将 SP 作为准备好的语句调用，它几乎在所有情况下都有帮助，但是由于我添加了 user_sessions 表 - 它停止了。另外，尝试使用SELECT FOR UPDATE但没有成功

标签： mysql