MySQL Forums
Forum List  »  InnoDB

Deadlock occuring since setting a unique index.
Posted by: malo yamakado
Date: April 06, 2009 09:45PM

Hello,

I recently add a unique index to one of the field of a innodb table.
Since then, deadlock occurs very often.

The table is storing the map relationship between an url and its actual end point url (after redirection).
Several processes are storing for some set of urls this relationship into the table.
These processes also are accessing the table on read in order to check if the url is already into the table.

If anybody has an idea on how to avoid to get deadlock, please help me! Thank you very much.

The SHOW INNODB STATUS; command shows the following information:

------------------------
LATEST DETECTED DEADLOCK
------------------------
090407  1:31:32
*** (1) TRANSACTION:
TRANSACTION 0 24666416, ACTIVE 1070 sec, process no 4714, OS thread id 1159256400 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 14320, undo log entries 129
MySQL thread id 33610, query id 13722156 localhost root update
INSERT INTO effective_url_map (URL,URL_STATUS,EFFECTIVE_URL,EFFECTIVE_URL_STATUS) VALUES ('http://www.yasamkadin.com/2009/04/06/kudus-te-sanal-gezintigitmis-kadar-olacaksiniz.html',200,'http://www.yasamkadin.com/2009/04/06/kudus-te-sanal-gezintigitmis-kadar-olacaksiniz.html',200)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `pressarmy/effective_url_map` trx id 0 24666416 lock mode AUTO-INC waiting
*** (2) TRANSACTION:
TRANSACTION 0 24670702, ACTIVE 12 sec, process no 4714, OS thread id 1158723920 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216, undo log entries 2
MySQL thread id 33598, query id 13722125 localhost root update
INSERT INTO effective_url_map (URL,URL_STATUS,EFFECTIVE_URL,EFFECTIVE_URL_STATUS) VALUES ('http://www.yasamkadin.com/rss.xml',200,'http://www.yasamkadin.com/rss.xml',200)
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `pressarmy/effective_url_map` trx id 0 24670702 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 114634 n bits 248 index `url_unique` of table `pressarmy/effective_url_map` trx id 0 24670702 lock mode S waiting
Record lock, heap no 99 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 687474703a2f2f7777772e796173616d6b6164696e2e636f6d2f7273732e; asc http://www.yasamkadin.com/rss.;...(truncated); 1: len 4; hex 80070b42; asc    B;;

Options: ReplyQuote


Subject
Views
Written By
Posted
Deadlock occuring since setting a unique index.
8952
April 06, 2009 09:45PM


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.