Unable to complete concurrent transactions on e-commerce database due to lock wait timeout

Question

Unable to complete concurrent transactions on e-commerce database due to lock wait timeout

Isabella Brown 20

Hello,

I am experiencing an issue with processing concurrent checkout transactions in our e-commerce database within our Windows enterprise environment. This transactional workload previously processed smoothly under high volume, but it has recently started failing unexpectedly during peak business hours.

When multiple users submit orders simultaneously, the transaction requests remain pending for an extended period instead of committing immediately. Previously, brief row-level locks resolved without disrupting operations. Now the connection pool stalls and transactions terminate with a "Lock wait timeout exceeded; try restarting transaction" error. When inspecting general process lists, we can see the waiting threads accumulating, but the underlying queries causing the block are not clearly identifiable.

Summary of the problem:

Concurrent e-commerce checkout transactions fail with Lock wait timeout exceeded errors.

Active threads pile up in the queue while waiting for row-level locks to release.

The specific blocking transactions in the InnoDB engine are not immediately visible through standard process monitoring.

Could you please advise whether this may be resolved by querying specific sys or performance_schema tables, or by reviewing engine transaction status logs? If there is an established procedure to trace and isolate these blocking transactions, I would be grateful for any guidance.

Thank you in advance for your assistance.

0 comments

1 answer

Your answer

Answer 1

Hello Isabella,

Thank you for posting your question on Microsoft Windows Forum!

The error:

Lock wait timeout exceeded; try restarting transaction

usually indicates that one transaction is holding locks for an extended period, causing other transactions to wait until they eventually time out.

Since you've already identified that threads are accumulating and standard process monitoring isn't clearly showing the blocker, I would focus on identifying the blocking transaction before making any configuration changes.

Recommended Troubleshooting Steps

Step 1: Check active InnoDB transactions

Run:

SELECT *

FROM information_schema.innodb_trx;

This will show currently running transactions, including those that have been active for a long time.

Step 2: Identify blocking and waiting sessions

If Performance Schema is enabled, run:

SELECT *

FROM performance_schema.data_lock_waits;

and

SELECT *

FROM performance_schema.data_locks;

These tables can help identify which session is blocking another transaction.

Step 3: Review InnoDB engine status

Run:

SHOW ENGINE INNODB STATUS\G

This is often the fastest way to identify:

Blocking transactions
Lock contention
Deadlocks
Long-running transactions

Pay special attention to the LATEST DETECTED DEADLOCK and TRANSACTIONS sections.

Step 4: Check for long-running transactions

Run:

SELECT

trx_id,

trx_state,

trx_started,

trx_mysql_thread_id

FROM information_schema.innodb_trx

ORDER BY trx_started;

A transaction that remains open for a long time can block checkout-related updates and inserts even when queries appear normal.

Step 5: Review application behavior

Since this affects checkout operations during peak hours, I would also verify:

Transactions are committed promptly.
Transactions are not left open while waiting for external services.
Application code is not holding locks longer than necessary.
Missing indexes are not causing excessive row scans.

My Observation

Because the issue previously worked normally and is now occurring during peak periods, the most likely causes are:

A recently introduced long-running transaction.
Increased lock contention caused by application changes.
Missing or inefficient indexes resulting in more rows being locked than expected.

The quickest way forward is usually:

SHOW ENGINE INNODB STATUS\G

combined with:

SELECT * FROM information_schema.innodb_trx;

These two outputs often reveal the exact transaction that other sessions are waiting on.

I hope this answer has brought you useful information. If so, please click on Accept Answer and consider upvoting it. Doing so helps other community members identify useful solutions to similar issues.