How to avoid deadlocks in INNODB
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)
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
2594
January 11, 2006 05:09AM
3016
January 05, 2006 09:58AM