ID Number: Q43199
1.00 1.10 1.11 4.20
OS/2
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 reference words: Optimization and tuning