SQL Server, вводящий в заблуждение XLOCK & оптимизации

Из недавнего тестирования и чтения, которое я сделал, кажется, что "X" (эксклюзивная) часть названия XLOCK вводит в заблуждение. На самом деле он блокирует не больше, чем UPDLOCK. Если бы это было исключение, это предотвратило бы внешние SELECTs, которые это не делает.

Я не вижу ни из чтения, ни из тестирования, ни разницы между ними.

XLOCK создает эксклюзивную блокировку только при использовании с TABLOCK. Мой первый вопрос "почему только при такой детализации?"

Далее я наткнулся на блог, в котором говорится следующее:

Однако следите за подсказкой XLOCK. SQL Server будет эффективно игнорировать подсказку XLOCK! Существует оптимизация, при которой SQL Server проверяет, изменились ли данные со времени самой старой открытой транзакции. Если нет, то xlock игнорируется. Это делает подсказки xlock в основном бесполезными и их следует избегать.

Кто-нибудь сталкивался с этим явлением?

Судя по тому, что я вижу, этот намек следует игнорировать.

Источник

3 ответа

Решение

Эксклюзивность X замки против 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 замки

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

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
Источник
Другие вопросы по тегам