Trace and Fix InnoDB Row Locks and Deadlocks on MySQL

If checkout hangs, admin screens spin, and MySQL connections pile up, you are usually staring at InnoDB row locks or a transaction deadlock—not a “slow site” mystery. This guide is for Australian small-business owners and sysadmins who need to SSH into a root server, log into MySQL, read SHOW ENGINE INNODB STATUS\G, isolate the poisoned thread ID, and release locked blocks with a native KILL before revenue keeps dropping.

Fixwebnode provides direct remote server support for this exact failure mode across Australia. We work on your Linux host over SSH—no marketplace bidding—so you can follow the DIY steps below or hand the session to a specialist via our Linux server support page when the lock graph is too tangled to clear safely.

Why InnoDB locks freeze sales streams under pressure

InnoDB protects concurrent writes with row-level locks. Under load—flash sales, bulk imports, stuck cron jobs, or long-running reports—one session can hold a lock while others wait. MySQL may eventually roll back a deadlock victim, but a frozen background thread often never finishes: it blocks the sales stream indefinitely until you identify the thread and terminate it.

Remote diagnosis is the normal path in Australia: SSH to the host, inspect processlists and the InnoDB engine status, kill only the blocker, then verify that checkout and writes resume. Guessing with a full MySQL restart can abort every open transaction and corrupt application state; targeted tracing is safer.

How do I trace and fix InnoDB row locks blocking MySQL sales in Australia?

SSH into the server, open the mysql client as an admin user, run SHOW FULL PROCESSLIST and SHOW ENGINE INNODB STATUS\G, note the blocking thread ID from the TRANSACTIONS / LATEST DETECTED DEADLOCK sections, then issue KILL <thread_id> and re-check that waits clear. If locks return immediately or you lack safe root access, book remote Fixwebnode support rather than restarting mysqld blindly.

Common InnoDB lock and deadlock issues

These problems look similar on the storefront but have different root causes. Treat each as its own incident.

• Long-running transaction holding row locks — One session started a transaction (often from a failed deploy script, php-fpm worker, or admin export) and never committed; every sale touching those rows queues behind it.
• Classic multi-statement deadlock — Two transactions lock rows in opposite order; MySQL rolls one back with error 1213, but retries keep colliding and the sales API flaps.
• Hidden metadata or gap locks from bulk writes — Large UPDATE/DELETE without proper indexes, or uncommitted migrations, lock ranges so new inserts appear “frozen” even when PROCESSLIST looks almost idle.
• Orphaned connection after app crash — The application died; the MySQL thread remains in Sleep or Query with locks until wait_timeout or you kill it manually.

Before you start: SSH and MySQL access

You need root or sudo on the Linux host and a MySQL account with PROCESS and SUPER (or CONNECTION_ADMIN) privileges. Work on a maintenance window if you can; still prefer killing one thread over restarting the whole instance.

Step 1 — SSH into the server

ssh root@YOUR_SERVER_IP
# or: ssh deploy@YOUR_SERVER_IP

Step 2 — Open the MySQL client

sudo mysql -u root
# or: mysql -u root -p

Step 3 — Confirm the server is under lock pressure

SHOW FULL PROCESSLIST;\G
SHOW STATUS LIKE 'Innodb_row_lock%';
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

High Innodb_row_lock_current_waits or many rows in State Locked / Waiting for ... lock means you proceed to engine status—not a blind reboot.

Fix 1: Isolate a frozen background thread blocking the sales stream

Symptom: checkout and stock updates hang; one connection sits for minutes in “Sending data”, “Updating”, or even “Sleep” while others show “Waiting for table/row lock”.

Step 1 — Capture the live process list

SHOW FULL PROCESSLIST;

Note candidate Id values with long Time, suspicious Info (open transaction SQL), and users tied to crons or old deploys.

Step 2 — Dump InnoDB status (the critical view)

SHOW ENGINE INNODB STATUS\G

Scroll to TRANSACTIONS. Look for ACTIVE (x sec), lock struct(s), row lock(s), and lines like MySQL thread id 12345 holding locks while others wait. That thread id is your poisoned session.

Step 3 — Optional: performance_schema lock waits (MySQL 5.7+/8.0)

SELECT * FROM performance_schema.data_locks\G
SELECT * FROM performance_schema.data_lock_waits\G
SELECT r.trx_id waiting_trx, r.trx_mysql_thread_id waiting_thread,
 b.trx_id blocking_trx, b.trx_mysql_thread_id blocking_thread
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

On MySQL 8 the information_schema.innodb_locks views were replaced; prefer performance_schema.data_locks and data_lock_waits if the older query errors.

Step 4 — Forcefully release locked blocks with KILL

KILL 12345;
-- replace 12345 with the blocking thread id from INNODB STATUS

MySQL rolls back that session’s transaction and frees its row locks. Re-run:

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G
SHOW STATUS LIKE 'Innodb_row_lock_current_waits';

Waits should drop toward zero and sales queries should complete. If the same SQL returns under a new thread id within seconds, the application is reopening the bad transaction—fix the job, not only MySQL.

When to call Fixwebnode: you cannot tell blocker from victim, replication is involved, or killing threads risks financial batch jobs. Remote specialists can join the SSH session and map app pools to thread owners.

Fix 2: Read and break a repeating transaction deadlock

Symptom: application logs show Deadlock found when trying to get lock; try restarting transaction (errno 1213). Throughput flaps; some orders succeed after retry, others fail in bursts.

Step 1 — Pull the latest deadlock section

SHOW ENGINE INNODB STATUS\G

Find LATEST DETECTED DEADLOCK. Note both transactions’ SQL, index names, and lock modes (X exclusive, S shared, gap locks). Identify which tables and primary/secondary keys collide.

Step 2 — Confirm frequency

SHOW STATUS LIKE 'Innodb_deadlocks';
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

Optionally enable logging (session or carefully in config) so the error log keeps full graphs:

SET GLOBAL innodb_print_all_deadlocks = ON;

Step 3 — DIY application-side resolution

1. Make both code paths lock rows in the same table order (e.g. always parent row before child rows).
2. Keep transactions short: no HTTP calls or heavy PHP work inside BEGIN ... COMMIT.
3. Retry the deadlocked statement once or twice with backoff at the app layer.
4. Ensure supporting indexes exist so updates hit row locks, not wide gap locks.

Step 4 — Clear any leftover waiter after a bad burst

SHOW FULL PROCESSLIST;
KILL <stuck_waiter_id>;

Deadlock victims are usually rolled back automatically; you only KILL sessions that remain stuck holding locks afterward.

When to call Fixwebnode: deadlocks span multiple microservices or a multi-tenant schema and you need query-order redesign plus index changes under production traffic. Related platform work is covered on our multi-tenant SaaS scalability service page when lock storms are really a tenancy design issue.

Fix 3: Bulk writes, gap locks, and “nothing is running” freezes

Symptom: PROCESSLIST looks quiet, yet inserts into orders or stock fail with lock wait timeouts. Often a migration, report, or mass discount update locked an index range.

Step 1 — Check history list and long transactions

SHOW ENGINE INNODB STATUS\G

In TRANSACTIONS, hunt for old ACTIVE times and large undo. In LOG / BUFFER sections, note if purge is lagging.

Step 2 — Find uncommitted or huge statements

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;

Step 3 — Kill the bulk holder; split the job

KILL <trx_mysql_thread_id>;

Rewrite bulk work into batched statements with tight commits (e.g. 500–1000 rows per transaction), correct secondary indexes, and avoid SELECT ... FOR UPDATE on wide ranges during peak Australian trading hours.

Step 4 — Verify

SHOW STATUS LIKE 'Innodb_row_lock%';
SELECT COUNT(*) FROM information_schema.innodb_trx;

When to call Fixwebnode: purge lag, huge undo tablespaces, or migration locks on production need careful scheduling and monitoring—not repeated KILL loops.

Fix 4: Orphaned Sleep threads after app or PHP-FPM crashes

Symptom: after a deploy or PHP fatal error, MySQL threads remain with locks; WordPress or custom checkout forms time out even though the web tier was restarted.

Step 1 — List sleeping connections with open transactions

SHOW FULL PROCESSLIST;
SELECT * FROM information_schema.innodb_trx\G

Step 2 — Kill orphans

KILL <id>;

Step 3 — Align app pools so crashes do not leave locks

sudo systemctl status php8.2-fpm nginx
sudo tail -n 100 /var/log/nginx/error.log
sudo tail -n 100 /var/log/mysql/error.log

Restart only the app pool if needed after clearing DB locks:

sudo systemctl reload php8.2-fpm

If the storefront issue is also broken mail on contact or order forms, that is a separate path—see fix broken WordPress contact forms and SMTP—but clear InnoDB locks first when checkouts hang.

When to call Fixwebnode: thread storms return after every deploy, pointing at connection pooling or missing commit/rollback in application code.

When DIY is enough vs when to book Fixwebnode

DIY is enough when you have SSH and MySQL admin rights, a single obvious blocker in SHOW ENGINE INNODB STATUS\G, and killing that thread restores sales without immediate recurrence. Document the SQL you killed so developers can fix the transaction boundaries.

Book Fixwebnode when locks recur, you run replicas or Galera/Group Replication, you lack confidence distinguishing victim from blocker, or the deadlock graph crosses many services. We deliver remote server support for Australian businesses—direct specialist help, not a freelance board. Geography and coverage are listed on our service areas page; engagement is over SSH and secure remote sessions nationwide.

Avoid these shortcuts under pressure: do not killall mysqld as a first step; do not set innodb_lock_wait_timeout=1 permanently to “hide” waits; do not delete IBDs or undo files to clear locks.

Closing: get the sales stream moving again

Tracing InnoDB conflicts is a terminal skill: SSH in, read processlist and SHOW ENGINE INNODB STATUS\G, isolate the poisoned thread ID, and release locks with KILL. Use the numbered checks above for long transactions, repeating deadlocks, bulk gap locks, and orphaned Sleep sessions. When the graph is unclear or the blocker keeps returning from application code, talk to Fixwebnode for hands-on remote resolution.

Start a conversation or book remote help on our Ubuntu Linux server support and bug fixing landing page. We will work the lock with you until checkout traffic flows again—calmly, on the live server, without marketplace middlemen.