Two deadlock types
A deadlock within Db2® can occur when two transactions are both holding a lock wanted by the other transaction.
In a Db2 environment, two
deadlock types can occur when:
- Two resources are involved. Each transaction has locked one resource and wants the other resource in an incompatible mode. The resources are typically index pages and data pages. This is the classic deadlock situation.
- Only one resource is involved. The main purpose of update (U) locks is to reduce the number of
situations where a lock promotion caused the deadlock. The U-lock has solved most of these
situations, but it is still possible in specific situations to have a deadlock with only one
resource involved, because the resource can be locked in more than one way.
A typical example of this is when a transaction opens a cursor with the ORDER BY option and uses an index to avoid the sort. When a row in a page is fetched, Db2 takes a share (S) lock at that page. If the transaction then issues an update without a cursor for the row last fetched, the S-lock is promoted to an exclusive (X) lock.
If two of these transactions run concurrently and both get the S-lock at the same page before taking the X-lock, a deadlock occurs.