MySQL Bugs: #61965: deadlock simultaneously insert on tbl1 and update on tbl2 from diff tx, innodb

Bug #61965	deadlock simultaneously insert on tbl1 and update on tbl2 from diff tx, innodb
Submitted:	24 Jul 2011 20:25	Modified:	3 Aug 2011 22:41
Reporter:	Federico Jakimowicz	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	deadlock, innodb, insert, transactions, UPDATE

Description:
When doing a new insert on table "A" and at same time updating an exisiting row on table "B" from 2 transactions simultaneously a deadlock is found.
Table A has a fk that references the id of table 1. Both transactions are attempting to update the same row on table "B".

The innodb status is the follwing (see how to repeat steps)

when: c2>INSERT INTO addscore (user_id, value) VALUES (1, 10);

------------
TRANSACTIONS
------------
Trx id counter 504
Purge done for trx's n:o < 3B2 undo n:o < 0
History list length 44
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 4, query id 31 localhost 127.0.0.1 root
SHOW ENGINE INNODB STATUS
---TRANSACTION 503, ACTIVE 111 sec
MySQL thread id 3, query id 28 localhost 127.0.0.1 admin
Trx read view will not see trx with id >= 504, sees < 502
---TRANSACTION 502, ACTIVE 180 sec
3 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 2, query id 30 localhost 127.0.0.1 admin
Trx read view will not see trx with id >= 503, sees < 503
--------

when: c1>UPDATE is excecuted .

------------
TRANSACTIONS
------------
Trx id counter 504
Purge done for trx's n:o < 3B2 undo n:o < 0
History list length 44
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 4, query id 33 localhost 127.0.0.1 root
SHOW ENGINE INNODB STATUS
---TRANSACTION 503, ACTIVE 179 sec
3 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 3, query id 32 localhost 127.0.0.1 admin
Trx read view will not see trx with id >= 504, sees < 502
---TRANSACTION 502, ACTIVE 248 sec
3 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 2, query id 30 localhost 127.0.0.1 admin
Trx read view will not see trx with id >= 503, sees < 503
--------

How to repeat:
1) Create the following 2 tables:

CREATE TABLE `users` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(250) NOT NULL COLLATE 'latin1_spanish_ci',
	`score` BIGINT(20) NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`),
	UNIQUE INDEX `id` (`id`)
)
COLLATE='latin1_spanish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=0

CREATE TABLE `addscore` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`user_id` BIGINT(20) NOT NULL,
	`value` INT(10) NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`),
	UNIQUE INDEX `id` (`id`),
	INDEX `FK_addscore_users` (`user_id`),
	CONSTRAINT `FK_addscore_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
)
COLLATE='latin1_spanish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=0

--------------------------------------------------------------------

2) open 2 sql clients (c1 and c2) and run the follwing steps:

c1>START TRANSACTION;
c2>START TRANSACTION;
c1>SELECT * FROM users WHERE id=1;
c2>SELECT * FROM users WHERE id=1;
c1>INSERT INTO addscore (user_id, value) VALUES (1, 10);
c2>INSERT INTO addscore (user_id, value) VALUES (1, 10);
c1>UPDATE users SET score=10 WHERE id=1; //here process gets suspended waiting for an unlock
c2>UPDATE users SET score=10 WHERE id=1; //process throws a deadlock error

--------------------------------------------------------------------

Suggested fix:
Not 100% sure if this is a bug.
Changing the excecution order of the script excecuting first the updates and then the inserts solves the problem since the update from tx2 is must wait for tx1 to commit. However it seems a bit tricky situation and in some cases difficult to predict. This sql excecution order is the order hibernate excecutes the sql it first does inserts then updates.

Maybe it would be better if when doing the insertion the lock is not done over the "users" table referenced by the foreign key "user_id" on "addscore" table.

Does this quote (http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html):

"If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails."

explain what happens and why?

Side note: there is no need to define UNIQUE INDEX(id) if id is defined as PRIMARY KEY.

Hi Valeriy,

Thanks a lot for your tip I have been reading on that and also found this one  http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html .
So it seems there is no other way than removing the fk, changing the excecution order to perform the updates before de insert or using select for update?

kind regards.

I have been doing some tests on oracle 10g and this locking issue does not happen. It just put the 2nd tx in wait when attemps to update user.
Maybe that would be a more desirable behaviour?
I will read more about oracle locking.

Locking in every RDBMS is done differently. Oracle RDBMS does not set read (S, shared) locks on rows at all. InnoDB does, in some cases. I doubt this can be changed.

Thank you for the feedback.

This is duplicate of already verified feature request.