Protecting Against Concurrent Updates

Protecting Against Concurrent Updates

Assume that as we observe the net worths of movie executives using the function worthRanges of "Cursors" Figure 1, some other process is modifying the underlying MovieExec relation. We shall have more to say about a number of processes accessing a single database at the same time when we discuss transactions in "Transactions in SQL". However, for the moment, let us simply accept the possibility that there are other processes that could modify a relation as we use it.

What should we do about this possibility? Perhaps nothing. We might be happy with approximate statistics, and we don't care whether or not we count an executive who was in the process of being deleted, for instance. Then, we simply accept what tuples we get through the cursor.

On the other hand, we may not wish to allow concurrent changes to affect the tuples we see through this cursor. Rather, we may insist on the statistics being taken on the relation as it exists at some point in time. We cannot control exactly which modifications to MovieExec occur before our gathering of statistics, but we can expect that all modification statements appear either to have occurred completely before or completely after the function worthRanges ran, regardless of how many executives were affected by one modification statement. To obtain this guarantee, we may declare the cursor insensitive to concurrent changes.

Example 1:  We could modify lines (7) and (8) of "Cursors" Figure 1 to be:

8)               SELECT netWorth FROM MovieExec;

If execCursor is so declared, then the SQL system will guarantee that changes to relation MovieExec made between one opening and closing of execCursor will not affect the set of tuples fetched.

An insensitive cursor could be expensive, in the sense that the SQL system might spend a lot of time managing data accesses to assure that the cursor is insensitive. Again, a discussion of managing concurrent operations on the database is deferred to "Transactions in SQL". However, one simple way to support an insensitive cursor is for the SQL system to hold up any process that could access relations that our insensitive cursor's query uses.

There are certain cursors ranging over a relation R about which we may say with certainty that they will not change R.  Such a cursor c    an run all together with an insensitive cursor for R, without risk of changing the relation R that the insensitive cursor sees. If we declare a cursor FOR READ ONLY, then the database system can be sure that the underlying relation will not be modified because of access to the relation through this cursor.

Example 2: We could append after line (8) of "Cursors" Figure 1 a line

        FOR READ ONLY;

If SO, then any attempt to execute a modification through cursor execCursor would cause an error.