Why I'm getting a deadlock from mysql using SELECT ... FOR UPDATE lock?

I have two threads, they have to update the same table but the first one is using a primary key to lock a single record, the second thread have to lock a set of records using another index. The lock is made with SELECT ... FOR UPDATE steatment, I cannot understand why they run into a deadlock.

This is the table:

CREATE TABLE `ingressi` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `evento` int(10) unsigned NOT NULL,
 `stato` int(10) unsigned NOT NULL,
 ....,
 ....,
 PRIMARY KEY (`id`),
  KEY `evento` (`evento`,`stato`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

This is the query log (Please mind the connection):

    43 Query    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
    43 Query    set autocommit=0
    43 Query    SELECT stato FROM ingressi WHERE id=1 FOR UPDATE
    39 Query    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
    39 Query    set autocommit=0
    39 Query    SELECT count(*) FROM ingressi WHERE evento=66 FOR UPDATE
    43 Query    UPDATE `ingressi` SET stato=0 WHERE id=1
    43 Query    COMMIT

Just after the last query the deadlock error is thrown.

(conn=39) Deadlock found when trying to get lock; try restarting transaction

The 43 and 39 connection are JDBC connection of a Spring-Java application using a connection pool.

Why the second connection doesn't wait and it was deadlocked?

Solution

When you use a secondary index to locate and lock a row, MySQL will first lock the entry in the secondary index, and, after that, the corresponding row in the primary key. This double step can cause your deadlock.

Let's assume the following row:

+----+--------+-------+
| id | evento | stato |
+----+--------+-------+
|  1 |     66 |    10 |
+----+--------+-------+

Your first transaction uses the primary key to find the row with id=1, and places an exclusive lock on it.
Your second transaction uses the index (evento, strato) to find entries with evento=66, places an exclusive lock on that entry (in the secondary index), and then tries to get an exclusive lock in the row in the primary key. Since it is already locked, it has to wait.
Your first transaction now wants to update that row. It has an exclusive lock, so it's fine. But that update changes strato. Since it is indexed, the corresponding entry in the index (evento, strato) has to be modified (which requires an exclusive lock). Unfortunately, the second transaction has an exclusive lock on it, so the first transaction has to wait for the second transaction.
Since the second transaction already waits for the first transaction, we have a deadlock

So how to prevent it?

For your specific situation, you could use a different secondary index to find your row, e.g. KEY evento1 (evento). If MySQL uses this index (and to make sure, you can use SELECT count(*) FROM ingressi FORCE INDEX (evento1) WHERE evento=66 FOR UPDATE), this should prevent this specific deadlock:

the first transaction locks primary key id=1
the second transaction locks the entry (evento1=66,id=1) in the secondary index evento1
the second transaction tries to get a lock for primary key id=1, which is locked, so it waits
the first transaction updates the row and needs to get a lock on entry (evento1=66,stato=10,id=1) in the secondary index evento. This time, it works, as this is now not locked
the first transaction commits, releases the lock on primary key id=1 and the second transaction can finish

If that is a reasonable solution for your specific situation will depend on, well, your specific situation. It might e.g. be overkill to add an index you don't actually want or need just to prevent a deadlock that happens twice a year. Or maybe you have more of those situations which are all slightly different and that may need a different approach. You may find some additional general guidelines at e.g. How to Minimize and Handle Deadlocks.