MySQL Forums
Forum List  »  InnoDB

How to avoid deadlocks in INNODB
Posted by: Sarat Kakumanu
Date: December 15, 2005 04:55PM

I thought INNODB was supposed to do row-level locking, but I am running into a deadlock and if I do 'SHOW INNODB STATUS', I get the details of the DEADLOCK and it says that a table is locked. Any ideas on why this would happen?

The table user below is indexed on user_id, so I am not sure why these two statements would block each other.


------------------------
LATEST DETECTED DEADLOCK
------------------------
051215 16:41:22
*** (1) TRANSACTION:
TRANSACTION 0 19686196, ACTIVE 0 sec, process no 18498, OS thread id 8424368 set
ting table lock
mysql tables in use 1, locked 0
LOCK WAIT 1 lock struct(s), heap size 320
I am running into an issue with one of my tables

MySQL thread id 19, query id 1338041 192.168.253.59 192.168.253.58 javawe
b_local System lock
update user set last_connection_datetime='2005-12-15 17:48:20' where user_id=5
01758
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test_db/user` trx id 0 19686196 lock mode X waiting
*** (2) TRANSACTION:
TRANSACTION 0 19686195, ACTIVE 0 sec, process no 18498, OS thread id 9972656 set
ting table lock
mysql tables in use 1, locked 0
22 lock struct(s), heap size 2496, undo log entries 7
MySQL thread id 2, query id 1338109 192.168.253.59 192.168.253.58 javaweb
_local System lock
update user set inner_label=NULL, outer_label=NULL where user_id=401331
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `etrace_prod_dbo/gwuser` trx id 0 19686195 lock mode IS
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `etrace_prod_dbo/gwuser` trx id 0 19686195 lock mode X waiting
*** WE ROLL BACK TRANSACTION (1)

Options: ReplyQuote


Subject
Views
Written By
Posted
How to avoid deadlocks in INNODB
12032
December 15, 2005 04:55PM
4363
December 15, 2005 08:00PM
3629
December 16, 2005 12:20PM
3674
December 16, 2005 06:03PM
4919
December 16, 2005 04:12PM
3100
December 16, 2005 06:05PM
3139
December 19, 2005 10:06AM
3435
December 17, 2005 08:32PM
3338
December 17, 2005 08:40PM
3076
December 19, 2005 10:03AM
2740
December 19, 2005 10:33AM
2854
December 19, 2005 10:38AM
2630
December 20, 2005 04:04AM
2817
December 20, 2005 05:26PM
3016
January 05, 2006 09:58AM


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.