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:
Your program shows you only updated rows.
Your program shows you only original rows.
Your program might return a mixture of original rows and updated rows.
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:
To control concurrent events such as these, use the locking and isolation level features of the database server.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]