MySQL Forums
Forum List  »  InnoDB

Deadlock found when atempting to insert and update from diferent tx.
Posted by: Federico Jakimowicz
Date: July 23, 2011 05:54PM

Hi I have the follwing 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=1

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=1

when I open to mysql clients (c1 and c2) and follow this 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;
at this point a deadlock error is throwed by mysql.

If I remove the fk from addscore table this error does not happen anymore.
Also if I change the order of the statements and invoke the update before the inserts the update sentence of c2 stays waiting for c1 to release the lock and thus the deadlock does not happen.

Some info about the lock:

------------------------
LATEST DETECTED DEADLOCK
------------------------
110723 20:28:27
*** (1) TRANSACTION:
TRANSACTION 3AF, ACTIVE 32 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 6, query id 224 localhost 127.0.0.1 admin Updating
UPDATE users SET score=10 WHERE id=1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 510 n bits 72 index `PRIMARY` of table `test`.`users` trx id 3AF lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 00000000038b; asc ;;
2: len 7; hex e7000001ff0110; asc ;;
3: len 6; hex 706f6c61636f; asc polaco;;
4: len 8; hex 8000000000000000; asc ;;

*** (2) TRANSACTION:
TRANSACTION 3B0, ACTIVE 29 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 8, query id 225 localhost 127.0.0.1 admin Updating
UPDATE users SET score=10 WHERE id=1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 510 n bits 72 index `PRIMARY` of table `test`.`users` trx id 3B0 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 00000000038b; asc ;;
2: len 7; hex e7000001ff0110; asc ;;
3: len 6; hex 706f6c61636f; asc polaco;;
4: len 8; hex 8000000000000000; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 510 n bits 72 index `PRIMARY` of table `test`.`users` trx id 3B0 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 00000000038b; asc ;;
2: len 7; hex e7000001ff0110; asc ;;
3: len 6; hex 706f6c61636f; asc polaco;;
4: len 8; hex 8000000000000000; asc ;;

*** WE ROLL BACK TRANSACTION (2)

There is any way to prevent this deadlock without changing the execution order of the sentences? Can anyone explain me a bit how this deadlock happens?

Thanks in advanced.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.