There is a case which makes me a bit confused. As far as I understand deadlocks should appear if there are two query statements (including subqueries) in request (each transaction). So the ordering of these statements may be not proper, so they may lock each other (across two transactions). But what are possible causes of deadlocks when each transaction (request with auto commit) has a single statement (without subqueries)? Could someone prove, that if a select statement uses "update where in" clause against a key field, it actually makes two locks: one for all keys in the table since the row is updated, and the other one for the key in criteria since "in" operator is used?

It seems like this question was unclear, so I post some content from InnoDB status query (some sensitive data were hidden):

2016-02-08 08:47:09 2b9abb9f8700
TRANSACTION 13865307, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 152875, OS thread handle 0x2b99d4797700, query id 4452586 {{IP-Address}} {{Username}} update
INSERT INTO friend (user_id, friend_id) VALUES ({{User-ID}}, {{Friend-ID}})
RECORD LOCKS space id 40 page no 1147 n bits 88 index `PRIMARY` of table `{{DB-Name}}`.`user` trx table locks 2 total table locks 5  trx id 13865307 lock mode S locks rec but not gap waiting lock hold time 0 wait time before grant 0 
TRANSACTION 13865292, ACTIVE 1 sec fetching rows
mysql tables in use 1, locked 1
2476 lock struct(s), heap size 276008, 58981 row lock(s)
MySQL thread id 151667, OS thread handle 0x2b9abb9f8700, query id 4452574 {{IP-Address}} {{Username}} updating
UPDATE user SET user.app_status = '1' WHERE (user.app_id = 1) AND (user.app_user_id in ({{App-User-ID}}))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 40 page no 1147 n bits 88 index `PRIMARY` of table `{{DB-Name}}`.`user` trx table locks 1 total table locks 5  trx id 13865292 lock_mode X lock hold time 0 wait time before grant 0 
RECORD LOCKS space id 40 page no 6095 n bits 88 index `PRIMARY` of table `{{DB-Name}}`.`user` trx table locks 1 total table locks 5  trx id 13865292 lock_mode X waiting lock hold time 0 wait time before grant 0 

So the question arising is the following: How it is possible, that a single UPDATE (transaction no. 2) requires two locks on the same key (PRIMARY) and goes into a deadlock? My initial hypothesis was, that it may be connected with WHERE IN statement (though app_user_id is a regular key). So I tried to clarify, whether this criteria may cause a double acquiring of the lock. And if it does, then could a simple check for equality (namely, WHERE user.app_user_id = '{{App-User-ID}}') make this update more atomic?

Some notes regarding database instance. This case happened on 10.0.17-MariaDB. But as far as I understand, its behavior should be similar to MySQL 5.6, since they are mutually compatible. Each table has a sequential primary key; there are two foreign keys from user table to friend one; and also app_user_id is an indexed field.

Pardon me for being not clear enough originally and too verbose, but I primary work with in-application concurrency, so competitions happening on database layer are not always clear and predictable for me.

Here are create statements for user and friend tables as asked in comments by Rick James

  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `app_id` int(11) NOT NULL,
  `app_user_id` char(100) NOT NULL,
  `full_name` char(250) NOT NULL,
  `first_name` char(100) DEFAULT NULL,
  `last_name` char(100) DEFAULT NULL,
  `image` char(250) DEFAULT NULL,
  `session_id` char(50) DEFAULT NULL,
  `last_date` datetime DEFAULT NULL,
  `app_status` tinyint(1) NOT NULL DEFAULT '0',
  `app_last_date` datetime DEFAULT NULL,
  `date_create` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_app_user_id` (`app_user_id`),
  KEY `app_id` (`app_id`),
  KEY `app_user_id` (`app_user_id`)

CREATE TABLE `friend` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `friend_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK__user` (`user_id`),
  KEY `FK__user_2` (`friend_id`),
  CONSTRAINT `FK__user_2` FOREIGN KEY (`friend_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
  • Please provide SHOW CREATE TABLE for the tables involved in this deadlock. – Rick James Feb 12 '16 at 5:51
  • Please show us any SELECT ... FOR UPDATE statements leading into this. – Rick James Feb 12 '16 at 5:52
  • BEGIN...COMMIT? Or autocommit=0? – Rick James Feb 12 '16 at 5:52
  • Thank you for comments, Rick James. I have included create statement in the body of the request. Regarding transactions, they are not started implicitly, and the autocommit flag is ON. Regarding SELECT ... FOR UPDATE, this kind of statements is not used and all operations are presented as single (isolated) queries. – sviklim Feb 12 '16 at 10:48

What's a "double friend"? (OK, I made that up.) Your schema allows a user to have the same friend twice. Let's get rid of that, and hope that it helps your real problem. That table should be (ignoring the FKs -- which may be contributing to the problem):

CREATE TABLE `friend` (
  -- leave this out:  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,  -- unless you expect more than 4 billion users
  `friend_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, friend_id),
  INDEX       (friend_id, user_id)
) Engine=InnoDB;

Discussion of many-to-many.

Perhaps you do not want ON DELETE CASCADE -- Won't that remove users prematurely? (I'm not a fan of FKs.)

(Unrelated:) You have a redundant index on app_user_id.

  • Thank you for this idea Rick James. Though the schema became a bit 'lighter', I'm not sure this will fix issue, since it does not explain what caused the dead lock. Regarding the primary key in friends; most of ORM solutions work not great with combined primaries, so every entity has an own 'id' field, even if it can be avoided. Regarding foreign keys; user and friend ids both come from 'user' table, so we need to ensure that all references to and from a user are deleted when he/she leaves us. – sviklim Feb 13 '16 at 9:55
  • <soap-box>Yet another case of 3rd party software "getting in the way".</soap-box> Having PRIMARY KEY(id) is extra step in locking the row(s); I don't really know, but it might lead to an "unnecessary" deadlock. – Rick James Feb 13 '16 at 18:12
  • When doing that INSERT in the deadlock, the FOREIGN KEYs require (I think) 2 probes into user to validate that the 'parent' ids exist. This probably locks those rows to prevent there deletion. If you are also changing the status of one of them at the same time, then there is some level of conflict. (No, I don't yet see the need for a full-blown 'deadlock'.) – Rick James Feb 13 '16 at 18:52
  • The recommendation about getting rid of id will probably help diminish the locks caused by the indirect connection to the PRIMARY KEY. – Rick James Nov 28 '16 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.