Fix MySQL Deadlocks in Go with One Sort Function    

At adjoe, the largest rewarded mobile ad network, we process massive volumes of data, user clicks, views, scrolls and installs. This data is critical for our adtech publishers to fine-tune their campaigns.

Any reporting discrepancy can lead to misjudgments about user behavior and campaign performance. Today, we’re sharing our approach to fixing deadlocks. 

This tech blog explores the common causes of MySQL deadlocks and show how we eliminate them by aligning application logic with database locking behavior.

What are MySQL Deadlocks?

A MySQL deadlock is a situation where two or more database transactions block each other from accessing the database resources they need to complete. In simpler terms, Transaction A is waiting for Transaction B to release a row lock or table lock, while Transaction B is waiting for Transaction A, and neither can proceed.

Deadlocks usually happen in high-concurrency environments where multiple queries try to update or modify the same rows or tables at the same time. They can cause transaction failures, query errors, performance issues, and database contention if not handled properly.

Problem: MySQL Deadlocks Causing Data Gaps

It all started with a simple bug ticket: a publisher was missing data in one of our reporting APIs. We operate a high-throughput system consuming events from the queue. The consumer’s role is essentially to receive event data, aggregate it, and perform bulk inserts into our MySQL cluster. 

This data eventually powers the reporting dashboards for our publishers. After a quick look at our Kibana logs, we observed that the MySQL cluster was being clogged by deadlocks. 

We started looking at terms like “Deadlock”, “Error 1213”, and saw many logs containing them. It didn’t take long to create a Kibana dashboard that showed the real picture of the problem:

Luckily, we had a retry mechanism in place; events in the queue would eventually be picked up by other consumers. However, the contention was so high that some data was being sent to the Dead Letter Queue (DLQ), requiring manual resolution.

That was the smoking gun: the deadlocks on the consumer side were directly causing data gaps in the reporting service.

Investigation: How to Detect Deadlocks? 

We started by breaking down why deadlocks occur in the first place. Generally, there are two main reasons for deadlock detection:

1. Inconsistent Locking Order (Classic Cause)

If different parts of your application access the same resources in different orders, a deadlock is inevitable under high load.

Scenario:

• Transaction A updates Table_X, then tries to update Table_Y.
• Transaction B updates Table_Y, then tries to update Table_X.
• Result: Transaction A waits for Table_Y to be released, while Transaction B waits for Table_X. They are stuck forever.

2. Gap Lock Deadlocks

This was the more subtle issue we suspected. In MySQL, when you try to insert a new record into a unique index, the database must ensure no one else inserts that same value simultaneously. To do this, it places a Gap Lock on the empty space before the next existing record.

The deadlock trigger here is deceptive because Gap Locks are compatible.

• Transaction A can acquire a Gap Lock on “IDs 10 to 20”.
• Transaction B can also acquire a Gap Lock on “IDs 10 to 20” at the same time.

The database allows this overlap because a Gap Lock is effectively a “Shared Read” lock. It’s a reservation saying, “I am looking at this space.”

The Crash: The problem explodes the moment both transactions try to write (Insert) into that gap.

1. Transaction A tries to Insert. It needs an Exclusive Lock. It must wait for Transaction B to release its shared Gap Lock.

2. Transaction B tries to Insert. It needs an Exclusive Lock. It must wait for Transaction A to release its shared Gap Lock.

3. Other Culprits (Briefly Noted) 

There are, of course, other reasons for deadlock detection. Lock escalation due to low memory, insufficient connection pools, or even “Next-Key Lock” collisions.

However, in our case, the root cause was more fundamental. It wasn’t about resources; it was about direction.

The “Aha!” Moment: Root Cause for Deadlocks 

The breakthrough came when we compared how events were sorted on the producing side against our database schema.

Our consumer writes data into a table with a Unique Index (ensuring we have one record per ID + Country + Date).

Physically, this index is stored as a B-Tree. When inserting new rows or checking for duplicates, the database traverses this tree in a specific, fixed direction.

We realized that our producer was publishing events in an order that went “against the grain” of the unique index. By sending unsorted (or reverse-sorted) data, we were asking the database to grab locks at random positions throughout the index tree, jumping back and forth.

Why ‘Against the Grain’ Creates Deadlock Issues

When you insert data in an order that conflicts with the Unique Index, especially in a high-throughput system, you drastically increase the surface area for collisions.

Imagine the unique index as a narrow, one-way bridge:

• With the Grain (Sorted)

If Transaction A and Transaction B both arrive sorted, they cross the bridge moving in the same direction. If B is faster, it simply waits behind A. No crash.

• Against the Grain (Unsorted/Reverse)

If Transaction A tries to lock the bridge from left-to-right, and Transaction B tries to lock it from right to left (or jumps onto the bridge randomly), they will inevitably meet in the middle.

// Neither can move forward because the other is blocking the path.

Fix Deadlocks with Sorted Events

Once we identified the friction between our logical input order and the physical database storage, the fix was simple. We implemented a sort function on the consumer side to align our events with the database index before inserting them into the table.

We forced the application to sort events strictly by the columns in our Unique Index (ID, Country, Date). 

It aligned our logical locking order with the database’s physical storage. Everyone started crossing the bridge in the same direction, and the deadlocks vanished.

Results

What’s better than deadlock monitoring? Deadlock monitoring that is completely flat.

Understanding the underlying mechanics of InnoDB locking and aligning our application logic to match helped eliminate the instability without upgrading hardware or changing the database engine.

End Note

MySQL deadlocks in high-traffic systems are often invisible until they cause real data gaps, but the solution doesn’t have to be complex. 

At adjoe, simply sorting events to match the Unique Index transformed the reporting pipeline into a stable, deadlock-free system. 

This highlights a key lesson for engineering teams: understanding how your application logic interacts with database storage can prevent subtle issues more effectively than retries, scaling, or hardware upgrades.

For more in-depth adtech insights, check out the adjoe engineers blog. 

FAQs

How do I detect deadlocks in MySQL?

You can detect MySQL deadlocks by checking the InnoDB status using SHOW ENGINE INNODB STATUS;

MySQL automatically detects deadlocks and rolls back one of the conflicting transactions. Monitoring logs and enabling the performance schema can also help identify recurring deadlocks.

How do gap locks contribute to deadlocks?

Gap locks temporarily reserve spaces between rows for inserts. If two transactions try to insert in the same gap simultaneously, they can block each other, causing a deadlock.

Build products that move markets