SQL Server:防止存储过程中的脏读 [英] SQL Server: preventing dirty reads in a stored procedure

问题描述

考虑一个 SQL Server 数据库及其两个存储过程:

Consider a SQL Server database and its two stored procs:

*1.在事务中执行 3 件重要事情的 proc:创建客户，调用 sproc 执行另一个插入，并有条件地插入具有新标识的第三条记录.

*1. A proc that performs 3 important things in a transaction: Create a customer, call a sproc to perform another insert, and conditionally insert a third record with the new identity.

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 上的 SELECT 不涉及交易.

*2. A stored proc which polls the Customer table for new entries that don't have a related PreferredCust entry. The client app performs the polling by calling this stored proc every 500ms. The SELECT onto the Customer does NOT involve a transaction.

  --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 中有一个条目，并最终在下游产生了问题.

A problem has arisen where the polling stored procedure has found an entry in the Customer table, and returned it as part of its results. The problem was that it has picked up that record, I am assuming, as part of a dirty read. The record ended up having an entry in PreferredCust later, and ended up creating a problem downstream.

问题

• 如何通过第二个存储过程明确防止脏读?
• 我对脏读场景的假设有多大可能?

环境为 SQL Server 2005，默认配置为开箱即用.在这两个存储过程中都没有给出其他锁定命中.

The environment is SQL Server 2005 with the default configuration out of the box. No other locking hits are given in either of these stored procedures.

这两个存储过程是通过 JDBC 连接从 Java 客户端调用的.不知道它们是否使用相同的连接，但 SQL Profiler 显示它们使用相同的 SPID 和 ClientProcessID.

These two stored procs are being called from a Java client via a JDBC connection. It's unknown whether they are using the same connection, but SQL Profiler shows that they're using the same SPID and ClientProcessID.

以下是 SQL Profiler 显示的内容:

Here's what SQL Profiler shows:

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

推荐答案

您无法防止脏读.编写器采用排他锁来防止诚实、已提交读和读.但是您没有可以防止脏读.脏读者必须停止做脏读，期间.

You can't prevent dirty reads. Writers take exclusive locks to prevent honest, read committed, reads. But there is nothing you can do to prevent dirty reads. The dirty reader has to stop doing dirty reads, period.

假设轮询 Customer 表的代码在您的控制之下，解决方案是从查询中删除脏读提示.这可能会导致争用，因为轮询现在将阻止写入.最好的解决方案是启用行版本控制:

Assuming that the code that polls the Customer table is under your control, the solution is to remove the dirty read hint from the query. This will probably cause contention, since the polling will now block on the writes. The best solution for that is to enable row versioning:

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

然后简单地从 Customer 轮询作为正常查询，没有任何提示.您的民意调查不会阻止写入，因为行版本控制将启动并将查询扫描重定向到行的预更新、非锁定版本.

Then simply poll from Customer as a normal query, w/o any hints. Your poll won't block behind writes as row-versioning will kick in and redirect the query scan to a pre-update, non-locked version of the row.

还有一点:每 500 毫秒轮询一次?也许您应该使用查询通知机制来使您的缓存无效，请参阅 The Mysterious Notification.

One more note: polling every 500ms? Perhaps you should use a Query Notification mechanism to invalidate your caches, see The Mysterious Notification.

这篇关于SQL Server:防止存储过程中的脏读的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！