Getting deadlock in MariaDB(10.1) when updating user's last activity in the table.
The query causing the error is
UPDATE auth_sessions SET last_activity_time='2018-12-21 05:45:39 WHERE id= 481;
We execute this query inside the procedure whenever users perform any action on the application.
Below is the status received from 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
Table Schema - Auth Session
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
;
Any clue or solution to resolve this issue?
If you have a multi-statement transaction (which you don't):
Sometimes the cure for problems similar to this is to have
SELECT ... WHERE id= 481 FOR UPDATE;
before the UPDATE, but inside the transaction.
Not all deadlocks are preventable. It is best to be prepared to handle deadlocks. This particular one can probably be correctly handled by:
Plan A (preferred): Replay the UPDATE.
Plan B (probably ok, considering the query's purpose): Ignore the deadlock.
Plan C (I don't know if it will work; if it does, is should eliminate this kind of deadlock):
UPDATE auth_sessions
SET last_activity_time = NOW()
WHERE last_activity_time != NOW()
AND id = 481;
The idea is to avoid trying to update if the value is already set to the desired time.