Can two mysql SELECT FOR UPDATE FROM mytable WHERE .. IN (..) deadlock?

Question

Can two or more instances of the following deadlock with one another?

START TRANSACTION
SELECT mycolumn FROM mytable WHERE myid IN (list-of-ids) FOR UPDATE
:
COMMIT

Points to keep in mind:

mycolumn is indexed
This is mariaDB/innoDB
There is only one table involved (mytable)

Example A:

Thread 1:

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (100,200) FOR UPDATE
    :
    COMMIT    

Thread 2: (note that IN-list is in opposite order)

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (200,100) FOR UPDATE
    :
    COMMIT

Could these threads possibly deadlock on the SELECT in mariaDB/innoDB?

Example B:

Thread 1:

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (100,200) FOR UPDATE
    :
    COMMIT    

Thread 2: (note that IN-list is in same order)

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (100,200) FOR UPDATE
    :
    COMMIT

Could these threads possibly deadlock on the SELECT in mariaDB/innoDB?

UPDATE: A traditional way of avoiding deadlocks is to be sure to acquire locks in the same order, so that you can't have Thread 1 owning lock A and waiting on lock B while Thread 2 owns lock B and is waiting on lock A. However, I've seen several posts suggesting that even getting a single lock can't be considered atomic, because there are asynchronous processes going on in the engine that could still result in deadlock (and as a corollary to this, advising developers to always expect deadlocks and recover from them by retrying the transaction). I'm really asking whether deadlock can be eliminated by using proper coding practices in innoDB.)

score 0 · Answer 1 · answered Aug 16 '17 at 21:31

This thread is discussing one example of deadlocks. There are dozens, maybe hundreds, of ways to get a deadlock.

Sure, sorting the ids might prevent the IN(100,200) case, but what about all the rest? Sure, go ahead and sort the ids. And be sure to have suitable indexes. And so on. These might at least diminish the frequency of deadlocks.

Another example of a deadlock cause: Lack of an index that leads to a table scan instead of an index lookup. I think this can lead to locking all rows in the table, thereby both slowing things down and by having more locks, thereby begging for a deadlock.

I'll tell you what I have said many a time: Deadlocks happen; live with it. Be ready to restart the transaction from the BEGIN.

If you ever get a second deadlock, you should probably abort the program and spend some quality time figuring out how to speed up the entire transaction. That is the best way to minimize deadlocks.

(OK, I'll admit that I don't know the definitive answer about IN(). Long ago (ver 4.1?), I thought I had proof that sorting was beneficial. Later I heard that MySQL sorted the IN list, thereby killing my theory. Or is all this hearsay evidence?)

markp-fuso · Answer 2 · 2017-08-08T17:41:03.543

What happens if you run 2 sessions concurrently, selecting the 2 data sets (myid = 100 / myid = 200) in reverse order, eg:

-- session #1 runs:
start transaction;
select mycolumn from mytable where myid = 100 for update;
do sleep(30);
select mycolumn from mytable where myid = 200 for update;
commit;

And while session #1 is sleeping:

-- session #2 runs:
start transaction;
select mycolumn from mytable where myid = 200 for update;
do sleep(30);
select mycolumn from mytable where myid = 100 for update;
commit;

Do you get a deadlock?

NOTE: (Obviously?) You can run the above test, selecting myid=100/200 in the same order, to see what happens re: deadlocking.

Can two mysql SELECT FOR UPDATE FROM mytable WHERE .. IN (..) deadlock?

2 Answers2