Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It's not the same lock - the lock transaction 1 has is on the (index) record only and not the gap lock. </p> <p>Here's what's happening:</p> <ol> <li>Transaction 2 gets a lock for the (index) record <em>but not the gap before the record</em> ('rec but not gap'), i.e. it has a record lock only.</li> <li>Transaction 1 tries to get a lock on the record and the gap before (i.e. a next-key lock), but can't because transaction 2 has a record lock (and so transaction 1 waits). </li> <li>Transaction 2 tries to get a lock on the record <em>and the gap before</em> (i.e. a next-key lock) and can't because Transaction 1 is already waiting for the same lock and is ahead of it in the queue.</li> <li>Deadlock.</li> </ol> <p>I'm not entirely sure why Transaction 2 doesn't acquire a next-key lock immediately - perhaps the process of obtaining the record lock and then the gap lock isn't atomic (in the general sense of the word). </p> <p>I think the issue is that you have a composite primary key (id, value1) but you are deleting from a range (specifying id only) - this requires gap locks. See <a href="http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html</a>, in particular:</p> <blockquote> <p>Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)</p> </blockquote> <p>Can you change your code so you specify the full primary key when deleting, i.e. id <em>and</em> value1?</p> <p>Other options:</p> <ul> <li>Retry the delete when there's a deadlock, e.g. catch the error in your code and then retry if it was caused by a deadlock. This approach is often easier said than done, especially in legacy applications, but it is recommended by the MySQL page on <a href="http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html" rel="noreferrer">how to cope with deadlocks</a>: </li> </ul> <blockquote> <p>Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.</p> </blockquote> <ul> <li>Lock the whole table with a table-level lock before issuing the delete statement. This may affect performance though and is a 'sledgehammer' approach.</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. PODeadlock issue when transaction tries to accuire a lock it's already holding
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMartin Wilson
    1. USMartin Wilson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODeadlock issue when transaction tries to accuire a lock it's already holding
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIt might be possible if I add an inner select of all possible keys inside an IN-statement or a join. It feels weird that the single delete-statement should a) not be atomic and b) not fast enough that any other delete actually gets locked by it.
      singulars
      1. PO
      1. USAndreas Wederbrand
    2. COHow often do you get these deadlocks? Is anything else happening in the transaction before the delete? How many concurrent threads are running the deletes? Could you post the code?
      singulars
      1. PO
      1. USMartin Wilson
    3. COIt happens a couple of times each day, always from two different machines. Nothing happens before the delete that relates to that table. Other things happen to other tables but that shouldn't matter. I'm not able to post the unscrambled code.
      singulars
      1. PO
      1. USAndreas Wederbrand
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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