Find and Fix MariaDB Lock Waits and Deadlocks Safely

Effective MariaDB deadlock troubleshooting begins by separating three different events. A normal lock wait has a blocker and may resolve when that transaction commits. A lock wait timeout stops a statement after waiting too long. A deadlock is a cycle in which transactions wait on each other, so InnoDB selects a victim immediately rather than waiting for the timeout. Metadata locks form another queue around table definitions and can make a harmless-looking ALTER TABLE stall an entire workload.

The worst incident response is to raise innodb_lock_wait_timeout and hope. That lets requests queue longer, increases pool exhaustion, and preserves the root blocker. Killing the longest-running query can also be wrong: a session in Sleep may hold the transaction blocking everyone, while the visible active queries are victims. Killing a large writer can trigger a lengthy rollback and add more I/O.

This guide builds a safe diagnostic workflow for row locks, metadata locks, and deadlocks. It shows how to capture a wait-for chain, find the root blocker, decide between observation and termination, understand rollback semantics, repair transaction order and indexing, implement bounded retries, test reproductions, and monitor the system. Commands are version-aware and should be exercised with least-privilege diagnostic access.

Distinguish waits, timeouts, and deadlocks

A row-lock wait

Transaction A modifies or locks a record. Transaction B requests an incompatible record, gap, next-key, or table-level InnoDB lock and waits. If A commits or rolls back before B's timeout, B continues. A brief wait can be normal concurrency control.

A lock wait timeout

When an InnoDB record or table lock wait reaches innodb_lock_wait_timeout, MariaDB returns:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

By default, the failed statement is rolled back, not necessarily the entire transaction. If the application catches error 1205 and later commits, earlier statements in that transaction may still commit. innodb_rollback_on_timeout changes this behavior where configured, but application correctness should not depend on an unverified global default.

The safest general application policy is to treat a timeout as transaction failure: issue ROLLBACK, discard the unit of work, and retry the complete idempotent transaction only when policy permits.

A deadlock

A deadlock is a cycle. For example:

Transaction A locks account 10.
Transaction B locks account 20.
A requests account 20 and waits for B.
B requests account 10 and waits for A.

No participant can progress. InnoDB detects the cycle and rolls back a selected victim. The application commonly receives error 1213 with SQLSTATE 40001.

Deadlocks are not governed by innodb_lock_wait_timeout when detection is active. Raising the timeout does not fix them.

A metadata-lock wait

MariaDB protects object definitions with metadata locks (MDL). A transaction that touched a table can retain a shared metadata lock until commit. DDL needs a stronger lock and waits. Once an exclusive request is queued, subsequent statements can line up behind it, producing an outage even though the original transaction looks idle.

Row-lock tables and metadata-lock instrumentation answer different questions. Query both when symptoms are ambiguous.

Recognize production symptoms

Lock contention may appear as:

API P95/P99 latency rising while CPU remains moderate.
Many sessions in Waiting for ... lock states.
Connection-pool exhaustion and request timeouts.
Errors 1205 or 1213 in application logs.
Threads_running and blocked-session count increasing.
An ALTER TABLE, RENAME, or deploy migration that never starts.
Replica lag caused by a blocked apply transaction.
InnoDB row-lock wait counters accelerating.
A “sleeping” connection with a long open transaction.

Capture UTC timestamps, affected endpoint, deployment version, query digest, transaction ID, connection ID, tenant, and retry count. Lock evidence is transient: once a blocker commits or a deadlock victim rolls back, current wait tables may be empty.

Start with a low-impact incident snapshot

Record server identity and lock-related configuration:

SELECT VERSION(), @@version_comment, NOW(6), @@GLOBAL.server_id;

SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'innodb_lock_wait_timeout',
  'innodb_rollback_on_timeout',
  'innodb_deadlock_detect',
  'innodb_print_all_deadlocks',
  'transaction_isolation',
  'performance_schema'
);

Some variables vary by release. Missing rows are compatibility information, not a reason to invent defaults.

Capture process activity:

SHOW FULL PROCESSLIST;

Avoid SHOW PROCESSLIST without FULL when SQL text truncation could hide the predicate. Process-list text can contain secrets and personal data; protect the output.

Record InnoDB's latest diagnostic state:

SHOW ENGINE INNODB STATUSG

The output includes only the latest detected deadlock section, not a complete history. Save it promptly with server and timestamp.

Collect cumulative row-lock counters:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Innodb_row_lock_current_waits',
  'Innodb_row_lock_time',
  'Innodb_row_lock_time_avg',
  'Innodb_row_lock_time_max',
  'Innodb_row_lock_waits',
  'Threads_connected',
  'Threads_running',
  'Uptime'
);

Use rates and interval deltas. A lifetime average since startup does not describe the present incident.

Build the active InnoDB wait-for chain

MariaDB exposes current InnoDB transactions through information_schema.INNODB_TRX, pending relationships through INNODB_LOCK_WAITS, and relevant locks through INNODB_LOCKS.

First inspect schemas because columns can differ across versions:

DESCRIBE information_schema.INNODB_TRX;
DESCRIBE information_schema.INNODB_LOCK_WAITS;
DESCRIBE information_schema.INNODB_LOCKS;

List active transactions, oldest first:

SELECT trx_id,
       trx_state,
       trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_seconds,
       trx_wait_started,
       trx_mysql_thread_id,
       trx_rows_locked,
       trx_rows_modified,
       LEFT(trx_query, 500) AS trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

Join waiters to blockers:

SELECT w.requesting_trx_id,
       rt.trx_mysql_thread_id AS waiting_thread_id,
       rt.trx_started AS waiting_trx_started,
       rt.trx_wait_started,
       LEFT(rt.trx_query, 300) AS waiting_query,
       w.blocking_trx_id,
       bt.trx_mysql_thread_id AS blocking_thread_id,
       bt.trx_started AS blocking_trx_started,
       LEFT(bt.trx_query, 300) AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS AS w
JOIN information_schema.INNODB_TRX AS rt
  ON rt.trx_id = w.requesting_trx_id
JOIN information_schema.INNODB_TRX AS bt
  ON bt.trx_id = w.blocking_trx_id
ORDER BY rt.trx_wait_started;

Column names should be verified on the installed release. The blocking transaction's current trx_query may be NULL because its connection is idle after an earlier statement. Join the thread ID to process-list data to identify user, host, database, command, and current state:

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO, 500) AS info
FROM information_schema.PROCESSLIST
WHERE ID IN (123, 456);

Replace IDs with verified values. Never act on a stale ID from a prior snapshot; connection IDs can eventually be reused.

Use the sys schema on MariaDB 10.6+

Where installed, the sys.innodb_lock_waits view provides a human-oriented summary, and sys.x$innodb_lock_waits provides raw values for tooling:

SELECT *
FROM sys.innodb_lock_waits
ORDER BY wait_age_secs DESC;

Feature-detect the view:

SHOW FULL TABLES FROM sys LIKE '%innodb_lock_waits%';

Do not make incident automation depend only on sys; some installations omit or customize it.

Find the root blocker, not the loudest waiter

A blocker can itself be waiting on another transaction. Build a directed graph:

waiting transaction -> blocking transaction

The root blocker has incoming waiters but is not waiting on another current row lock. Ending a downstream waiter may reduce one timeout but leaves the queue.

Evaluate each blocker:

Is it running, sleeping, committing, or rolling back?
How old is the transaction?
How many rows did it lock and modify?
Which application, host, user, and deployment owns it?
Is it executing external calls between SQL statements?
Does it hold a business-critical transaction that must finish?
How many sessions and services wait behind it?
What is the rollback cost if terminated?

An old transaction is not automatically guilty. Long consistent reads can be read-only and not block ordinary row writes, though they may affect purge. Focus on the actual wait edges and lock modes.

Inspect the locked object and index

Join the wait relationship to lock details on a tested release:

SELECT w.requesting_trx_id,
       rl.lock_mode AS requested_mode,
       rl.lock_type AS requested_type,
       rl.lock_table AS requested_table,
       rl.lock_index AS requested_index,
       rl.lock_data AS requested_data,
       w.blocking_trx_id,
       bl.lock_mode AS blocking_mode,
       bl.lock_type AS blocking_type,
       bl.lock_table AS blocking_table,
       bl.lock_index AS blocking_index,
       bl.lock_data AS blocking_data
FROM information_schema.INNODB_LOCK_WAITS AS w
JOIN information_schema.INNODB_LOCKS AS rl
  ON rl.lock_id = w.requested_lock_id
JOIN information_schema.INNODB_LOCKS AS bl
  ON bl.lock_id = w.blocking_lock_id;

Lock data can be NULL, partial, or encoded in ways unsuitable for business interpretation. Do not paste it into a DELETE or assume it identifies one safe row.

The index name is diagnostically valuable. If an UPDATE searches without a selective index, InnoDB can examine and lock more records or ranges than the application intended. Capture the statement's plan using plain EXPLAIN or a safe equivalent SELECT.

Diagnose metadata-lock queues

MariaDB 10.5.2+ exposes performance_schema.metadata_locks when Performance Schema and the metadata instrument are enabled.

Check support and instrumentation:

SELECT @@performance_schema;

SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_instruments
WHERE NAME LIKE 'wait/lock/metadata%';

Enable the instrument at runtime only after considering diagnostic overhead and change policy:

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/lock/metadata%';

Inspect pending and granted locks:

SELECT OBJECT_TYPE,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       LOCK_TYPE,
       LOCK_DURATION,
       LOCK_STATUS,
       OWNER_THREAD_ID,
       OWNER_EVENT_ID
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS IN ('PENDING','GRANTED')
ORDER BY OBJECT_SCHEMA, OBJECT_NAME, LOCK_STATUS;

Map Performance Schema thread IDs to process-list IDs:

SELECT THREAD_ID,
       PROCESSLIST_ID,
       PROCESSLIST_USER,
       PROCESSLIST_HOST,
       PROCESSLIST_DB,
       PROCESSLIST_COMMAND,
       PROCESSLIST_TIME,
       PROCESSLIST_STATE,
       LEFT(PROCESSLIST_INFO, 500) AS processlist_info
FROM performance_schema.threads
WHERE PROCESSLIST_ID IS NOT NULL;

Join using metadata_locks.OWNER_THREAD_ID = threads.THREAD_ID. The first pending exclusive table lock often belongs to DDL, but the transaction holding an older granted shared lock is the root blocker.

Permanent instrumentation uses a startup option on supported releases:

[mariadb]
performance_schema = ON
performance-schema-instrument = 'wait/lock/metadata/sql/mdl=ON'

Performance Schema cannot be activated at runtime if it was disabled at startup. Test sizing and overhead before changing a production baseline.

Understand why idle sessions block DDL

Consider:

START TRANSACTION;
SELECT * FROM sales.orders WHERE id = 42;
-- Application performs other work and does not COMMIT.

The transaction can retain a metadata lock on sales.orders. Later:

ALTER TABLE sales.orders ADD INDEX ix_orders_status (status);

waits for an exclusive metadata lock. New queries may then queue behind the waiting DDL depending on lock scheduling. The visible outage appears to be caused by ALTER, while the root cause is the uncommitted application transaction.

Prevent this pattern by:

Keeping autocommit enabled for independent reads.
Starting transactions immediately before required SQL.
Never doing HTTP calls, user input, or long computation inside a transaction.
Committing or rolling back in finally/defer cleanup.
Setting pool transaction state explicitly on checkout and return.
Applying a bounded metadata lock timeout for migrations.

Do not leave a migration waiting indefinitely in production. A queued DDL can amplify a single old session into broad impact.

Capture deadlock evidence

Immediately after a deadlock:

SHOW ENGINE INNODB STATUSG

Find the LATEST DETECTED DEADLOCK section. It describes transactions, statements, locks held and requested, and which transaction was rolled back. This is only the latest deadlock and can be overwritten by the next one.

For a bounded investigation, enable all deadlock logging:

SET GLOBAL innodb_print_all_deadlocks = ON;

MariaDB writes each detected InnoDB deadlock to the error log. Verify destination and monitor volume:

journalctl -u mariadb --since '-15 minutes' --no-pager

or inspect the configured error-log file. Deadlock records contain SQL and identifiers that may be sensitive.

Restore the previous value after the capture:

SET GLOBAL innodb_print_all_deadlocks = OFF;

Do not assume OFF was the original value; record and restore it exactly. Persisting all-deadlock logging can be reasonable where volume and retention are controlled, but that is an explicit operations decision.

Read a deadlock report systematically

For each transaction, extract:

Transaction ID and age.
MariaDB thread/connection ID.
Current statement.
Tables and index names.
Lock modes held and requested.
Number of locked and modified rows.
Whether a gap or next-key component is involved.
Which transaction InnoDB rolled back.

Then draw the cycle:

Transaction A holds resource X, requests Y
Transaction B holds resource Y, requests X

The victim is not necessarily the “bad” transaction. InnoDB considers transaction weight and other factors to minimize rollback cost. Fix the cycle shared by participants rather than blaming the victim query alone.

Statements printed are typically the current statement, not every earlier statement that acquired locks. Application traces or transaction-level logging are needed to reconstruct full lock acquisition order.

Reproduce a simple deadlock safely

Use a disposable database, never a shared production table.

Setup:

CREATE DATABASE lock_lab;
USE lock_lab;

CREATE TABLE accounts (
  id BIGINT UNSIGNED NOT NULL,
  balance DECIMAL(18,2) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO accounts VALUES (10, 1000.00), (20, 1000.00);

Session A:

START TRANSACTION;
UPDATE lock_lab.accounts
SET balance = balance - 10.00
WHERE id = 10;

Session B:

START TRANSACTION;
UPDATE lock_lab.accounts
SET balance = balance - 20.00
WHERE id = 20;

Session A then requests row 20:

UPDATE lock_lab.accounts
SET balance = balance + 10.00
WHERE id = 20;

Session B completes the cycle by requesting row 10:

UPDATE lock_lab.accounts
SET balance = balance + 20.00
WHERE id = 10;

One transaction should become the deadlock victim. Roll back or commit the surviving session explicitly, inspect the report, then remove the disposable schema:

ROLLBACK;
DROP DATABASE lock_lab;

DROP DATABASE destroys data. Run it only against the dedicated lab schema after confirming the current connection and environment.

Resolve an active blocking incident safely

1. Stop amplification

Pause migrations, batch writers, report jobs, or retry storms that add waiters. Apply admission control at the application or job runner where possible.

2. Preserve evidence

Capture wait edges, transactions, process list, metadata locks, InnoDB status, application traces, and relevant logs with UTC timestamps.

3. Identify the root blocker and owner

Confirm current connection ID, transaction age, rows modified, business operation, and rollback cost. Contact the owning service team when time permits.

4. Choose the least destructive action

If the blocker is a read-only query and the connection can cleanly end its transaction, application cancellation may be enough. If an idle connection holds an open transaction, killing only a nonexistent current query does not release locks; the connection must commit, roll back, or close.

To stop only the current statement:

KILL QUERY 12345;

To close the connection and roll back its transaction:

KILL CONNECTION 12345;

Use the verified current ID. KILL CONNECTION can cause a large rollback and significant I/O. Monitor INNODB_TRX for ROLLING BACK state and do not repeatedly restart MariaDB to make rollback disappear.

5. Verify recovery

Check that wait edges shrink, request latency returns, pool queues drain, errors fall, and replication/cluster health remains stable. A killed blocker is mitigation, not root-cause resolution.

Decide when not to kill

Do not terminate reflexively when:

The blocker is seconds from a safe commit.
Rollback cost is greater than waiting.
It is an essential financial or migration transaction without a recovery plan.
It has modified non-transactional tables or invoked external side effects.
Another upstream blocker will keep the queue.
The connection ID or ownership is uncertain.

Instead, stop new work, extend the incident window only with explicit approval, and let the transaction complete while monitoring. The correct decision depends on service impact versus recovery risk.

Fix transaction ordering

The most durable deadlock fix is consistent lock acquisition order. The lab transfer should lock smaller account ID first regardless of transfer direction.

Application logic can sort identifiers, then lock them:

START TRANSACTION;

SELECT id, balance
FROM ledger.accounts
WHERE id IN (10, 20)
ORDER BY id
FOR UPDATE;

UPDATE ledger.accounts SET balance = balance - 10.00 WHERE id = 10;
UPDATE ledger.accounts SET balance = balance + 10.00 WHERE id = 20;

COMMIT;

The application must verify both rows exist and preserve business invariants. Sorting IDs only helps if every code path follows the same order, including background jobs and administrative routines.

For multi-table workflows, define an organization-wide order, such as customer before order before payment. Document it beside transaction APIs and enforce it in tests.

Shorten transaction lifetime

Do not hold locks across:

HTTP or RPC calls.
Message publication without an outbox design.
User interaction.
File upload or report generation.
Sleep/backoff.
Large CPU transformations.
Connection-pool return.

Prepare external data first, open the transaction, perform only database work, commit, then handle post-commit actions through a durable workflow.

Chunk large maintenance updates when business atomicity permits:

UPDATE inventory.items
SET archived = 1
WHERE id > 1000000
  AND id <= 1010000
  AND archived = 0;

Commit each deterministic range and store progress. Chunking reduces lock duration and rollback cost but changes all-or-nothing semantics. It is not suitable when the entire data set must change atomically.

Add the right index to reduce lock footprint

An unindexed update can scan and lock a much broader range:

UPDATE sales.orders
SET status = 'expired'
WHERE tenant_id = 77
  AND status = 'pending'
  AND expires_at < '2026-08-25 00:00:00';

A candidate index is:

CREATE INDEX ix_orders_tenant_status_expires
ON sales.orders (tenant_id, status, expires_at);

Before DDL, use plain EXPLAIN on a semantically equivalent SELECT:

EXPLAIN FORMAT=JSON
SELECT id
FROM sales.orders
WHERE tenant_id = 77
  AND status = 'pending'
  AND expires_at < '2026-08-25 00:00:00';

The index can reduce examined and locked records, but it adds write amplification and DDL risk. Test actual lock behavior, skew, write latency, disk, metadata locks, and replicas.

Indexes do not eliminate logical conflicts when two transactions intentionally modify the same row. They reduce accidental breadth.

Understand gap and next-key locks

Under InnoDB's common REPEATABLE READ isolation, range searches used for locking or modification can lock index records and gaps to protect against phantom changes. Two transactions touching different nonexistent keys can still conflict through overlapping gaps.

Examples include:

SELECT *
FROM booking.reservations
WHERE resource_id = 9
  AND starts_at >= '2026-08-25 10:00:00'
  AND starts_at <  '2026-08-25 11:00:00'
FOR UPDATE;

The exact lock footprint depends on index, uniqueness, predicate, isolation, and MariaDB version. A suitable composite index narrows the searched range. Unique equality lookup to an existing row often needs less gap protection than a broad non-unique range.

Changing isolation to READ COMMITTED can reduce some gap locking but changes repeatable-read and phantom semantics across the application. Treat it as a correctness change requiring transaction tests, not a quick deadlock toggle.

Use NOWAIT and SKIP LOCKED for deliberate workflows

MariaDB supports locking-read modifiers on appropriate versions and statements:

SELECT id
FROM jobs.queue
WHERE status = 'ready'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

This can help multiple workers claim independent jobs without waiting. It may produce unfairness or starvation and does not suit operations that must process every row immediately.

NOWAIT fails immediately instead of waiting:

SELECT id, balance
FROM ledger.accounts
WHERE id = 10
FOR UPDATE NOWAIT;

Version, engine, and syntax support must be verified. Applications must handle the error explicitly. These clauses control waiting behavior; they do not correct inconsistent lock order.

Set lock wait timeout from the service contract

Inspect global and session values:

SELECT @@GLOBAL.innodb_lock_wait_timeout,
       @@SESSION.innodb_lock_wait_timeout,
       @@GLOBAL.innodb_rollback_on_timeout;

For an interactive request, a long database wait can exceed the upstream HTTP timeout, leaving database work continuing after the client gives up. A shorter session value may fail fast:

SET SESSION innodb_lock_wait_timeout = 5;

Five seconds is an example. Coordinate database wait, statement timeout, pool timeout, request deadline, and retry budget.

For batch work, a longer wait may be acceptable if it reduces abort/retry churn. Do not globally raise the value to hide blockers.

MariaDB documents that timeout rolls back the statement by default; the whole transaction can be rolled back when innodb_rollback_on_timeout is enabled. Test application behavior against actual configuration and explicitly call ROLLBACK on error.

Implement safe deadlock and timeout retries

Deadlocks are expected under valid concurrency and applications should be able to retry appropriate transactions. A safe retry policy requires:

Retry the entire transaction, not only the failed statement.
Roll back and discard the failed connection state first.
Use bounded attempts.
Apply exponential backoff with jitter.
Respect the request's overall deadline.
Ensure operations are idempotent or deduplicated.
Record error code, SQLSTATE, attempt, digest, and transaction ID.
Do not retry syntax, constraint, or authorization errors.

Pseudocode:

for attempt in 1..max_attempts:
    begin transaction
    try:
        perform complete unit of work
        commit
        return success
    catch error 1213 or approved error 1205:
        rollback
        if deadline_exceeded or attempt == max_attempts:
            raise
        sleep(exponential_backoff_with_jitter)

Retries can amplify contention. If every waiter retries immediately, the database sees a synchronized storm. Use backoff, admission control, and metrics.

External side effects need an idempotency key, transactional outbox, or equivalent design. A payment call made before a deadlock cannot simply be repeated with the SQL transaction.

Do not disable deadlock detection casually

Deadlock detection consumes CPU when very many sessions contend on the same records. MariaDB exposes innodb_deadlock_detect on supported releases, and specialized high-contention systems may evaluate disabling it.

With detection disabled, cycles are not resolved immediately; transactions wait until innodb_lock_wait_timeout. This can increase latency and occupied connections dramatically. A low timeout, strict retry policy, and load testing become essential.

Do not disable detection as a generic performance recommendation. First fix hot rows, transaction order, concurrency admission, batching, and schema access. Feature behavior is version-sensitive and should be tested on a production-like contention workload.

Troubleshoot recurring patterns

Many waiters, one sleeping blocker

The connection likely holds an open transaction after its last statement. Map thread ID to application, verify transaction age and modified rows, then arrange commit/rollback or close the connection. Fix pool cleanup and transaction boundaries.

Deadlocks began after adding an index

A new plan can change row visitation and lock order. Compare before/after plans, affected transaction paths, and index order. Do not assume more selective always means identical locking.

Deadlocks affect different rows

Gap or next-key ranges may overlap, or transactions may touch multiple indexes and tables. Inspect the deadlock report's index and lock modes, not only application row IDs.

ALTER TABLE blocks normal queries

Find the pending metadata lock, then the older granted lock and owning transaction. Canceling/retrying DDL without clearing the transaction often repeats the queue.

Error 1205 occurs but later changes commit

This is consistent with statement-only rollback. Change the application to rollback the complete transaction after timeout, and review innodb_rollback_on_timeout policy.

Deadlock report shows only one current statement per transaction

Earlier statements may hold the locks forming the cycle. Add transaction-scoped application tracing and log ordered database operations with sensitive values redacted.

Killing the blocker did not restore performance

It may be rolling back, another blocker may exist, or the retry storm may sustain load. Monitor transaction state, wait graph, storage, threads, and application queues.

Lock waits occur only for one tenant

The tenant may be a hot partition with far more rows or concurrent updates. Test skew, indexes, hot counters, serialization strategy, and per-tenant admission control.

Monitor lock health continuously

Dashboards should include:

Current row-lock waits.
Row-lock wait events and time per second.
Error 1205 and 1213 rates by service and query digest.
Oldest active transaction age.
Open transaction count and idle-in-transaction indicators.
Number and age of metadata locks in PENDING state.
Threads_running, connections, and pool wait.
DDL wait duration and deployment state.
Rollback duration and rows modified.
Replica lag and cluster flow control where applicable.

Alert on sustained waiting and service impact, not every deadlock. A small number of safely retried deadlocks can be normal; a rising rate or exhausted retry budget requires action.

Keep metric cardinality bounded. Do not put raw SQL, customer IDs, or connection IDs into unbounded labels. Use normalized digests and secure drill-down artifacts.

Best practices checklist

Distinguish row locks, metadata locks, timeouts, and deadlocks.
Capture evidence before the transient wait disappears.
Build the full waiter-to-blocker graph.
Find the root blocker, including sleeping open transactions.
Verify the current connection ID before any KILL.
Estimate rollback cost before closing a writer.
Keep transactions short and database-only.
Acquire rows and tables in a consistent order.
Add indexes to narrow accidental lock ranges.
Treat isolation-level changes as correctness changes.
Roll back the complete transaction after error 1205 or 1213.
Retry only bounded, idempotent units with jitter.
Use NOWAIT/SKIP LOCKED only for suitable workflows.
Bound migration metadata-lock waits.
Monitor oldest transaction, wait rates, MDL queues, and retry exhaustion.

FAQ

What is the difference between a lock wait and a deadlock?

A lock wait may resolve when its blocker commits. A deadlock is a cycle that cannot resolve naturally, so InnoDB rolls back one participant when detection is enabled.

Does innodb_lock_wait_timeout control deadlocks?

No. InnoDB normally detects deadlock cycles immediately. The timeout controls ordinary record or table lock waits and becomes more important if deadlock detection is disabled.

Does error 1205 roll back the whole transaction?

By default MariaDB rolls back the timed-out statement, not necessarily the whole transaction. Applications should explicitly roll back and retry the complete unit of work.

Should applications retry error 1213?

Usually yes for idempotent transactions, with full rollback, bounded attempts, exponential backoff, jitter, and an overall deadline. External side effects require deduplication.

Why can a sleeping MariaDB connection block queries?

Its current command may be idle while an uncommitted transaction still holds row and metadata locks acquired by earlier statements.

Is it safe to KILL the blocking connection?

Only after verifying ownership, business impact, and rollback cost. Closing a large writer can trigger a long resource-intensive rollback.

How do I see metadata locks in MariaDB?

On MariaDB 10.5.2+, enable the Performance Schema metadata instrument and query performance_schema.metadata_locks, joining owner thread IDs to performance_schema.threads.

Can a missing index cause deadlocks?

It can broaden scans and locked ranges, increasing collision probability. It is rarely the only cause; inconsistent transaction order and long transactions also matter.

Conclusion

Successful MariaDB deadlock troubleshooting reconstructs the wait graph and transaction history instead of reacting to the loudest blocked query. Capture InnoDB transactions, locks, wait edges, metadata locks, process ownership, and the deadlock report. Then identify the root blocker and choose mitigation with a clear understanding of rollback cost.

Permanent fixes are usually application and schema work: consistent lock order, short transactions, selective indexes, bounded concurrency, explicit rollback, and idempotent retries with jitter. Timeouts and query cancellation are guardrails, not cures. When transaction semantics, diagnostics, and operational response agree, lock conflicts remain recoverable concurrency events rather than cascading database outages.