Currently committed enabled Scenario


In a typical two-users scenario, User A and User B perform UPDATEs on two different tables. Before they commit the transaction, they perform a SELECT on the table the other person is updating. Upon successful retrieval of the information, they commit the transaction.

Step Application A Application B
Step 1 USER A UPDATEs customer table USER B UPDATEs product table
Step 2 USER A SELECTs rows from product table USER B SELECTs rows from customer table
Step 3 USER A COMMITs the transaction USER B COMMITs the transaction

Problem


In the above scenario User A will wait for USER B and USER B will wait for USER A causing a deadlock.


Solution


With the new currently committed semantics in DB2 9.7, only committed data is returned even though there is a pending transaction on the same set of data. Readers do not have to wait for completion of the pending transaction, hence avoiding deadlocks. Refer to the table above for the scenario. You may also use the buttons on the right pane to try out the transactions yourself.

Note:- Please click on the buttons in order.