Log in
Free Trial

MySQL Deadlock Detection

Understand, detect, and resolve deadlocks in your database with Releem’s automated monitoring and alerting
SEP 16, 2025 • WRITTEN BY ROMAN AGABEKOV

What is a deadlock?

A deadlock is a situation where two or more transactions are each waiting for the other to release a lock. Because neither transaction can proceed, they become stuck. MySQL has a built-in mechanism to detect deadlocks when the innodb_deadlock_detect setting is enabled, which is the default in most setups. When MySQL detects a deadlock, it automatically rolls back one of the transactions to break the loop.

Deadlocks are not always a sign of a bug. They can occur in any system with high concurrency or complex workflows involving multiple statements. However, when deadlocks happen repeatedly or appear in the same workflow, they often signal a deeper issue in how transactions are structured, how indexes are applied, or how application logic interacts with the database.

How to monitor deadlocks manually

If you want to manually check for a deadlock on your MySQL server, run the following command:
SHOW ENGINE INNODB STATUS\G
Look for the section labeled LATEST DETECTED DEADLOCK. Examine the output for:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-09-19 21:42:10 0x7f2cd67fa700
*** (1) TRANSACTION:
TRANSACTION 24692, ACTIVE 3 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 127, OS thread handle 139832472479488, query id 44118 localhost appuser updating
UPDATE order_items 
  SET qty = qty + 1 
  WHERE order_id = 500 AND id = 1001
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 121 page no 145 n bits 72 index `PRIMARY` of table `shop`.`orders` trx id 24692 
lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; id=500
 1: ... (hidden/internal fields omitted)

*** (2) TRANSACTION:
TRANSACTION 24691, ACTIVE 4 sec updating or deleting
4 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 123, OS thread handle 139832488126208, query id 44116 localhost appuser updating
UPDATE orders 
  SET status = 'processed' 
  WHERE id = 500
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 121 page no 145 n bits 72 index `PRIMARY` of table `shop`.`orders` trx id 24691 
lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; id=500
 1: ... (hidden/internal fields omitted)
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 122 page no 88 n bits 80 index `fk_order_items_order_id` of table `shop`.`order_items` trx id 24691 
lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; order_id=500
 1: ... (hidden/internal fields omitted)

*** WE ROLL BACK TRANSACTION (1)

  • Transaction IDs and SQL queries
  • Lock types and what each transaction was waiting for
  • Table names and indexes involved
This manual approach works, but it’s reactive, time-consuming, and only shows the latest deadlock. You also won’t get historical data unless you’re already logging it elsewhere.

Deadlock types and how to fix them

Deadlocks can be grouped by the type of operation or lock pattern that caused them. Different deadlocks require different fixes. Recognizing the type helps you identify the underlying cause and choose the right fix. Here are the most common categories:

1. Update-Update deadlock

Occurs when two transactions update the same rows but in a different order. For example:

  • Transaction A updates orders, then order_items.
  • Transaction B updates order_items, then orders.
Since both are waiting on each other’s locks, the transactions are stuck.

How to fix:
  1. Access and update rows in the same order in every transaction.
  2. Standardize your transaction logic across your application.

2. Select-for-Update deadlock

This happens when one transaction uses SELECT ... FOR UPDATE to lock rows that another transaction is trying to update or delete. This often occurs in job queues or task schedulers that select rows for processing while other workers modify or remove the same data.

How to fix:
  1. Only use FOR UPDATE when necessary.
  2. Consider LOCK IN SHARE MODE if full write-locks aren’t needed.
  3. Separate row selection and modification into different steps when possible.

3. Insert Auto Increment deadlock

InnoDB places a special lock (called the auto-inc lock) on the end of the index when inserting into a table with an AUTO_INCREMENT column. If multiple transactions insert rows and then touch related tables, they can block each other in unpredictable ways.

How to fix:
  1. Move insert operations to the end of your transaction.
  2. Use bulk inserts to reduce contention.
  3. Avoid mixing inserts with updates on related tables in the same transaction.

4. Gap lock deadlock

InnoDB uses gap locks to lock ranges of index values for SELECT ... FOR UPDATE, DELETE, or UPDATE queries that use inequality or range filters. These range locks can block other inserts or updates that try to work within the same range. This is common with pagination, uniqueness checks, or complex filters.

How to fix:
  1. Use equality comparisons instead of ranges if possible.
  2. Reduce the locked range by adding filters or more specific indexes.
  3. Consider READ COMMITTED isolation level to reduce the range lock scope.

5. Insert Intention deadlock

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. 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.

How to fix:
  1. Use INSERT IGNORE, REPLACE, or INSERT ... ON DUPLICATE KEY UPDATE if the inserted row may already exist.
  2. Use SELECT ... FOR UPDATE beforehand to lock the row and avoid conflicts.
  3. Reduce the locked range by adding filters or more specific indexes.
  4. Consider READ COMMITTED isolation level to reduce the range lock scope.

6. Insert same PK deadlock

Plain `INSERT` is not idempotent when multiple workers try to write the same key at once . Two writers collide on the same PRIMARY key and deadlock.

How to fix:
  1. Change the statement to one of these patterns so the second writer doesn’t need to “win” a brand-new insert: INSERT IGNORE, REPLACE, or INSERT ... ON DUPLICATE KEY UPDATE if the inserted row may already exist. 
  2. Reduce the locked range by adding filters or more specific indexes.
  3. Consider READ COMMITTED isolation level to reduce the range lock scope.

7. Foreign key deadlock

This affects operations where one transaction deletes or updates a parent row, and another interacts with the child table. If referential integrity checks are triggered mid-transaction, InnoDB may require shared or exclusive locks on multiple tables, resulting in a deadlock.

How to fix:
  1. Always delete or update related rows in a consistent parent-first or child-first order.
  2. Use ON DELETE CASCADE or ON UPDATE CASCADE appropriately.
  3. Minimize transactional overlap on dependent tables.

8. Long transaction deadlock

A transaction that touches many rows or tables and holds locks for a long time can easily deadlock with smaller, faster transactions. This isn’t necessarily a logic bug, but sometimes it simply happens due to bad timing under high load.

How to fix:
  1. Break large transactions into smaller chunks.
  2. Commit frequently when possible.
  3. Avoid idle sessions within a transaction.

9. Lock escalation deadlock

Though relatively rare in MySQL, some workloads cause escalation-like behavior when multiple row-level locks start conflicting across transactions in unexpected patterns. These usually surface in high-concurrency systems with heavy writes and insufficient indexing.

How to fix:
  1. Add indexes to reduce full scans and write lock contention.
  2. Analyze patterns in locking and restructure queries to reduce overlap.
  3. Consider queuing or scheduling batch operations to run sequentially.

How Releem helps with deadlock monitoring

Releem's Deadlock Monitoring Service continuously checks for deadlocks by analyzing MySQL's internal reports. It captures complete information about every detected deadlock and makes that information accessible and actionable.

Here’s what Releem’s deadlock monitoring does:

  • Automatically detects all deadlocks in real time using SHOW ENGINE INNODB STATUS
  • Captures all relevant information, including SQL statements, table names, lock types, and indexes
  • Sends notifications through your preferred channels, such as Telegram, Slack, or email
  • Stores the full history of deadlocks, accessible through the Releem web interface and Grafana
  • Provides practical guidance to help you understand the lock type and fix the root cause

What you’ll see in a deadlock alert

When Releem detects a deadlock, it sends an alert that includes everything you need to start investigating. This includes:

  • The exact time the deadlock occurred
  • The full SQL statement for the transaction that was rolled back
  • The SQL statement that blocked it
  • The table names involved
  • The type of locks that were in conflict
For example:
DEADLOCK DETECTED  
Time: 2025-04-16 12:00:41  
DB: example
Table: orders

TRANSACTION 1 (ROLLED BACK)
	      HOLDS THE LOCK
Lock mode: Exclusive/Shared/AUTO-INC
Lock type: TABLE/RECORD -  rec but not gap/insert intention/gap before rec insert intention/”empty“
Index: PRIMARY  (exist if Lock type = RECORD)
WAITING LOCK TO BE GRANTED
Query Text: UPDATE orders SET status = 'paid' WHERE id = 123  
Lock mode: Exclusive/Shared/AUTO-INC
Lock type: TABLE/RECORD -  rec but not gap/insert intention/gap before rec insert intention/”empty“
Index: PRIMARY  (exist if Lock type = RECORD)

TRANSACTION 2
		WAITING LOCK TO BE GRANTED
Query Text:  INSERT INTO orders ...  
Lock mode: Exclusive/Shared/AUTO-INC
Lock type: TABLE/RECORD -  rec but not gap/insert intention/gap before rec insert intention/”empty“
Index: PRIMARY  (exist if Lock type = RECORD)
T1:
- has locks on ...
- waiting for locks on...

T2:
- has locks on ...
- waiting for locks on ...

How to respond to a Releem deadlock notification

Step 1: Review the alert

Start by examining the SQL statements in the alert. Look at the tables involved and the type of lock that caused the block. This gives you an immediate view of what was happening in the database at the time of the deadlock.

Step 2: Find the code

Use the query text and table names to trace the transaction back to the relevant part of your codebase. If the query comes from an ORM, enable SQL logging to get the original code. Most ORMs provide a way to log generated SQL, such as enabling echo=True in SQLAlchemy or enabling query logs in Laravel.

Step 3: Apply Fix

Once you’ve identified the query and type of deadlock, apply the corresponding fix based on the lock pattern involved.

How to prevent deadlocks in the future

Deadlocks aren’t always avoidable, especially in high-concurrency environments. But you can reduce how often they occur and how disruptive they are by structuring your transactions and queries more deliberately:

  • Access tables and rows in a consistent order: Inconsistent ordering is one of the most common causes of deadlocks. Always access tables and rows in the same order across all parts of your application.

  • Use proper indexes on frequently updated or filtered columns: When queries scan too many rows due to missing indexes, they hold locks longer than necessary. This increases the likelihood of conflicting transactions. Add indexes to support any columns used in joins, updates, deletes, and SELECT ... FOR UPDATE queries.

  • Keep transactions short and commit as early as possible: Long-running transactions hold locks open and increase contention. Avoid starting a transaction and then performing multiple slow operations or leaving the session idle. If changes are unrelated, split them into separate transactions.

  • Avoid combining SELECT FOR UPDATE and INSERT statements in the same transaction when possible: Locking existing rows and writing new ones can create complex lock dependencies, especially in tables with AUTO_INCREMENT values or triggers.

  • Consider adjusting your isolation level: The default isolation level in MySQL, REPEATABLE READ, holds more locks than READ COMMITTED. If you don’t need consistent snapshots for the duration of a transaction, switching to READ COMMITTED can help reduce lock contention.

  • As a last resort, serialize critical sections with explicit table locking: If nothing else helps, you can use LOCK TABLES to manually control access to entire tables. This approach guarantees exclusive access, but it comes with tradeoffs. You must start with SET autocommit = 0, lock the tables, perform the operations, then commit and release the locks. It should only be used in tightly scoped, low-volume parts of the application where deadlocks are unavoidable through normal means.

Releem makes deadlocks easier to deal with

Deadlocks are a natural part of working with transactional databases, but that doesn’t mean you have to treat them as a black box. With Releem’s Deadlock Detection, you get automated early warnings, full context, and clear next steps. It reduces the time you spend diagnosing blocking issues and gives you visibility into the health of your transaction patterns.

If you're ready to stop chasing deadlocks after the fact, turn on Deadlock Detection in your Releem dashboard. It takes care of the tracking so you can focus on fixing the real issues.

Article by

  • Roman Agabekov
    Founder & CEO
    Roman Agabekov has 17 years of experience managing and optimizing MySQL and MariaDB in high-load environments. He created Releem to automate routine tasks like performance monitoring, tuning, and query optimization. His articles share practical insights to help others maintain and improve their MySQL databases.
Keeping an Eye on MySQL with Health Checks
MySQL Performance Tuning Guide
How MySQL Configuration Impacts the Performance of Web Applications
How do you keep high-performance MySQL configuration?
Explore More
Ready to dive in?
Try Releem today for FREE!
Start Free Trial