Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 383

GO
UPDATE Production.Product
WITH (UPDLOCK)
SET ListPrice=159.99
WHERE ProductNumber='ST-1401'
The preceding query will take a certain amount of time to update the rows based on
the ProductNumber condition. The locking mode for this query will be Exclusive
Lock ( LCK_M_X ).
14. Both the queries on steps 12 and 13 are identified as slow updates for the rows
based on the ProductNumber='ST-1401' . Without blocking each process, the
update will succeed after a certain amount of time.
This completes the steps in designing sustainable locking methods to improve concurrency in
the data platform.
How it works...
We started with the TRANSACTION ISOLATION LEVEL to SERIALIZABLE to use the
HOLDLOCK hint by opening two separate query windows to execute the SELECT query.
Executing or setting the isolation level to serializable may not be necessary if we use the
default SQL Server isolation level, which is READ COMMITTED . As per the recipe, the isolation
level will affect the connection until we explicitly change it. Then, we looked into using the
sys.dm_tran_locks system catalog to obtain locking information for the relevant query
sessions. This will display the information about the time of row or page locks held on the
table that will allow the potential locking mechanisms on transactions to modify the query's
rows while the original transactions remain open. While the data modifications is processing,
whether or not the actual update is committed or rolled back at a later time, using the
SERIALIZABLE isolation allows dirty reads that allows data reads in progress.
Later, we used scripts to see how to use optimistic locking using SNAPSHOT ISOLATION by
referring to the READ COMMITTED option. The first process to hold the row version of SELECT
and the next process of UPDATE statements are executed in different query windows. As
per the settings, the SELECT statement will execute without holding any locks due to the
snapshot isolation setting for that session. However, the second session will start updating
the data using exclusive UPDLOCK until the update is executed that generates a new version
of row. Another parallel operation of a similar update on another window will capture the
automatic row versioning based on the snapshot isolation mode that is prevented by the first
process from overwriting the update performed by the second process that will prevent any
lost update.