MySQL Deadlock Explanation Needed

Mysql deadlock explanation needed

The first step is determining what the two queries are:

SELECT api_key, completed, compute_units, created, deleted, flags, func_name, group_id, hostname, is_meta, jid, label, language, num_children, parent_ujid, priority, process_id, restartable, status, type, uid, ujid, version, wid FROM jobs WHERE status='new' and is_meta=0 ORDER BY priority asc,jid asc FOR UPDATE

..and:

UPDATE jobs SET status='done' WHERE jid=10099

The first is a SELECT, the second is an UPDATE. But the key is the FOR UPDATE at the end of the SELECT, which I emphasized in bold.

The FOR UPDATE syntax is for a locking read - you can read the documentation about it here. The MySQL deadlock documentation suggestes using READ COMMITTED if you run into locking issues like these ones.

SHOW INNODB STATUS walk through

Explain deadlock in mysql

After reading this example from MySQL, I know the reason:

1) Transaction 01 (T1):

begin;
select * from test where id=1 for update; //it holds record_lock(id=1)

After executing that statement, T1 holds record lock (id=1)

2) Transaction 02 (T2):

begin;
select * from test where id=1 for update; //it waits for record_lock(id=1)

T2 is put into waiting queue because it is trying to acquire the lock that is held by T1.

3) Transaction 01:

select * from test where id>0 for update;`enter code here`

This statement is trying to acquire gap lock (from 1 to infinity), but T2 is waiting record-lock(id=1) in the queue, so it should wait for T2. Deadlock happens. Even T1 has record-lock(id=1), it can't even get this gap lock as T2 is waiting in queue.

Explain inexplicable deadlock

Firstly, deadlocks do not depend on explicit locking. MySQL's LOCK TABLE or using non-default transaction isolation modes are NOT required to have a deadlock. You can still have deadlocks if you never use an explicit transaction.

Deadlocks can happen on a single table, quite easily. Most commonly it's from a single hot table.

Deadlocks can even happen if all your transactions just do a single row insert.

A deadlock can happen if you have

• More than one connection to the database (obviously)
• Any operation that internally involves more than one lock.

What is not obvious, is that most of the time, a single row insert or update involves more than one lock. The reason for this is that secondary indexes also need to be locked during inserts / updates.

SELECTs won't lock (assuming you're using the default isolation mode, and aren't using FOR UPDATE) so they can't be the cause.

SHOW ENGINE INNODB STATUS is your friend. It will give you a bunch of (admittedly very confusing) information about deadlocks, specifically, the most recent one.

• You can't completely eliminate deadlocks, they will continue to happen in production (even on test systems if stress them properly)
• Aim for a very low amount of deadlocks. If 1% of your transactions deadlock, that is possibly too many.
• Consider changing the transaction isolation level of your transactions to read-committed IF YOU FULLY UNDERSTAND THE IMPLICATIONS
• ensure that your software handles deadlocks appropriately.

Understanding Deadlocks in MySQL

1. Update statements may set shared locks on secondary indexes or there are other statements within the 2nd transactions that set the shared lock before the update. As mysql manual says:

The UPDATE operation also takes shared locks on affected secondary index records when performing duplicate check scans prior to inserting new secondary index records, and when inserting new secondary index records.

However, in InnoDB all secondary indexes also include the primary key, so a secondary key lock does affect the primary key as well. But to be honest, if it had an exclusive lock only, that would not change the outcome.

2. No, you are not correct. In the 1st link mysql manual also says:

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.

You wrote in a comment that you added the unique index on application_number column after posting your question. This means that your 1st sql statement locks the entire table because id >'' matches all records and application_number field was not indexed.

So, regardless of the fact that your 1st sql statement updates a single record only, you managed to lock the entire table. Which brings us to your last question.

3. You cannot and must not avoid deadlocks: these are an essential feature of resolving certain race conditions. There are two things you can do:

a) handle the deadlock error (restart transaction, error message, etc).
b) minimize the chances of a deadlock occuring.

How can you minimize the chances of a deadlock occuring? Use appropriate indexes and where criteria to minimize the number of records locked by a statement; avoid using long running transactions; minimize the use of explicit locking (for update, for share).

Your adding the unique index on application_number is a really good start, but you must verify using explain that it is used by mysql.

How to explain the reason for this deadlock?

A Friend of mine explained this situation.

From the MYSQL documentation：

Deadlock occurs here because client A needs an X lock to delete the row. However, that lock request cannot be granted because client B already has a request for an X lock and is waiting for client A to release its S lock. Nor can the S lock held by A be upgraded to an X lock because of the prior request by B for an X lock. As a result, InnoDB generates an error for one of the clients and releases its locks

MySQL deadlocks explanation

Why is it holding a S lock and then waiting for a X lock of the same row... why didn't it get an X lock to begin with?

The 2nd query uses a select subquery to create a derived table. Select queries by default create a shared (S) lock only, unless you enable serialisable isolation level. You can explicitly instruct the select to use exclusive lock (well, intention exclusive, IX) by adding for update clause to the subquery, but I would be careful with that. You need to evaluate if the subquery could lock more records than the update part does and if it is worth locking these extra records.

In any case, why is transaction 1 blocking anything?

The select subquery of the 2nd query places an S lock on the given record. Then comes the 1st query along requesting an X lock on the same record, which cannot be granted immediatelly because of the S lock already there.

Whent the 2nd query tries to upgrade the lock to X, it finds that it is 2nd in the queue to get the X lock behind the first transaction. However, since the 2nd transaction is still running, the 2nd query cannot release the S lock, preventing the 1st transaction from completing.

Pls do not ask why mysql works this way because only a mysql developer can answer this question.

Why does mysql deadlock here?

Deadlocks are caused by inter-transaction ordering and lock acquisitions. Generally there is one active transaction per connection (although different databases may work differently). So it is only in the case of multiple connections and thus multiple overlapping transactions that deadlocks can occur. A single connection/transaction cannot deadlock itself because there is no lock it can't acquire: it has it, or it can get it.

An insert deadlock can be caused by a unique constraint - so check for a unique key constraint as a culprit. Other causes could be locks held for select "for update" statements, etc.

Also, ensure all transactions are completed immediately (committed or rolled back) after the operation(s) that require them. If a transaction is not closed in a timely manner it can lead to such deadlock behavior trivially. While "autocommit" usually handles this, it can be changed and should not be relied upon: I recommend proper manual transaction usage.

See Mysql deadlock explanation needed and How to Cope with Deadlocks for more information. In this case, it is likely sufficient to "just try again".

Why am I getting deadlock in MySQL

I believe I have found the issue.

From http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html:

Prior to inserting the row, a type of gap lock called an insertion intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.

Here's what that means for my case.

The deletes, because they affected no rows, all got a shared lock (mode IX) on the end-of-table gap. Once the insert was executed, the shared lock was still held by all threads, and the insert intention waited for the release of this shared lock.

The solution is to not do the following in parallel:

1. Delete the rows you want to insert, when the rows aren't there.
2. Insert the rows

So, the InnoDB engine status was just wrong. It failed to show that each of the transactions held the same lock. It failed to show that each lock was lock_mode IX, not X. It failed to show that each thread also had an insert intention lock waiting to be granted. Altogether, it was a pretty spectacular failure of the SHOW ENGINE INNODB STATUS;.

---

Related Topics