StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHowto bulk update a InnoDB table without deadlocks?
text
Body
copied!<p>I have two tables, one having a many-to-many relationship (<code>fooBarTable</code> with columns <code>fooId</code> and <code>barId</code>) and another InnoDB table <code>fooCounterTable</code> with columns <code>fooId</code> and <code>counter</code> counting the occurences of <code>fooId</code> in <code>fooBarTable</code>.</p> <p>When deleting all <code>barId</code>'s from <code>fooBarTable</code>, I need to update the <code>fooCounterTable</code> accordingly.</p> <p>The first thing I tried was this:</p> <pre><code>UPDATE fooCounterTable SET counter = counter - 1 WHERE fooId IN (SELECT fooId FROM fooBarTable WHERE barId = 42 ORDER BY fooId); </code></pre> <p>But I got this error:</p> <pre><code>MySQL error (1205): Lock wait timeout exceeded; try restarting transaction </code></pre> <p>Updating the table when adding <code>barId</code>'s is working fine with this SQL statement:</p> <pre><code>INSERT INTO `fooCounterTable` (fooId, counter) VALUES (42,1), (100,1), (123,1) ON DUPLICATE KEY UPDATE counter = counter + 1; </code></pre> <p>So I thought I'd do the same thing when decreasing the counter, even if it looks stupid to insert 0-Values, which should never happen:</p> <pre><code>INSERT INTO `fooCounterTable` (SELECT fooId, 0 FROM fooBarTable WHERE barId = 42 ORDER BY fooId) ON DUPLICATE KEY UPDATE counter = counter - 1;' </code></pre> <p>This seems to work fine in most cases, but sometimes I get a deadlock:</p> <pre><code>MySQL error (1213): Deadlock found when trying to get lock; try restarting transaction </code></pre> <p>So I read about deadlocks and found out about <code>SELECT ... FOR UPDATE</code> and I tried this:</p> <pre><code>START TRANSACTION; SELECT fooId FROM fooCounterTable WHERE fooId IN (SELECT fooId FROM fooBarTable WHERE barId = 42 ORDER BY fooId) FOR UPDATE; UPDATE fooCounterTable SET counter = counter - 1 WHERE fooId IN (SELECT fooId FROM fooBarTable WHERE barId = 42 ORDER BY fooId); COMMIT; </code></pre> <p>which resulted in:</p> <pre><code>MySQL error (2014): commands out of sync </code></pre> <p>Can anyone tell me how to resolve my problem?</p> <p><strong>Update</strong></p> <p>The last error (2014) occured, because I did not use and free the SELECT statement's results before executing the UPDATE statement, which is mandatory. I fixed that and I got rid of error 2014, but I still have deadlocks (error 1205) from time to time and I don't understand, why.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload