It is not always easy to use DB2 and updates can cause a lot of headache. As soon as you start updating you risk ending up in situations where many parallel tasks wants to make the same updates. You either loose data because "last update wins" or you get a deadlock.

Imagine that your program selects a row from a table, performs some calculations or string manipulations and updates the row using the new results. At the same time another task using the same program selects the same row before the first task completes the calculations. Now you have a "last update wins" situation and a potential data loss, especially if the second task was supposed to base its calculations upon the result of the calculations made by the the first task.

This is a common problem in any online system and many different solutions exists. If you want to serialize the tasks, you can use the following trick:

1. Before select'ing the row to be manipulated you perform a dummy update of the row. Look at the example below.
2. Then you perform your SELECT.
3. Then you perform the UPDATE.

This idea is built upon tip week 40/2001. The trick is that you can perform an update that doesn't update anything, but it acquires an exclusive (X) lock on the page containing the row. This lock prevents other tasks from both reading and updating the involved page. The other tasks running the same program will now be waiting at the dummy UPDATE statement, and only when the first task performs a COMMIT, the next task will be allowed to perform the UPDATE.

Here is some pseudo code to illustrate the solution:

UPDATE MYTABLE SET COLX = COLX WHERE COLA = :VARA;
SELECT COLY, COLZ INTO :VARY, :VARZ FROM MYTABLE WHERE COLA = :VARA;
VARY = ADVANCEDYFUNC(VARY);
VARZ = ADVANCEDZFUNC(VARZ);
UPDATE MYTABLE SET COLY = :VARY, COLZ = :VARZ WHERE COLA = :VARA;
COMMIT;

The first UPDATE is the dummy UPDATE acquiring the X lock, even though it doesn't update anything. This UPDATE is our enqueue. The WHERE clause in the dummy UPDATE must be an exact copy of the WHERE clause in the following SELECT statement. There is nothing wrong in making a dummy UPDATE of something completely different, but then you might cause trouble somewhere else. It is a good idea to make sure that the dummy UPDATE only updates one row, because it allows for the largest possible concurrency.

The COMMIT statement is our dequeue. It is important to realize that DB2 has no other enqueue/dequeue mechanism. You must therefore think carefully before you start using this technique especially because the COMMIT releases all locks and not just the locks you have in mind. If you plan to use the above technique in the middle of a task performing other updates, you must leave out the COMMIT in the above example, allowing the usual COMMIT to end the unit of work.

You must also take into consideration that a DB2 lock unlike an enqueue only lasts for a preset amount of time determined at DB2 startup time by your systems programmers. You must remember to consider this situation, too. When your DB2 lock is timed out DB2 rolls back your entire unit of work, so normally no harm is done and the user must retry the transaction.