Skip to content
Advertisement

MariaDb InnoDB deadlock while doing many inserts

I’m having deadlock issues with a MariaDB (10.0.27)if it’s under pressure. The database schema is basically a hierarchy and the leaf node causes this:

This is how my leaf table looks like:

I’m able to reproduce the deadlock with an input set that puts the system under pressure. With this, I’ve been playing around and once I remove the unique index UNI_BARCODE there are no deadlocks. In addition, my data does not intersect somehow, as we are using consumer groups from ActiveMQ to enforce in-order update processing for same products. So apparently, the unique index is the only overlapping thing that causes issues.

I’ve spent some time on this trying to fix this but couldn’t do it 🙁 I would highly appreciate any ideas on this 🙂 Thanks in advance!

Advertisement

Answer

Yes, I’ve noticed this a lot at my place of work.

An InnoDB table with both a PRIMARY KEY and a secondary UNIQUE KEY has a large chance of causing a deadlock. Example of such a case is described clearly in this bug report: https://bugs.mysql.com/bug.php?id=86812

There seems to be a race condition in InnoDB row-level locking. I guess the locks on the unique index and the locks on the clustered index (primary key) are not acquired atomically.

This affects both MySQL and MariaDB, since they both use InnoDB. It affects many versions of InnoDB, so upgrading will not help. It affects both READ-COMMITTED and REPEATABLE-READ transaction isolation levels, so it won’t help to change that.

What I advised the developers at my place of work is that you have three alternatives:

  1. Retry the transaction that was rolled back as a result of the deadlock. This is the usual recommendation for other types of deadlocks.

  2. Pessimistic locking. Use LOCK TABLES BARCODE WRITE; before attempting the insert. And of course UNLOCK TABLES; immediately afterwards, to allow concurrent sessions to get their turn.

  3. Redesign the table so it doesn’t have both a primary key and unique key. For example, drop the id column so the unique key on (BARCODE_REC_ID, TYPE ) becomes the clustered index.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement