MySQL trigger causing deadlock resolved with Lock Tables

“The Inserts are done in batches” — Sort each batch by the 4-column PK. This should eliminate many deadlocks and turn the rest into “lock waits”. (That is, when there would have been a deadlock, it can simply wait for the other connection to finish.)

Also, if practical, limit batches to 100 rows.

It is almost always useless to have the PRIMARY KEY start with the Partition key.

(I agree that you should try to avoid LOCK TABLES.)

Explanation

The classic deadlock is:

I grab row number 1, you grab row 2, then I reach for row 2 (but can’t get it) and you reach for row 1 (and can’t get it). Neither of us is willing to let go of what we have.

So a referee steps in and forces one of us to give back when he has, letting the other proceed to completion.

It is impossible (or impractical) for me (or you) to grab all the rows needed; so the rows are actually grabbed one at a time. Think of a giant UPDATE that is changing millions of rows. It is not wise to stop everything while I grab all those rows.

This is called “optimistic” — The processing assumes it will succeed and plows ahead. And 99.999…% of the time a typical transaction will finish before any other connection conflicts with it.

If we grab the rows in the same “order” (such as PRIMARY KEY order), one of us can finish; the other can simply wait. If the wait is only milliseconds, then the delay is imperceptible. (Limiting the batch size helps here.)

Better?

It may be better (that is, faster and less likely to deadlock) to get rid of the trigger and simply do two batch statements — one to the original batch INSERT, the other to batch Upsert (aka IODKU) the summary table.

In any case, catch errors in transactions and replay the entire transaction.

More discussion of high-speed inserting: http://mysql.rjweb.org/doc.php/staging_table (Though not directly applicable, you may find some related tips.)

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top