Эксклюзивность X замков по сравнению с U замками

В матрице совместимости блокировок ниже можно увидеть, что X блокировка совместим только со стабильностью схемы и типами блокировки Insert Range-Null. U совместим со следующими дополнительными типами общих блокировок _5 _ / _ 6 _ / _ 7 _ / _ 8 _ / _ 9_

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

Степень детализации X блокировок

Их выносят штрафом на всех уровнях. Трассировка скрипта и профилировщика ниже демонстрирует их успешное извлечение на уровне строки.

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

Но строки все еще можно читать!

Оказывается, на read committed уровне изоляции SQL Server не всегда снимает S блокировок, этот шаг будет пропущен, если без них нет риска прочитать незафиксированные данные. Это означает, что нет никакой гарантии, что конфликт блокировок когда-либо произойдет.

Однако, если начальный выбор with (paglock,XLOCK), то этот остановит транзакцию чтения, поскольку блокировка X на странице заблокирует IS блокировку страницы, которая всегда будет нужна читателю. Это, конечно, повлияет на параллелизм.

Прочие предостережения

Даже если вы заблокируете строку / страницу, это не означает, что вы заблокируете весь доступ к этой строке в таблице. Блокировка строки в кластеризованном индексе не помешает запросам читать данные из соответствующей строки в покрывающем некластеризованном индексе.

Это не предупреждение, это неправильное понимание того, что происходит в SELECT.

Простой SELECT не запрашивает общие блокировки, если страницы не содержат грязных данных и, следовательно, не блокируются XLOCK.

Чтобы вас заблокировал XLOCK, вам нужно работать с уровнем изоляции REPEATABLE READ. Это может быть вызвано двумя вещами:

1. Изменение данных с помощью INSERT / UPDATE / DELETE. Обновляемая таблица не обязательно должна быть той, на которой включен XLOCK.
2. Явный запрос REPEATABLE READ через уровень изоляции транзакции или табличную подсказку.

на основе комментариев в ответ @Martin, вот маленький скрипт (запустите разные части в разных окнах SSMS, чтобы проверить блокировку, предотвращающую SELECT:

--
--how to lock/block a SELECT as well as UPDATE/DELETE on a particular row
--

--drop table MyTable
--set up table to test with
CREATE TABLE MyTable (RowID int primary key clustered
                     ,RowValue int unique nonclustered not null) 

--populate test data
;WITH InsertData AS
(
    SELECT 4321 AS Number
    UNION ALL
    SELECT Number+1
        FROM InsertData
        WHERE Number<9322
)
INSERT MyTable
        (RowID,RowValue)
    SELECT
        Number, 98765-Number
        FROM InsertData
        ORDER BY Number
    OPTION (MAXRECURSION 5001)

-----------------------------------------------------------------------------
-- #1
--OPEN A NEW SSMS window and run this
--
--create lock to block select/insert/update/delete
DECLARE @ID int

BEGIN TRANSACTION

SELECT @ID=RowID FROM MyTable WITH (ROWLOCK, XLOCK, HOLDLOCK) WHERE RowID=6822
PRINT @ID

--COMMIT  --<<<only run the commit when you want to release the lock
          --<<<adfter opening the other new windows and running the SQL in them



-----------------------------------------------------------------------------
-- #2
--OPEN A NEW SSMS window and run this
--
--shows how a select must wait for the lock to be released
--I couldn't get SSMS to output any text while in the trnasaction, even though
--it was completing those commands (possibly buffering them?) so look at the
--time to see that the statements were executing, and the SELECT...WHERE RowID=6822
--was what was where this script is blocked and waiting
SELECT GETDATE() AS [start of run]
SELECT '1 of 2, will select row',* FROM MyTable Where RowID=6822
go
DECLARE @SumValue int
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT GETDATE() AS [before transaction, shouldn't be nuch difference]
BEGIN TRANSACTION
SELECT @SumValue=SUM(RowID) FROM MyTable WHERE ROWID<6000
SELECT GETDATE() AS [in transaction, shouldn't be much difference]
    , @SumValue AS SumValue
--everything to here will run immediately, but the select below will wait for the
-- lock to be removed
SELECT '2 of 2, will wait for lock',* FROM MyTable Where RowID=6822
SELECT GETDATE() AS [in transaction after lock was removed, should show a difference]
COMMIT


-----------------------------------------------------------------------------
-- #3
--OPEN A NEW SSMS window and run this
--
--show how an update must wait
UPDATE MyTable SET RowValue=1111 WHERE RowID=5000  --will run immediately
GO
UPDATE MyTable SET RowValue=1111 WHERE RowID=6822 --waits for the lock to be removed

-----------------------------------------------------------------------------
-- #4
--OPEN A NEW SSMS window and run this
--
--show how a delete must wait
DELETE MyTable WHERE RowID=5000 --will run immediately
go
DELETE MyTable WHERE RowID=6822  --waits for the lock to be removed