Avoiding Deadlock

A deadlock (also known as deadly embrace) is an egregious situation that can arise in any system that involves concurrent access to shared data from multiple processes. In its simplest form, process 1 holds an exclusive lock on data item A and is requesting a lock on data item B while at the same time process 2 holds an exclusive lock on data item B while requesting a lock on data item A. As you can easily see, both processes will wait forever unless one or the other releases the lock it holds. Of course, much more complex deadlock scenarios exist that involve multiple processes.

The primary application programming technique available in RDM that can be used to avoid deadlock is the timeout. A lock request will fail if the lock is not granted within the time duration specified by the connection's timeout value. The default timeout is set to 10 seconds. As noted above, this value can be changed using either the SET TIMEOUT statement.

While timeouts can be used to avoid deadlock, a related condition known as a livelock can still occur in which, in the example above both of process 1's and process 2's lock requests timeout at the same time, causing each to free the other lock as well and then restart their respective transactions with the timing of the operations such that the same situation continues to repeat itself.

Both livelock and deadlock can be avoided by including in a single request locks on all of the tables (i.e., a grouped lock request) that will potentially be modified by a transaction. As noted in the last section, a transactional stored procedure performs a grouped lock request for all needed locks at the beginning of the transaction, before any modification statements have executed. The table locks included in grouped lock requests made by RDM SQL are always specified in the same order. While a timeout can still certainly occur, neither a deadlock nor livelock situation will occur.

However, if you are issuing dynamic SQL transactions that include multiple database modification statements, you need to explicitly lock all tables that can be modified in the START TRANSACTION statement. If you do not explicitly lock the tables in a dynamic SQL transaction, SQL will automatically make the lock requests for each statement. If a timeout occurs during execution of a database modification statement, the correct response is to ROLLBACK the transaction and then restart it.

It is also recommended that you encapsulate all of your transactions in transactional stored procedures in order to ensure that deadlock and livelock situations are avoided.