更新表中用户的最后一个活动时，在mariadb（10.1）中出现死锁。
导致错误的查询是
UPDATE auth_sessions SET last_activity_time='2018-12-21 05:45:39 WHERE id= 481;每当用户对应用程序执行任何操作时，我们都在过程中执行此查询。
以下是从接收到的状态 show engine innodb status ; ```
LATEST DETECTED DEADLOCK
2018-12-21 05:45:39 7fe5b8e6eb00
***(1) TRANSACTION:
TRANSACTION 3742528, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 12 lock struct(s), heap size 2936, 81 row lock(s)
MySQL thread id 1941, OS thread handle 0x7fe5b5df4b00, query id 43106 localhost 127.0.0.1 root updating
UPDATE auth_sessions
SET last_activity_time= NAME_CONST('time_now',_latin1'2018-12-21 05:45:39' COLLATE 'latin1_swedish_ci')
WHERE id= NAME_CONST('temp_session_id',481)
***(1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 470949 page no 6 n bits 160 index PRIMARY of table xfusion_auth_engine.auth_sessions trx table locks 5 total table locks 5 trx id 3742528 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
***(2) TRANSACTION:
TRANSACTION 3742527, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
12 lock struct(s), heap size 2936, 81 row lock(s)
MySQL thread id 1943, OS thread handle 0x7fe5b8e6eb00, query id 43123 localhost 127.0.0.1 root updating
UPDATE auth_sessions
SET last_activity_time= NAME_CONST('time_now',_latin1'2018-12-21 05:45:39' COLLATE 'latin1_swedish_ci')
WHERE id= NAME_CONST('temp_session_id',481)
***(2) HOLDS THE LOCK(S):
RECORD LOCKS space id 470949 page no 6 n bits 160 index PRIMARY of table xfusion_auth_engine.auth_sessions trx table locks 5 total table locks 5 trx id 3742527 lock mode S locks rec but not gap lock hold time 0 wait time before grant 0
***(2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 470949 page no 6 n bits 160 index PRIMARY of table xfusion_auth_engine.auth_sessions trx table locks 5 total table locks 5 trx id 3742527 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
***WE ROLL BACK TRANSACTION (2)
TRANSACTIONS
表架构-身份验证会话CREATE TABLE auth_sessions (id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto Increment ID',user_id VARCHAR(255) NULL DEFAULT NULL COMMENT 'User Email',user_key VARCHAR(255) NULL DEFAULT NULL COMMENT 'User Key',application_key VARCHAR(255) NULL DEFAULT NULL COMMENT 'Application Key',created DATETIME NULL DEFAULT NULL COMMENT 'Session Creation Time',expires DATETIME NULL DEFAULT NULL COMMENT 'Session Expiration Time',is_logged_in TINYINT(4) NULL DEFAULT NULL COMMENT 'Tells whether user is logged in or not ',session_key VARCHAR(255) NULL DEFAULT NULL COMMENT 'Session Key per user per application key',last_activity_time DATETIME NULL DEFAULT NULL COMMENT 'Last recorded time for any activity',session_key_bin BINARY(16) NULL DEFAULT NULL COMMENT 'Binary ID of Session Key',
PRIMARY KEY (id),
INDEX ix_session_key_bin (session_key_bin)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;
有解决这个问题的线索或方法吗？
1条答案
按热度按时间x8diyxa71#
如果您有多对账单交易（您没有）：
有时解决类似问题的方法是
在
UPDATE，但在事务内部。并不是所有的死锁都是可以预防的。最好做好处理死锁的准备。这个特殊的问题可能可以通过以下方式正确处理：
方案a（首选）：重播
UPDATE.方案b（考虑到查询的目的，可能还可以）：忽略死锁。
c计划（我不知道是否有效；如果是这样，则应消除这种死锁）：
这样做的目的是避免在值已设置为所需时间时尝试更新。