Managing deadlocks in Greenplum
Article ID: 295681
Updated On:
Products
Issue/Introduction
This article will explore:
- How deadlocks occur in Greenplum
- What the deadlock timeout is
- How to interpret the error raised by Greenplum in case of a deadlock timeout
Environment
Resolution
How deadlocks occur in your Database
Before you look at the details about deadlocks, it is important to understand how they occur.
process A: BEGIN; process B: BEGIN; process A: UPDATE users SET name = "Xavier" WHERE id = 1; process B: UPDATE users SET name = "Magnus" WHERE id = 2; -- Both process A and B acquired an exclusive lock in their transactions. -- The lock will be released when the transactions finishes. process A: UPDATE users SET name = "Magneto" WHERE id = 2; -- process A tries to acquire an exclusive lock, -- but process B already holds the lock for the record with id = 2 -- process A needs to wait till process B's transaction ends process B: UPDATE users SET name = "Professor X" WHERE id = 1; -- process B tries to acquire an exclusive lock, -- but process A already holds the lock for the record with id = 1 -- process B needs to wait till process A's transaction ends
At this point, process A is waiting for process B, and process B is waiting for process A. In other words, a deadlock has occurred. Neither of the two processes can continue and they will wait for each other indefinitely.
What is a deadlock timeout?
To resolve the situation explained above, Greenplum raises a deadlock error if it detects that two processes are waiting for each other. Greenplum will wait for a given interval before it raises the error. This interval is defined with the deadlock_timeout configuration value.After the deadlock timeout passes, the following error message is outputted:
"ERROR","40P01","deadlock detected","Process 32790 waits for ExclusiveLock on relation 4553521 of database 24880; blocked by process 35140. Process 35140 waits for RowExclusiveLock on relation 4553534 of database 24880; blocked by process 32790.", "UPDATE foo set START_TIME = $1, END_TIME = $2"deadlock.c",931,You can determine the deadlock objects by querying
pg_class:
database=# select relname from pg_class where oid = 4553534; relname ------------------------------ foo (1 row) database=# select relname from pg_class where oid = 4553521; relname ---------------------- bar (1 row)
Some objects may not initially appear related, but further inspection will generally reveal a dependency or a foreign key.
It is not highly unusual for an OLAP (Online Analytical Processing) database with high OLTP (Online Transaction Processing) loads to encounter deadlocks with high OLTP (Online Transaction Processing) loads.
Rollbacks are not ideal but they are a better solution than waiting forever.
Workflow Design Considerations
If possible you should strive to design your application in a way that prevents deadlocks in the first place. For example, if you are locking tables in your application, you want to make sure that you always invoke the locking in the same order.
In general, applications must be ready to handle deadlocks and retry the transaction in case of a failure.
The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.
Adjusting the deadlock timeout
The deadlock timeout is the amount of time that Greenplum waits on a lock before it checks for a deadlock.
The default timeout value in Greenplum is 1 second. However, this is probably the smallest time interval you would want to set in practice. If your database is heavily loaded, you might want to raise this value to reduce the overhead on your database servers.
Ideally, the deadlock_timeout should be a bit longer than your typical transaction duration.
For example, to increase the deadlock_timeout to 5 seconds, use the following command:
gpconfig -c deadlock_timeout -v "5s"
Additional Information
If you found this article helpful, please leave a thumbs up!
Feedback
