Правильный способ взять эксклюзивный замок

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

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

Я хотел бы сделать эксклюзивную блокировку на уровне строк вместо уровня таблицы, ночто я прочитал до сих пор говорит, что не могу сделатьwith (XLOCK, ROWLOCK, HOLDLOCK) если другие транзакции выполняются вREADCOMMITED уровень изоляции (что для меня).

Правильно ли я использую монопольную блокировку на уровне таблицы, и есть ли в Server 2008 R2 способ заставить монопольные блокировки на уровне строк работать так, как я хочу, без изменения других запросов, выполняемых в базе данных?

declare client_cursor cursor local forward_only for 
     select distinct CLIENT_GUID from trnHistory
open client_cursor

declare @ClientGuid uniqueidentifier
declare @TransGuid uniqueidentifier

fetch next from client_cursor into @ClientGuid
WHILE (@@FETCH_STATUS  -1)
BEGIN
    IF (@@FETCH_STATUS  -2)
    BEGIN
        begin tran

        declare @temp int

        --The following row will not work if the other connections are running READCOMMITED isolation level
        --select @temp = 1 
    --from trnHistory with (XLOCK, ROWLOCK, HOLDLOCK) 
    --left join trnCB with (XLOCK, ROWLOCK, HOLDLOCK) on trnHistory.TRANS_GUID = trnCB.TRANS_GUID
    --left join trnClients with (XLOCK, ROWLOCK, HOLDLOCK) on trnHistory.TRANS_GUID = trnClients.TRANS_GUID
    --(Snip) --Other tables that will be "touched" during the reconcile
    --where trnHistory.CLIENT_GUID = @ClientGuid

        --Works allways but locks whole table.
    select top 1 @temp = 1 from trnHistory with (XLOCK, TABLOCK) 
    select top 1 @temp = 1 from trnCB with (XLOCK, TABLOCK)
    select top 1 @temp = 1 from trnClients with (XLOCK, TABLOCK)
    --(Snip) --Other tables that will be "touched" during the reconcile

        declare trans_cursor cursor local forward_only for 
                select TRANS_GUID from trnHistory where CLIENT_GUID = @ClientGuid order by TRANS_NUMBER
        open trans_cursor

        fetch next from trans_cursor into @TransGuid
        WHILE (@@FETCH_STATUS  -1)
        BEGIN
            IF (@@FETCH_STATUS  -2)
            BEGIN

                --Do Work here

            END
            fetch next from trans_cursor into @TransGuid
        END

        close trans_cursor
        deallocate trans_cursor

            --commit the transaction and release the lock, this allows other 
            -- connections to get a few queries in while it is safe to read.
        commit tran
    END

    fetch next from client_cursor into @ClientGuid
END 

close client_cursor
deallocate client_cursor
 Laurence18 нояб. 2012 г., 01:28
Вы говорите, что данные уже противоречат друг другу, и вы хотите, чтобы люди не читали их, пока выВы исправляете это?
 Scott Chamberlain18 нояб. 2012 г., 01:34
@ Laurence Нет данных непротиворечивы, но неверны с помощью корректируемого алгоритма. Однако, чтобы исправить это смещение, необходимо внести изменения в первое звено в цепочке, разрывая "consistancy»и это исправление распространялось вниз по цепочке, где каждое звено могло делать с ним разные вещи. Как только вся цепочка была исправлена, она снова "Последовательная», Во время этого процесса исправления я не могу допустить чтения (если я не могу 'не остановить их, как read_uncomited) Я
 Laurence18 нояб. 2012 г., 00:57
Я пытаюсь понять, зачем вам нужен эксклюзивный замок. Могут ли другие люди вставлять записи? Другие люди обновляют записи? Вы беспокоитесь о том, что другие люди могут получить непоследовательное представление о данных?
 Laurence18 нояб. 2012 г., 01:23
Я нене понимаю, почему транзакция не будетЯ могу защитить вас от этого, если только у вас нет людей, выполняющих read_uncommitted.
 Scott Chamberlain18 нояб. 2012 г., 01:25
@ Лоренс, я знаю, что это так, мой вопрос: «Правильно ли я использую эксклюзивную блокировку на уровне таблицы, и есть ли в Server 2008 R2 способ сделать так, чтобы эксклюзивные блокировки на уровне строк работали так, как я хочу, без изменения других запросов, выполняющихся в базе данных? ", Как я уже говорил ранее, я работаю с 5 базами данных, я хочу заблокировать все 5 одновременно, а не при первом касании каждой из них, чтобы сохранить согласованность. Я обновлю свой пример кода, чтобы показать другие таблицы, поскольку вы заставили меня понять, что я допустил одну ошибку.
 Laurence18 нояб. 2012 г., 02:32
Я, вероятно, плотный, но если выбеспокоюсь только о других читателях, не так ли?Достаточно ли для этого транзакции? начать транзакцию, сделать непоследовательным, сделать непротиворечивым, совершить транзакцию. Никто не видит середину, если они не говорят, что они нене возражаю против противоречивых данных. Если ты'Если вы беспокоитесь о других авторах, то я думаю, что xlocks со строкой будут в порядке. Вы упомянули 5 баз данных в один момент, хотя. Там могут быть морщины с распределенными транзакциями.
 Scott Chamberlain18 нояб. 2012 г., 02:34
Когда я сказал, что 5 баз данных я оговорился, я имел в виду 5 таблиц. Я не могу редактировать старый комментарий.
 Scott Chamberlain18 нояб. 2012 г., 02:40
@ Лоренс, чем больше я об этом думаю, тем лучше. Превратите это в ответ, и я отмечу, что он принят.
 Scott Chamberlain18 нояб. 2012 г., 01:21
@Laurence Я беспокоюсь о том, что другие люди могут получить непоследовательное состояние просмотра. Я пытаюсь исправить ошибку, которая затронула небольшой процент клиентов, однако этот процесс исправления оставляет несколько взаимозависимых строк в нескольких таблицах (на самом деле я буду блокировать 5 таблиц, но упростил мой пример кода до одной таблицы) в несогласованном состоянии во время процесс исправления. Несоответствие изолирован для каждого клиента, но делаетSELECT SUM(ColA) FROM trnHistory из-за того, что один клиент вернул бы неверное значение во времякоррекция» процесс. Поэтому мне нужно взять эксклюзивную блокировку для предотвращения чтения.

Ответы на вопрос(2)

XLOCK не будет блокировать одновременный читатель вread committed поэтому я просто воспроизвел это: это правда. Автор сценария:

Сессия 1:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

SELECT * FROM T WITH (ROWLOCK, XLOCK, HOLDLOCK /*PAGLOCK, TABLOCKX*/) WHERE ID = 123

Сессия 2:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

SELECT * FROM T WHERE ID = 123

Вставьте имя таблицы, которая у вас под рукой. Сессия 2 не блокируется.

Я также пытался использоватьPAGLOCK но это нетоже не работает. Затем я попробовалTABLOCKX но это нетоже не работает!

Таким образом, ваша стратегия на основе блокировки таблицы не работает. Я думаю ты'придется изменить читателей так, чтобы они либо

использовать изоляцию моментальных снимков, чтобы получить согласованное представление (как и до любой записи)использовать более высокий уровень изоляции, чтобы быть заблокированным автором

Конечно, существует неприятный обходной путь, чтобы действительно, действительно заблокировать таблицу: изменить ее схему. Это займетSch-M блокировка, которая конфликтует практически с любым доступом к таблице. Он даже содержит некоторые операции чтения метаданных. Это может выглядеть так:

--just change *any* setting in an idempotent way
ALTER TABLE T SET (LOCK_ESCALATION = AUTO)

Я проверил это, чтобы работать.

Прав ли SQL Server не подчинятьсяXLOCK? Или это недостаток продукта? Я думаю, что это правильно, потому что это соответствует задокументированным свойствамREAD COMMITTED, Кроме того, даже используяSERIALIZABLE бывают случаи, когда одна транзакция может заблокировать строку исключительно, а другая может прочитать эту же строку! Это может происходить при наличии индексов. Одна транзакция может X-блокировать некластеризованный индексIX_T_SomeCol в то время как другой радостно считывает кластерный индекс.PK_T

Так что на самом деле вполне нормально, что транзакции могут выполняться независимо даже при наличии исключительной блокировки.

 usr29 авг. 2015 г., 19:20
@Xin это держало это из-заHOLDLOCK, Это идентичноSERIALIZABLE и удерживает все блокировки до конца транзакции.
 Jim11 мар. 2017 г., 08:53
@usr You 'прав, мой плохой. Может быть, я прочитал того же самого Павластатья (Пол Уайт)sqlblog.com/blogs/paul_white/archive/2010/11/01/... Я все еще могухотя, чтобы воспроизвести его, я должен попробовать более новую версию. Очень удивительный результат! Но он говорит:SQL Server не может применить оптимизацию блокировки, если на содержащей странице есть незафиксированное изменение. "
 Xin29 авг. 2015 г., 18:11
Да, только уровень изоляции REPEATABLE READ или SERIALIZABLE может заблокировать сеанс 2, потому что, хотя сеанс получает XLOCK, и даже если вы не фиксируете транзакции, сеанс 1 уже освобождает XLOCK после завершения его чтения. Это больше не удерживало.
 usr10 мар. 2017 г., 11:06
@ Джим Я однажды прочитал об этом авторитетное сообщение в блоге одного из разработчиков (Пол Рэндалл ?!). Это оптимизация, чтобы сделать RC изоляциюне возьмите S-замок для каждого ряда, который дорог. Может быть, в древнем 2005 году этого еще не было. Хорошо знать. Я конечно проверял на>= 2008.
 SalientBrain18 сент. 2013 г., 21:00
мы обнаружили, что только если я использую уровни изоляции REPEATABLE READ или SERIALIZABLE во время чтения sql, он блокируется (если существует некоммитированный rowlock + xlock)
 Jim06 мар. 2017 г., 13:50
Я не вижу такого поведения. Попробовал это на SQL Server 2005, и он блокируется, как я и ожидал, не зная, как вам удалось получить транзакцию для чтения строки с помощью XLOCK, но матрица совместимости блокировок довольно ясно говорит о несовместимости X с S (т.е. вы можетечитать совершенные, когда естьs эксклюзивный замок на ряд).
Решение Вопроса

то вам не следуетне нужны эксклюзивные замки, шаблон

Begin Transaction

  Make Data Inconsistent

  Make Data Consistent

Commit Transaction

Все должно быть в порядке. Единственные сеансы, которые увидят противоречивые данные, это те, которые используютnolock или жеRead Uncommittedили те, которые ожидают сделать несколько последовательных чтений без использованияRepeatable Rows или же .Serializable

В ответ на вопрос, по моему мнению, правильный способ сделать эксклюзивную блокировку - это устроить так, чтобы двигатель сделал это за вас.

Ваш ответ на вопрос