Managing Concurrency With Cursor Locks

   

The goal of using cursor locks is to ensure separate applications don't interfere with one another. Multiuser database applications encounter concurrency problems whenever several users try to access or update the same information at the same time. Concurrency has to do with separate processes, running at the same time, trying to use the same item. With pessimistic cursor locks, you can temporarily prohibit either read access or changes by other applications. With optimistic cursor locks, you expect no concurrent changes and do not prohibit access or changes by other applications.

The following list describes several common situations where you must manage concurrency.

• Temporary update   This occurs when one transaction reads the uncommitted updates of another. If the first transaction does a rollback, the second transaction has misleading data. The solution is to prevent access to uncommitted updates.
• Lost update   This occurs when two transactions read the same data item with the intention of update. In this situation, the first update might be lost because the second update, arriving moments later, is based on the original value. Using a lock with the intent to update prevents this problem.
• Incorrect summary   This occurs when a single transaction updates two items. The database is in an inconsistent state until both updates have completed. If a summarization query reads the two items while the update is in progress, the summary can be erroneous. A read-lock on the result set eliminates the problem.

Your application can handle concurrency issues by using locks. Locking can prevent one process from reading data that is being changed by another process, and it can prevent a process from changing data that is about to be changed by another concurrent process.

Locking provides the benefit of ensuring correct data, but it also makes the other concurrent applications wait to apply their changes. The application design trade-off is to carefully lock what you must, but keep the update process small enough to be quick, thereby minimizing the wait time for the other applications.

You can control row and page locking by setting the appropriate cursor lock option. While the available lock options may vary with your choice of a cursor library, several lock types are generally available. These are described in the following table.

Lock Type	Description
Read only	This type of lock lets concurrent applications read the data, but no application can change the data. This lock is useful in conditions where your application must temporarily prevent data changes, but still can allow unrestricted reading.
Optimistic using values	Useful in conditions where there is only a small chance that a second user may update a row in the interval between when a cursor is opened and the row is finally updated. The current values in the row are compared with the values retrieved when the row was last fetched. If any of the values have changed, an error is returned. Performance is good because locks are not held on the rows that make up the result set.
Optimistic using rows	This is for tables where the rows have a modification timestamp column. The cursor engine compares each row's current timestamp value with the timestamp value that was stored when the row was last fetched to determine whether the row was updated. Because only the timestamp needs comparison, this is faster than checking all of the values.
Shared	This type of lock allows many processes to read the same information, but changes are not permitted. An example of shared locks is when many processes have the same table open for reading. If your application must temporarily prevent other users from changing the data, but still let them read the data without waiting, shared locking is a good choice.
Exclusive	This option locks the data for modification as it is read into the cursor result set. If you must temporarily prevent other processes from changing the data, you should use exclusive locking on either the row, the table, or the page. Locking works best within the protection of a transaction with its commit and rollback options. The lock is held until the transaction is terminated. Positioned updates using a cursor can be blocked by other connections holding a lock on a row. Exclusive locking provides the greatest data protection and the slowest throughput.

For More Information   For more information on the different types of locks that SQL Server uses, search online for "Understanding Locking" in the SQL Server Books Online, and "Data Integrity and Concurrency in Microsoft SQL Server" in MSDN Library Visual Studio 6.0.