SQL Server: предотвращение грязного чтения в хранимой процедуре

Рассмотрим базу данных SQL Server и два ее хранимых процесса:

* 1. Proc, который выполняет 3 важные вещи в транзакции: создать клиента, вызвать sproc для выполнения другой вставки и условно вставить третью запись с новым идентификатором.

BEGIN  TRAN
    INSERT INTO Customer(CustName) (@CustomerName)
    SELECT @NewID = SCOPE_IDENTITY()

    EXEC  CreateNewCustomerAccount @NewID, @CustomerPhoneNumber

    IF @InvoiceTotal > 100000
         INSERT INTO  PreferredCust(InvoiceTotal, CustID) VALUES (@InvoiceTotal, @NewID)

COMMIT TRAN

* 2. Сохраненный процесс, который опрашивает Customer таблица для новых записей, которые не имеют связанных PreferredCust запись. Клиентское приложение выполняет опрос, вызывая этот сохраненный процесс каждые 500 мс. ВЫБРАТЬ на Customer НЕ включает транзакцию.

  --not in the Preferred list
   SELECT C.ID
   FROM Customer    AS C
   LEFT JOIN PreferredCust AS PRE ON PRE.CustID = C.ID
   WHERE PRE.CustID IS NULL

Возникла проблема, когда хранимая процедура опроса нашла запись в Customer таблицы, и вернул его как часть его результатов. Проблема была в том, что он взял эту запись, я полагаю, как часть грязного чтения. Запись закончилась записью в PreferredCust позже и в конечном итоге создали проблему вниз по течению.

Вопрос

  • Как вы можете явно предотвратить грязное чтение этим вторым хранимым процессом?
  • Насколько вероятно мое предположение о сценарии грязного чтения?

Среда - SQL Server 2005 со стандартной конфигурацией по умолчанию. Никаких других попаданий блокировки не дается ни в одной из этих хранимых процедур.

Эти два хранимых процесса вызываются из клиента Java через соединение JDBC. Неизвестно, используют ли они одно и то же соединение, но SQL Profiler показывает, что они используют один и тот же SPID и ClientProcessID.

Вот что показывает SQL Profiler:

SELECT @@MAX_PRECISION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET IMPLICIT_TRANSACTIONS OFF
SET QUOTED_IDENTIFIER ON
SET TEXTSIZE 2147483647
go
EXEC WriteNewCustomer  'CustomerX', 199000
go

--get any customers in the priority 
SELECT @@MAX_PRECISION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET IMPLICIT_TRANSACTIONS OFF
SET QUOTED_IDENTIFIER ON
SET TEXTSIZE 2147483647
go
EXEC GetCustomersWithLowInvoice
go
Источник

3 ответа

Вы не можете предотвратить грязное чтение. Писатели берут эксклюзивные блокировки, чтобы не допустить честного чтения, совершенного чтения. Но вы ничего не можете сделать, чтобы предотвратить грязное чтение. Грязный читатель должен прекратить делать грязные чтения, точка.

Предполагая, что код, который опрашивает таблицу Customer, находится под вашим контролем, решение состоит в том, чтобы удалить из запроса грязную подсказку о прочтении. Это, вероятно, вызовет конфликт, так как опрос теперь будет блокировать записи. Лучшее решение для этого - включить управление версиями строк:

ALTER DATABASE [<DBNAME>] SET ALLOW_SNAPSHOT_ISOLATION ON; 
ALTER DATABASE [<DBNAME>] SET READ_COMMITTED_SNAPSHOT ON;

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

Еще одно примечание: опрос каждые 500 мс? Возможно, вам следует использовать механизм уведомлений о запросах, чтобы сделать ваши кэши недействительными, см. "Таинственное уведомление".

Источник

Уровень изоляции по умолчанию read committed, Грязное чтение не может происходить при этом уровне изоляции.

Вероятно, есть еще одна причина, которую вы упустили из виду.

Источник

Поместите следующее в начало вашей процедуры (или непосредственно перед BEGIN TRAN).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Вы также можете выбрать REPEATABLE READ или же SERIALIZABLE, Тем не менее, я бы согласился с Andomar в том, что, вероятно, есть еще одна причина для изменения уровня изоляции, учитывая, что уровень по умолчанию READ COMMITTED,

Источник
Другие вопросы по тегам