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.

Detect Database Locking Issues

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.

MySQL Locking Detection

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:

SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;

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:

SHOW FULL PROCESSLIST;

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 Locking Detection

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:

SELECT
  blocked_locks.pid         AS blocked_pid,
  blocked_activity.usename  AS blocked_user,
  blocked_activity.query    AS blocked_query,
  blocking_locks.pid        AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocking_activity.query   AS blocking_query
FROM  pg_catalog.pg_locks         blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks         blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

For Postgres 9.6 and higher, you can run:

SELECT pid, usename, pg_blocking_pids(pid) as blocked_by, query as blocked_query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

• 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.

Why Your Database is Slow but CPU is Idle?

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.

How to Fix Database Locking 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:

KILL 12345;

• 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:

SELECT pg_terminate_backend(12345);

• 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:

[mysqld]
innodb_lock_wait_timeout = 5

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

idle_in_transaction_session_timeout = 300000 

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

lock_timeout = 2000

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.

FAQs

Conclusion

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.