Coding guidelines to avoid deadlocks

Deadlock refers to a specific condition in a database, when two processes are waiting for the other process to release a resource. For example, when one client application holds a lock on a table and attempts to obtain the lock on a second table that is held by another client application, this may lead to a deadlock if the other application attempts to obtain the lock that is held by the first application.

To circumvent the deadlock problem, it is recommended that you need to sort the information to be accessed in a certain order before grabbing locks. This is applicable to situations where you need to grab multiple inventory item locks within a single transaction boundary. However, you do not need to sort if you call the APIs to process single items per transaction commit.

Reading Uncommitted Data in DB2® Database

In DB2, when you select a record from a table, a read lock is obtained on the record. If the record being selected has been updated but not committed, the thread waits until it commits the changes. Alternatively you could read the record with Uncommitted Read (UR) in which case the latest value that has been updated is provided to the user.

You can read uncommitted data from any list API by enabling the ReadUncommitted attribute to Y in its input XML. To achieve this, you must customize the individual JSP and pass the ReadUncommitted attribute as a hidden attribute. For example:

<input type="hidden" name="xml:/Order/@ReadUnCommitted" value="Y"/>

As a result, the locking scenario is circumvented in the DB2 database. Locking is the default in DB2.

It is not mandatory to pass this flag. However, if you set this flag to Y, the system is forced to read uncommitted data. For example, a transaction, T1, updates Table TAB-1 but the transaction's data is not committed. If the ReadUncommitted flag is set to Y, other transactions can read the uncommitted data in Table TAB-1.

Before setting this flag, evaluate concurrent transactions to determine whether a situation exists in which a deadlock is occurring. If no such situation occurs, the flag should remain at its default setting.

This behavior is different from Oracle, hence if you are writing custom code on DB2 you should understand this behavior to avoid lock escalations.