Concurrency issues

To understand the hazards of concurrency, you must think in terms of multiple programs, each executing at its own speed. Suppose that your program is fetching rows through the following cursor:
EXEC SQL DECLARE sto_curse CURSOR FOR
   SELECT * FROM stock
      WHERE manu_code = 'ANZ';
The transfer of each row from the database server to the program takes time. During and between transfers, other programs can perform other database operations. At about the same time that your program fetches the rows produced by that query, another user's program might execute the following update: 
EXEC SQL UPDATE stock
   SET unit_price = 1.15 * unit_price
      WHERE manu_code = 'ANZ';
In other words, both programs are reading through the same table, one fetching certain rows and the other changing the same rows. The following scenarios are possible:
  1. The other program finishes its update before your program fetches its first row.

    Your program shows you only updated rows.

  2. Your program fetches every row before the other program has a chance to update it.

    Your program shows you only original rows.

  3. After your program fetches some original rows, the other program catches up and goes on to update some rows that your program has yet to read; then it executes the COMMIT WORK statement.

    Your program might return a mixture of original rows and updated rows.

  4. Same as number 3, except that after updating the table, the other program issues a ROLLBACK WORK statement.

    Your program can show you a mixture of original rows and updated rows that no longer exist in the database.

The first two possibilities are harmless. In possibility number 1, the update is complete before your query begins. It makes no difference whether the update finished a microsecond ago or a week ago.

In possibility number 2, your query is, in effect, complete before the update begins. The other program might have been working just one row behind yours, or it might not start until tomorrow night; it does not matter.

The last two possibilities, however, can be important to the design of some applications. In possibility number 3, the query returns a mix of updated and original data. That result can be detrimental in some applications. In others, such as one that is taking an average of all prices, it might not matter at all.

Possibility number 4 can be disastrous if a program returns some rows of data that, because their transaction was cancelled, can no longer be found in the table.

Another concern arises when your program uses a cursor to update or delete the last-fetched row. Erroneous results occur with the following sequence of events:
  • Your program fetches the row.
  • Another program updates or deletes the row.
  • Your program updates or deletes WHERE CURRENT OF cursor_name.

To control concurrent events such as these, use the locking and isolation level features of the database server.