Detect and Fix Database Locking Issues without High CPU

When a database experiences high latency and slow query times, but CPU usage remains low, the cause is almost always resource contention, specifically locking. In this guide, we want to explore database locking issues and how to troubleshoot them.

In database locking, active threads are not working, calculating, or processing data; they are waiting. A query requests access to a row or table that is currently locked by another transaction. The database engine puts the requesting query to sleep until the lock is released. While sleeping, the query consumes zero CPU cycles, but the latency continues to increase for the user.

Before assuming a query is locked, ensure your disk isn't overwhelmed. If iowait is high, the issue is hardware, not software. Check this guide on Monitoring I/O Latency to confirm your disk health.

Now proceed to the following steps on PerLod Hosting to detect the database locking issues and best fixes.

The first step is to confirm that locks, not disk I/O or network issues, are the bottleneck. You must identify which queries are waiting and, more importantly, which query is blocking them.

For MySQL, specifically InnoDB, the sys schema provides the clearest view of lock waits. Run the following query to see exactly what is blocked and what is blocking it:

Output explanation of this query:

• waiting_pid: The Process ID of the stuck transaction, which experiences high latency.
• waiting_query: The SQL statement that cannot run.
• blocking_pid: The Process ID of the transaction holding the lock, which is the culprit.
• blocking_query: The query executed by the blocker.
  • Note: If this is NULL, the blocking transaction might be Idle in transaction.

If you cannot use the sys schema, use the standard process list to look for threads in the Lock wait state:

You must look for the State column saying Locked or Waiting for table metadata lock. Also, check the Time column, high value in Time combined with Command: Sleep often represents an idle blocker.

PostgreSQL uses the pg_stat_activity view to show real-time system state. Use the following query to identify blocked processes and join them with the specific process blocking them:

For Postgres 9.6 and higher, you can run:

• pg_blocking_pids(pid): Returns an array of Process IDs blocking this query.
• wait_event_type = 'Lock': Filters only for queries currently sleeping due to locks.

To prevent performance bottlenecks, you must first identify their source. When latency spikes while CPU usage remains low, it indicates that queries are waiting on resources rather than processing data.

Here are the most common causes for these specific database locking issues:

1. Idle in Transaction (The Ghost Lock): This is the most common cause of confusing locking issues:

• Scenario: An app starts a transaction but crashes or errors before sending a COMMIT.
• Result: The database keeps the lock open indefinitely, waiting for a signal that never comes.
• Symptom: 0% CPU and a session state of Idle in transaction, usually with a NULL query.

2. Unindexed Foreign Keys:

• Scenario: You delete a row from a parent table, like Users, which forces the database to check the child table, like Orders, for orphans.
• Result: If Orders.user_id is missing an index, the database locks the entire Orders table to perform a slow full scan.
• Symptom: A simple delete causes a massive pile-up of unrelated queries on the child table.

3. Long-Running Reporting Queries:

• Scenario: A heavy query runs on a live table with a lock, for example, SELECT … FOR UPDATE or a schema change.
• Result: Tiny and routine updates like last login get stuck waiting behind this single big query.
• Symptom: Latency spikes as transactions queue up one-by-one, creating a chain reaction of issues.

4. Explicit Table Locking:

• Scenario: Using commands like LOCK TABLE users WRITE or using mysqldump without the --single-transaction flag.
• Result: These operations take a global lock, freezing all writes. CPU usage drops to near zero because no writes can be processed.

Once you discovered the causes of database locking issues, you can proceed to the following step to resolve the issues.

Fixing database locking issues requires killing the immediate bottleneck to restore service now and applying the best configuration to stop it from happening again.

1. Immediate Fix: Terminate the Blocker

You must remember that you do not kill the waiting queries; they are victims. You must kill the blocking query, which is the one holding the lock.

In MySQL, you can use the blocking_pid found in the detection step:

• Replace 12345 with the ID from the blocking_pid column.
• This forces a rollback of the blocker and immediately releases locks for all waiting queries.

In PostgreSQL, you can use the blocking_pid found in pg_blocking_pids:

• pg_cancel_backend(pid): Tries to stop the query but keeps the connection.
• pg_terminate_backend(pid): Closes the connection and forces a rollback. Use this for stuck locks.

2. Long-term Fix: Set Timeouts Configuration

You can force the database to kick inactive connections automatically to prevent Idle in transaction issues.

For MySQL configuration, edit the my.cnf config file and set a timeout for lock waits; the default is 50s, which is often too high for web apps:

For PostgreSQL configuration, edit postgresql.conf file and terminate any session that stays idle in transaction for more than 5 minutes:

Abort any statement waiting for a lock for more than 2 seconds:

Note: Setting lock_timeout is safer than statement_timeout because it only kills queries that are actually stuck waiting, not queries that are just slow but working.

For high-throughput databases, it is recommended to move to Dedicated Servers, which ensures that 100% of the disk I/O and CPU time is reserved for your transactions and removes external variables from your performance tuning.

3. Application-Side Fixes

• Keep Transactions Short: Never run API calls or file uploads inside an open transaction. Prepare data first, then BEGIN, write, and COMMIT instantly.
• Add Indexes: Index all Foreign Key columns. This prevents the database from locking an entire table just to check one row during updates or deletes.
• Retry Logic: Configure your app to catch Lock wait timeout errors and automatically retry the transaction after a brief pause.

Tip: To permanently reduce locking pressure, you can move read-heavy data out of the database entirely by implementing a caching layer, which can remove thousands of queries per second. To set this up, you can check this guide on Using Redis Data Caching to Speed Up Processing.

When your database feels slow but your server metrics look healthy, the culprit is almost always locking. This silent killer happens not because your server is weak, but because your connections are fighting for the same rows.

By using sys.innodb_lock_waits in MySQL or pg_blocking_pids in Postgres, you can identify the specific query holding the line up. Kill the blocker to restore service immediately, then apply the long-term fixes, including indexing foreign keys and shortening transactions, to ensure your database handles traffic smoothly without bottlenecks.

We hope you enjoy this guide on detecting and fixing Database locking issues. Subscribe to our X and Facebook channels to get the latest updates and articles.