MySQL Forums
Forum List  »  InnoDB

Transaction Deadlock Fixed with Composite Index - but why?
Posted by: Rick D
Date: April 17, 2019 08:47AM

I encountered a transaction deadlock issue that occurred when two transactions were both trying to do a large set of UPDATEs. Each individual UPDATE in each transaction was only for a unique row so there was no danger of the transactions updating the same row. The update query’s WHERE clause had 3 values. (UPDATE table SET ValueToChange = n WHERE v1 = x, v2 = y, v3 = z) and each value was individually indexed. The entire set of UPDATE queries took about 20 seconds, and were delivered in batches of about 500 out of 10000 or so total queries.

I eventually tried making the entire WHERE clause have its own composite index (before, each value had its own individual index) and this solved the deadlock issue.

The question is why? Was it because the UPDATE statement was locking all rows found for each individual value in the WHERE clause? (all v1 rows were locked, all v2 rows were locked, etc.) For any single given value in the where clause, IF all 3 are not specified, the query WOULD overlap the other transaction, but as long as all 3 values are followed, the transactions should not touch the same row.

Was not having a composite index for all 3 values (v1, v2, v3) at fault here? How are rows locked during a transaction with multiple values in a WHERE clause?

Using innodb, mysql 8.0. I am trying to understand why the deadlock occurred so I can avoid it in the future.

Options: ReplyQuote


Subject
Views
Written By
Posted
Transaction Deadlock Fixed with Composite Index - but why?
305
April 17, 2019 08:47AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.