When a database appears “stuck,” the first assumption is often deadlock. But lock waiting and deadlocks are fundamentally different—and require different troubleshooting.
Lock Waiting
Transaction A holds a lock while Transaction B needs the same resource:
A: UPDATE row1 → holds lock
B: UPDATE row1 → waits for A
A: COMMIT → B continues
There is no cycle. B simply waits for A to release the lock.
When troubleshooting, ask:
- Who holds the lock?
- Who is waiting?
- Why is the transaction still open?
Deadlock
A deadlock occurs when transactions wait for each other:
A: locks row1 → waits for row2
B: locks row2 → waits for row1
Neither can proceed. The database detects the cycle and rolls back one transaction to break it.
The key difference:
Lock wait: “I'm waiting for your lock.”
Deadlock: “You're waiting for my lock, and I'm waiting for yours.”
How to Prevent Them
For GBase Database and other relational databases, three practices help:
1. Keep transactions short
Don't hold locks while waiting for external APIs or application processing.
2. Use a consistent lock order
If multiple resources must be locked, acquire them in the same order across transactions.
3. Find the blocker first
For lock waits, identifying the blocking transaction is often more useful than staring at the waiting query.
The DBA Takeaway
When production says “the database is stuck,” don't immediately blame a deadlock.
First determine: Is someone waiting, or are transactions waiting for each other?
That distinction points you toward the right fix—and makes troubleshooting GBase Database concurrency issues much faster.
