Hello all,

I am using mysql 4.0.18 version. I got a deadlock while doing simultaneous transactions. I am attatching the INNODB status. I am not able to resolve why the deadlock is occurring.

There are two tables:
user_table (user_id - primary key, user_name)
user_transaction(transaction_id - primary key, xml_transaction, transaction_time, user_id (foreign key to user_table))

Sequence of operations that's being done
1) start transaction
2) obtain lock on user using select .. for update on user_table
3) insert transaction in user_transaction
4) some other operations
5) commit transaction

It looks like when adding a new row to user_transaction table, mysql db tries to obtain a shared lock. And another transaction tries to obtain an auto-inc lock on user_transaction table...

Are these two operations conflicting? or there is some other problem.

I would be grateful if someone can help me resolve the problem.

Thanks.

Sunil




051219 12:55:58
*** (1) TRANSACTION:
TRANSACTION 0 448233, ACTIVE 0 sec, OS thread id 2664 inserting
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1024, undo log entries 3
MySQL thread id 62, query id 8375 localhost 127.0.0.1 root update
insert into user_transaction (transaction_id, xml_transaction, transaction_time,
user_id) values (11, '<commhe_12_add trans_id=\"11\" item=\"18\" name=\"hello3\
" visible=\"true\" mxxxxxxx=\"true\" member_xxxxxxx=\"true\" />', '2005-12-19 1
2:55:58', 12)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1641 n bits 240 index `user_id` of table `mirage
/user_transaction` trx id 0 448233 lock_mode X locks gap before rec insert inten
tion waiting
Record lock, heap no 57 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits
0
0: len 8; hex 800000000000000d; asc ;; 1: len 4; hex 80000001; asc
;; 2: len 8; hex 8000000000000038; asc 8;;

*** (2) TRANSACTION:
TRANSACTION 0 448234, ACTIVE 0 sec, OS thread id 920 setting auto-inc lock, thre
ad declared inside InnoDB 500
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1024, undo log entries 2
MySQL thread id 61, query id 8378 localhost 127.0.0.1 root update
insert into user_transaction (transaction_id, xml_transaction, transaction_time,
user_id) values (9, '<commhe_12_add trans_id=\"9\" item=\"19\" name=\"hello1\"
visible=\"true\" moxxxxxxx=\"true\" member_xxxxxx1=\"true\" />', '2005-12-19 12:
55:58', 13)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1641 n bits 240 index `user_id` of table `mirage
/user_transaction` trx id 0 448234 lock_mode X
Record lock, heap no 57 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits
0
0: len 8; hex 800000000000000d; asc ;; 1: len 4; hex 80000001; asc
;; 2: len 8; hex 8000000000000038; asc 8;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `mirage/user_transaction` trx id 0 448234 lock mode AUTO-INC wa
iting
*** WE ROLL BACK TRANSACTION (2)