SQL Server - 防止丢失更新和死锁 [英] SQL Server - Prevent lost update and deadlocks

问题描述

我正在开发一个项目，其中包含 2 个用 C#(.NET 框架 4)开发的应用程序 -

1. WCF 服务(向客户公开)
2. ASP.NET 网络表单应用程序(总部使用).

这两个应用程序都可以从 SQL Server 2005 数据库中的公共帐户"表中选择和更新行.帐户表中的每一行都包含一个客户余额.

两个应用程序中请求的业务逻辑都涉及从accounts"表中选择一行，根据余额进行一些处理，然后更新数据库中的余额.选择和更新余额的过程不能参与交易.

我意识到在选择行和更新它之间是可能的，可以通过来自相同或不同应用程序的另一个请求来选择和更新行.

我在下面的文章中发现了这个问题.我指的是丢失更新"的第二种情况.http://www.codeproject.com/Articles/342248/Locks-and-Duration-of-Transactions-in-MS-SQL-Serve

<块引用>

第二种情况是当一个事务(事务 A)读取一个将值记录并检索到局部变量中记录将由另一个事务(事务 B)更新.和稍后事务 A 将使用中的值更新记录局部变量.在这种情况下，事务 B 完成的更新可以被视为丢失更新".

我正在寻找一种方法来防止上述情况并防止在同一行收到多个并发请求时余额变为负数.一次只能通过一个请求(来自任一应用程序)来选择和更新一行，以确保余额一致.

我正在考虑在一个请求选择一行后立即阻止访问该行.根据我的研究，以下是我的观察结果.

1. 隔离级别

使用可重复读"隔离级别，2 个事务可以选择一个公共行.

我测试了这会打开 2 个 SSMS 窗口.在两个窗口中，我都启动了一个具有可重复读取隔离级别的事务，然后在公共行上进行选择.我能够选择每笔交易中的行.

接下来，我尝试更新每个事务的同一行.语句持续运行了几秒钟.然后第一个事务的更新成功，而第二个事务的更新失败并显示以下消息.

<块引用>

错误 1205:事务(进程 ID)在锁资源上死锁与另一个进程并已被选为死锁受害者.重新运行交易.

因此，如果我使用具有可重复读取的事务，则 2 个并发事务应该不可能更新同一行.Sql server 自动选择回滚1 个事务.这是正确的吗?

但我也希望通过仅允许单个事务选择特定行来避免死锁错误.

2. 行锁

我在 Stackoverflow 上找到了下面提到使用 ROWLOCK 提示来防止死锁的答案.(请参阅已接受答案的评论).

避免丢失更新"的最小事务隔离级别

我启动了一个事务并使用了带有 ROWLOCK 和 UPDLOCK 的 select 语句.然后在一个新的 SSMS 窗口中，我启动了另一个事务并尝试使用相同的选择查询(具有相同的锁).这次我无法选择该行.该语句在新的 SSMS 窗口中继续运行.

因此，在事务中使用 Rowlock 似乎会阻塞使用相同锁提示的 select 语句的行.

如果有人能回答以下问题，我将不胜感激.

1. 我对隔离级别和行锁的观察是否正确?

2. 对于我描述的场景，我是否应该使用 ROWLOCK 和 UPDLOCK 提示来阻止对行的访问?如果不是，正确的方法是什么?

我打算将我的选择和更新代码放在一个事务中.事务中的第一个选择查询将使用 ROWLOCK 和 UPDLOCK 提示.这将防止该记录被另一个使用具有相同锁的 select 来检索同一行的事务选择.

解决方案

我建议 SNAPSHOT 的 SQL 隔离级别.非常类似于 Oracle 的锁管理.

参见 http://www.databasejournal.com/features/mssql/snapshot-isolation-level-in-sql-server-what-why-and-how-part-1.html>

如果您的代码不太复杂，您可能无需任何更改即可实现.请记住，某些可见性可能会受到影响(即脏读可能不会给出脏数据)

我发现这个一揽子系统比到处使用查询提示更容易、更精确.

使用以下方法配置数据库:

SET ALLOW_SNAPSHOT_ISOLATION ON

然后使用它作为您的交易报表的前缀:

设置事务隔离级别快照

I am working on a project with 2 applications developed in C# (.NET framework 4) -

1. WCF service (exposed to customers)
2. ASP.NET webforms application (Head office use).

Both applications can select and update rows from a common "accounts" table in a SQL Server 2005 database. Each row in the accounts table holds a customer balance.

The business logic for a request in both applications involves selecting a row from "accounts" table, doing some processing based on the balance, followed by updating the balance in the database. The process between selecting and updating of the balance cannot participate in a transaction.

I realized it is possible between selecting the row and updating it, the row could be selected and updated by the another request from same or different application.

I found this issue described in the below article. I am referring to 2nd scenario of "lost update". http://www.codeproject.com/Articles/342248/Locks-and-Duration-of-Transactions-in-MS-SQL-Serve

The second scenario is when one transaction (Transaction A) reads a record and retrieve the value into a local variable and that same record will be updated by another transaction (Transaction B). And later Transaction A will update the record using the value in the local variable. In this scenario the update done by Transaction B can be considered as a "Lost Update".

I am looking for a way to prevent the above situation and to prevent balance from becoming negative if multiple concurrent requests are received for the same row. A row should be selected and updated by only a single request (from either application) at a time to ensure the balance is consistent.

I am thinking along the lines of blocking access to a row as soon as it has been selected by one request. Based on my research below are my observations.

1. Isolation levels

With 'Repeatable read' isolation level it is possible for 2 transactions to select a common row.

I tested this be opening 2 SSMS windows. In both windows I started a transaction with Repeatable read isolation level followed by select on a common row. I was able to select the row in each transaction.

Next I tried to update the same row from each transaction. The statements kept running for few seconds. Then the update from 1st transaction was successful while the update from 2nd transaction failed with the below message.

Error 1205 : Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

So if I am using transaction with Repeatable read it should not be possible for 2 concurrent transactions to update the same row. Sql server automatically chooses to rollback 1 transactions. Is this correct?

But I would also like to avoid the deadlock error by allowing a particular row to be selected by a single transaction only.

2. Rowlock

I found the below answer on Stackoverflow that mentioned use of ROWLOCK hint to prevent deadlock. (see the comment of the accepted answer).

Minimum transaction isolation level to avoid "Lost Updates"

I started a transaction and used a select statement with ROWLOCK and UPDLOCK. Then in a new SSMS window, I started another transaction and tried to use the same select query (with same locks). This time I was not able to select the row. The statement kept running in the new SSMS window.

So use of Rowlock with transactions seems to be blocking rows for select statements which use the same lock hints.

I would appreciate it if someone could answer the below questions.

1. Are my observations regarding isolation levels and rowlock correct?

2. For the scenario that I described should I use ROWLOCK and UPDLOCK hints to block access to a row? If not what is the correct approach?

I am planning to place my select and update code in a transaction. The first select query in the transaction will use the ROWLOCK and UPDLOCK hints. This will prevent the record from being selected by another transaction that uses select with the same locks to retrieve the same row.

解决方案

I would suggest SQL Isolation level of SNAPSHOT. Very similar to Oracles lock management.

See http://www.databasejournal.com/features/mssql/snapshot-isolation-level-in-sql-server-what-why-and-how-part-1.html

If your code is not too complicated, you can probably implement this without any changes. Bare in mind that some visibility may be affected (ie Dirty reads may not give dirty data)

I find this blanket system easier and more precise than using query hints all over the place.

Configure the database using:

SET ALLOW_SNAPSHOT_ISOLATION ON

Then use this to prefix your transaction statements:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

这篇关于SQL Server - 防止丢失更新和死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！