Ответ 1

Эксклюзивность блокировок X vs U

В блокировка матрицы совместимости ниже видно, что блокировка X совместима только со стабильностью схемы и Insert Range-Null блокировки. U совместим со следующими дополнительными типами общей блокировки S/IS/RS-S/RI-S/RX-S

матрица совместимости замков http://i.msdn.microsoft.com/ms186396.LockConflictTable(en-us,SQL.105).gif

Гранулярность блокировок X

Они выводятся отлично на всех уровнях. Трассировка script и профайлера ниже демонстрирует, что они успешно удаляются на уровне строк.

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, которая всегда будет нужна читателю. Это, конечно, повлияет на concurrency.

Другие оговорки

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

Ответ 2

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

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

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

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

Ответ 3

на основе комментариев в @Martin answer, вот немного script (запустите разные части в разных 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