[MDEV-17561] Deadlocks happen often when updating different rows Created: 2018-10-28  Updated: 2018-11-26  Resolved: 2018-11-26

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update, Storage Engine - InnoDB
Affects Version/s: 10.2.18
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Ali Kalantari Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: deadlock, innodb, need_feedback
Environment:

MariaDB 10.2.18, Debian stretch 9.5

Issue Links:
Relates
relates to MDEV-13333 Deadlock failure that does not occur ... Closed

 Description   

The following deadlock error happens so often that it's driving me and our customers crazy:
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction.

I didn't have this issue so often before upgrading to MariaDB 10.2
I can see what queries cause Deadlock by pt-deadlock-logger, and they're never on the same record, two different records, having different data cause the issue.

server ts user db tbl idx lock_type lock_mode wait_hold victim query
db1 2018-10-28T12:38:08 user1 db tbl1 PRIMARY RECORD S w 1 UPDATE `db`.`tbl1` SET `plan` = 'bronze', `price` = 790000, `modified` = '2018-10-28 12:38:08' WHERE `db`.`tbl1`.`id` = 152216
db1 2018-10-28T12:38:08 user2 db tbl1 PRIMARY RECORD S w 0 UPDATE tbl1 SET employer_id = 15104 , modified = '2018-10-28 12:38:08' WHERE id = 152198


 Comments   
Comment by Ali Kalantari [ 2018-10-29 ]

I found the issue.
There was a trigger on the table with count(*) in its query.

Comment by Elena Stepanova [ 2018-10-29 ]

Is it a new trigger, which didn't exist when you were running a previous version?

Generated at Thu Feb 08 08:37:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.