Database Reference
In-Depth Information
The queries are executed using transaction marks and the preceding statements are
executed using HOLDLOCK and TABLOCK hints along with SERIALIZABLE and READ
UNCOMMITTED isolated levels that create no additional locks on the key columns.
10. Now, use the optimistic locking method using SNAPHOT ISOLATION by using the
READ COMMITTED isolation level and use the additional UPDLOCK hint.
11. Open a New Query window on SSMS tool:
USE master
GO
ALTER DATABASE AdventureWorks2008R2 SET ALLOW_SNAPSHOT_ISOLATION
ON
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
USE Adventureworks2008R2
GO
BEGIN TRAN
SELECT Name, ProductNumber, ListPrice AS Price,ProductLine,DaysToM
anufacture
FROM Production.Product
WHERE ProductLine = 'M'
AND DaysToManufacture <2
ORDER BY Name ASC;
GO
The preceding query will return around 54 rows from the Production.Product table
based on the ProductLine=M.
12. Open another New Query window session:
USE Adventureworks2008R2
GO
UPDATE Production.Product
WITH (UPDLOCK)
SET ListPrice=159.90
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 Update Lock
(LCK_M_U).
13. On the first query window (step 12), execute the following query:
--Once the second window session query is completed, execute the
following UPDATE query
USE Adventureworks2008R2
 
Search WWH ::




Custom Search