INF: Concurrency and Consistency and SQL Server Alternatives

ID: Q43199

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5, 7.0

SUMMARY

The following is an overview of the concurrency versus consistency problem and the alternatives available with the SQL Server.

The information on locking in the SQL Server is based on the methods described in the paper "Granularity of locks and degrees of consistency in a shared data base" by J.N. Gray, G.R. Putzolo, and I.L. Traiger, in "Modeling in Data Base Management Systems," North Holland, 1976.

MORE INFORMATION

There is a trade-off between sharing data and maintaining consistency when multiple users update the same data. The best solution is to only make changes that read and update in an atomic unit, rather than reading and updating in two separate steps. The best example is an account balance update transaction. If the transaction consists of reading the account balance, adding or subtracting some amount, and then writing it back, it will be necessary to lock the account between the read and the update in order to ensure that the update is not undone by another transaction.

If the transaction were constructed without a separate read (that is, UPDATE ACCOUNT SET BALANCE=BALANCE-AMOUNT WHERE ID=...), then no locking is required. To prohibit negative balances, use a trigger or add a condition to the WHERE clause that requires BALANCE to be greater than or equal to AMOUNT.

Some types of data, such as addresses, can just be replaced without regard for the previous value. The UPDATE command can be constructed to selectively alter only particular fields, thus avoiding the problem of overlaying the updates made to other fields by other transactions (which would occur if the entire row were replaced).

Reservation Application Example

In an application designed to control the scheduling of some resource (such as airline seats), it may be necessary to key in a lot of data once the desired resource is selected. If no special action is taken, the selected item will not be "reserved" until all of the information is keyed in. If another user happens to be trying to select the same item, one of them will "lose" and will have to key in the information again.

Using HOLDLOCK to place a SHARE lock on the selected item would eliminate the possibility of another user updating that item, but with many users competing for the same items, it is highly possible that multiple users would acquire SHARE locks on the same item, thus preventing any of them from updating it. The first user attempting to update the item would be blocked indefinitely. The second user attempting an update would cause a deadlock, and one of the two users would be terminated in order to break the deadlock. Acquiring an exclusive lock on the item while reading it would eliminate the deadlock, but would reduce concurrency, since other users could not even read the item to see whether or not it was allocated.

The solution in this case is to add an "allocated" flag to the item. When the user selects an item, the transaction should issue an update with a WHERE clause that includes the unique identifier of the item and a condition that the "allocated" flag not be set. If the update is successful, the user can be prompted for the rest of the information. If the update fails, some other user selected the item first, but nothing must be re-entered.

General Browse Problem

In the general browse problem, several users browse through and selectively update rows in a table. If locking is used, an entire page full of rows must be locked each time a user pages forward through the data. This greatly reduces concurrency because the users may not update any of the rows that have been reserved for them. HOLDLOCK would allow other users to read the rows, but not update them.

If locking is not used and if the updates depend on the previous value of the data, inconsistencies can result. Timestamping is a solution to this problem. Timestamp is a special data type that is automatically updated whenever a row is changed. Transactions can avoid updating rows that have changed since the last read by issuing the UPDATE with a WHERE clause that includes not only the unique identifier of the row, but also a timestamp comparison. If the timestamp is not the same as when the row was last read, the update fails. In this approach, the data entered as part of the update is usually lost, but the user interface could include the option to display the new values and ask the user whether or not the update should be applied.

Summarization Consistency Problem

Another problem that occurs when multiple transactions update the same data is the summarization inconsistency problem. If account balances are being updated while a summarization report is reading the database, the report may contain inconsistencies. This will only occur if updates are allowed against accounts that already have been summarized in addition to those that have yet to be processed. The solution is to use HOLDLOCK on the summarization transaction. HOLDLOCK causes SHARE locks to be held until commit time. The end result is that other transactions can update accounts that have not yet been read for the summarization report, but no updates are permitted against accounts that have already been processed.

Allowing the updates to process against accounts that have not yet been read is essentially the same as allowing those updates to run before running the summarization report, and then prohibiting all updates while it runs. The HOLDLOCK approach provides the same degree of consistency as locking the entire table, and it allows more concurrency.

Additional query words: Optimization tuning

Keywords : kbother SSrvServer
Version : winnt:6.0,6.5,7.0
Platform : winnt
Issue type : kbinfo