mysql - Two duplicate delete queries using primary key causing deadlock -


i don't understand how 2 duplicate queries each delete single row against single table using primary key have deadlocked. can explain?

it seems me 1 of transactions should have gotten lock , other 1 have wait.

here's deadlock report, queries:

fri jun 01 2012 13:50:23 *** (1) transaction: transaction 3 1439005348, active 0 sec, process no 22419, os thread id 1166235968 starting index read mysql tables in use 1, locked 1 lock wait 2 lock struct(s), heap size 368 mysql thread id 125597624, query id 3426379709 node3-int 10.5.1.119 application-devel updating delete `sessdata` `sesskey` = '87edf1479a275557ac8280dca78ab886' , `name` = 'currentrequesturl'  *** (2) transaction: transaction 3 1439005340, active 0 sec, process no 22419, os thread id 1234073920 starting index read, thread declared inside innodb 0 mysql tables in use 1, locked 1 3 lock struct(s), heap size 1216 mysql thread id 125597622, query id 3426379705 node2-int 10.5.1.118 application-devel updating delete `sessdata` `sesskey` = '87edf1479a275557ac8280dca78ab886' , `name` = 'currentrequesturl'  *** roll transaction (2)

here's schema table:

create table  `application`.`sessdata` (   `sesskey` varchar(255) not null default '',   `name` varchar(255) not null default '',   `value` varchar(255) default null,   primary key  (`sesskey`,`name`) ) engine=innodb default charset=latin1;

a few other details:

mysql version: 4.1.21 isolation level: repeatable-read character set on the above columns: latin1

you using mysql version 4.1.21. 4.1 past end-of-life , 4.1.21 isn't latest 4.1 version. (extended support mysql 4.1 ended on december 31, 2009.) should upgrade @ least 5.0.96, though might come up-to-date 5.5.25. failing that, upgrade 4.1.22 minimum do, though won't fix problem.

if read last example in mysql 4.1 documentation see how deadlock occur if row being deleted had been selected shared lock earlier in transaction. likewise have acquired shared locks if there foreign key constraints involved. general problem is:

a acquires shared lock on x

b waits exclusive lock on x. has wait because of a's lock.

a waits exclusive lock on x. has wait because b ahead of in queue exclusive lock.

the way innodb handles locks, not upgrade a's shared lock exclusive while b waiting same exclusive lock, deadlock.

alternarely, may hitting bug when 2 statements both trying delete non-existent row (possibly deleted preceding third duplicate delete). possibly related to:


Comments

Post a Comment

Popular posts from this blog

java - Play! framework 2.0: How to display multiple image? -

i need display gallery of photos. here template: @(photos: list[photo]) @title = { <bold>gallery</bold> } @main(title,"photo"){ <ul class="thumbnails"> @for(photo <- photos) { <li class="span3"> <a href="#" class="thumbnail"> <img src="@photo.path" alt=""> </a> </li> } </ul> } and here controller method: public static result getphotos() { return ok(views.html.photo.gallery.render(photo.get())); } and here photo bean : @entity public class photo extends model { @id public long id; @required public string label; public string path; public photo(string path, string label) { this.path = path; this.label = label; } private static finder<long, photo> find = new finder<long, photo>( long.class, photo.class); public static list<phot...
Read more

gmail - Is there any documentation for read-only access to the Google Contacts API? -

i want read-only access user's google contacts. there stackoverflow member wanted this, , received following answer : yes, there is, use https://www.googleapis.com/auth/contacts.readonly scope , "view contacts". this exchange reference read-only access google contacts can find using google. simply using access token obtained attempt read usual google contacts api (i.e. https://www.google.com/m8/feeds ) fails insufficient permissions. similarly, using google contacts api on new target fails 404 errors (for example https://www.googleapis.com/auth/contacts.readonly/default/full ). is there documentation anywhere on how use api, or permissible way access google contacts api full read-write access?
Read more

php - Controller/JToolBar not working in Joomla 2.5 -

relevant code: jtoolbarhelper::custom('savecategories', 'save', '', 'save', false, false); ... <input type="hidden" name="controller" value="easyblogcontroller"> according can dig docs , own previous questions, should call savecategories() function in easyblogcontroller . i've tried setting value easyblog , easyblog.php (file name) current easyblogcontroller (class name). clicking on save button refreshes page. not redirect, echo or var_dump test code put in savecategories() function. var_dump ing jrequest::getvar('controller') , 'task' return correct values. creating controller object , using $controller->execute('task'); works. the values going depend on location of controller trying call, , have couple options here. typically there controller in base folder of component (likely components/com_easyblog) in file called controller.php , class name inside ...
Read more