SQL Server, the misleading XLOCK & optimizations

by

Exclusivity of X locks vs U locks

In the lock compatibility matrix below it can be seen that the X lock is only compatible with the schema stability and Insert Range-Null lock types. U is compatible with the following additional shared lock types S/IS/RS-S/RI-S/RX-S

lock compatibility matrix http://i.msdn.microsoft.com/ms186396.LockConflictTable(en-us,SQL.105).gif

Granularity of X locks

These are taken out fine at all levels. The script and profiler trace below demonstrates them being successfully taken out at row level.

CREATE TABLE test_table (id int identity(1,1) primary key, col char(40))

INSERT INTO test_table
SELECT NEWID() FROM sys.objects

select * from test_table with (rowlock,XLOCK) where id=10

Trace

But rows can still be read!

It turns out that at read committed isolation level SQL Server will not always take out S locks, it will skip this step if there is no risk of reading uncommitted data without them. This means that there is no guarantee of a lock conflict ever occurring.

However if the initial select is with (paglock,XLOCK) then this will stop the reading transaction as the X lock on the page will block the IS page lock that will always be needed by the reader. This will of course have an impact on concurrency.

Other Caveats

Even if you lock the row/page this does not mean that you block all accesses to that row in the table. A lock on a row in the clustered index will not prevent queries reading data from the corresponding row in a covering non clustered index.

More Related Contents:

  1. Optimistic vs. Pessimistic locking
  2. Confused about UPDLOCK, HOLDLOCK
  3. How to split a comma-separated value to columns
  4. Using T-SQL, return nth delimited element from a string
  5. How to get a float result by dividing two integer values using T-SQL?
  6. Get first day of week in SQL Server
  7. SQL Server add auto increment primary key to existing table
  8. How to delete large data of table in SQL without log?
  9. Retrieve column definition for stored procedure result set
  10. Change Notification with Sql Server 2008
  11. Do while loop in SQL Server 2008
  12. Query times out when executed from web, but super-fast when executed from SSMS
  13. TSQL: How to convert local time to UTC? (SQL Server 2008)
  14. Regular Expressions in SQL Server servers?
  15. Unique constraint on multiple columns
  16. Transpose rows and columns with no aggregate
  17. How to find the port for MS SQL Server 2008?
  18. Drop primary key using script in SQL Server database [duplicate]
  19. How to convert number of minutes to hh:mm format in TSQL?
  20. How to find SQL Server running port?
  21. Changing SQL Server collation to case insensitive from case sensitive?
  22. Temporarily disable all foreign key constraints
  23. How to display two digits after decimal point in SQL Server
  24. The request failed or the service did not respond in a timely fashion?
  25. Merge multiple rows into a single row
  26. In SQL Server, how can I find everywhere a column is referenced?
  27. ‘Incorrect SET Options’ Error When Building Database Project
  28. Using Full-Text Search in SQL Server 2008 across multiple tables, columns
  29. Why insert-select to variable table from XML variable so slow?
  30. Generate MD5 hash string with T-SQL

Leave a Comment