MySQL Forums
Forum List  »  InnoDB

Deadlocks on inserts to the same table
Posted by: Radu Chiriac
Date: January 03, 2005 04:15PM

Hi

I've got the following table:
CREATE TABLE category (
id bigint(20) NOT NULL auto_increment,
client_id bigint(20) default NULL,
client_category_id varchar(250) default NULL,
category_name varchar(250) default NULL,
last_modified timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY client_id (client_id, client_category_id),
CONSTRAINT category_ibfk_1 FOREIGN KEY (client_id) REFERENCES client (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

When two concurrent threads attempt to insert a record into this table a deadlock is reported.

SHOW INNODB STATUS displays:
------------------------
LATEST DETECTED DEADLOCK
------------------------
050103 22:19:32
*** (1) TRANSACTION:
TRANSACTION 0 39965, ACTIVE 1 sec, process no 5829, OS thread id 962572 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 227, query id 762192 xaziain 127.0.0.1 web update
insert into category (client_id, client_category_id, category_name, last_modified) values (1,
'testCategory-0-1104783571964', 'testCategory-0-1104783571964', '2005-01-03 22:19:31')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1212 page no 4 n bits 272 index `client_id` of table `testdb/category` trx id 0 39965 lock mode S
waiting
Record lock, heap no 203 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
0: len 8; hex 8000000000000001; asc ;; 1: len 28; hex 7465737443617465676f72792d302d31313034373833353731393634;
asc testCategory-0-1104783571964;;2: len 8; hex 80000000000001b3; asc ;;

*** (2) TRANSACTION:
TRANSACTION 0 39964, ACTIVE 1 sec, process no 5746, OS thread id 958475 setting auto-inc lock, thread declared inside
InnoDB 500
mysql tables in use 1, locked 1
11 lock struct(s), heap size 1024, undo log entries 10
MySQL thread id 226, query id 762205 xaziain 127.0.0.1 web update
insert into category (client_id, client_category_id, category_name, last_modified) values (1,
'testCategory-4-1104783571964', 'testCategory-4-1104783571964', '2005-01-03 22:19:32')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1212 page no 4 n bits 272 index `client_id` of table `testdb/category` trx id 0 39964 lock_mode X
locks rec but not gap
Record lock, heap no 203 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
0: len 8; hex 8000000000000001; asc ;; 1: len 28; hex 7465737443617465676f72792d302d31313034373833353731393634;
asc testCategory-0-1104783571964;;2: len 8; hex 80000000000001b3; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `testdb/category` trx id 0 39964 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (1)


If I'm not misinterpreting the innodb status output the deadlock occurs because transaction 1 acquired the auto-inc lock and waits for a lock on the unique index 'client_id', while transaction 2 holds the lock on the unique index and waits for the auto-inc lock...
But why does it happen this way? Wouldn't it be natural for mysql in such situations (when executing inserts to the same table) to obtain locks in the same order? I mean shouldn't both transactions obtain locks in the same order, e.g. - first obtain the auto-inc lock and then the index lock? Is there any innodb parameter or INSERT statement hint that would allow to specify the order in which to obtain the locks?

Thank you in advance,
Radu

Options: ReplyQuote


Subject
Views
Written By
Posted
Deadlocks on inserts to the same table
4321
January 03, 2005 04:15PM


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.