SQL Server 2012使用READPAST和ROWLOCK来锁定多进程环境中的行 [英] SQL Server 2012 using READPAST and ROWLOCK for locking rows in a multi process environment
问题描述
我有点茫然
这就是我想要做的事：
1.开立交易
2.读取1行并将其从进一步读取中锁定，但允许读取同一桌面上的其他记录
3.更新其上的值
4.做仍然保持交易打开时数据的一些东西
5.如果外行还可以：更新行上的另一个值
6.提交允许访问该行的事务为了所有
这是我到目前为止所获得的
1 。在c＃
中打开 SqlConnection 2.使用
3.在数据库上执行以下脚本
Im a bit at loss
This is what I want to do:
1. Open a transaction
2. Read 1 Row and locking it from further reads, but allowing reading other records on the same table
3. Update a value on it
4. Do some stuff with the data while still holding the transaction open
5. If everthing was ok: Update another value on the row
6. Commit the transaction allowing to access the row for all
This Is what i got so far
1. Open SqlConnection in c#
2. begin SqlTransaction with IsolationLevel.ReadCommited
3. Execute the following script on the database
UPDATE [MyTable] SET Counter=Counter+1
OUTPUT inserted.Id, inserted.Value
WHERE Id = (SELECT TOP 1 Id FROM [MyTable] WITH (READPAST, ROWLOCK)
WHERE Flag=0 AND Counter < 5 ORDER BY Id ASC)
4.从 OUTPUT 子句中读取值做一些东西
5.如果从做一些事情的一切都没问题：将值标志更新为1
6提交交易
基本上是数据库中的队列。
我的问题：如果两个进程同时执行此操作，则有大约1％的概率，即使事务和锁定，同一记录也会被读取和更新。 （我想select语句中的锁定会在select完成后立即释放？）
只有行被锁定以便多个才能非常重要实例可以同时访问该表。
如果尝试了以上SQL脚本的一些变体：
- 已添加<code>更新语句的WITH（ROWLOCK）子句（导致插入语句出现一些死锁）
- 已添加<code> XLOCK 指向select语句中的WITH子句（导致表锁定，另一方面没有错误。但是否定了readpast& rowlock提示）
如何在不阻止其他进程访问表的其他记录的情况下对read with语句进行锁定？
感谢您的帮助：）
4. read the values from the OUTPUT clause and do some stuff
5. If everything from doing some stuff was ok: Update the value Flag to 1
6. Commit the transaction
basically a queue on the database.
My Problem: if two processes are doing this at the same time there is a probability of around 1% that the same record gets read and updated despite the transaction and locks. (I suppose the lock in select statement get released as soon as the select is finished?)
It''s very important that only the rows get locked so that multiple instances can access the table at the same time.
If tried some variations of the SQL script above:
- Added WITH (ROWLOCK) clause to the update statement (did result in a few deadlocks for insert statements)
- Added XLOCK to the WITH clause in the select statement (resulted in Table lock, no erros on the other hand. However negates the readpast & rowlock hints)
How can I hold the lock on the read with statement without preventing other processes from accessing other records of the table?
Thank you for your help :)
推荐答案
发布解决方案以便整理。
我建议......调查UPDLOCK的使用 - se例如 http：/ /stackoverflow.com/questions/1921091/thread-safe-sql-transaction-how-to-lock-a-specific-row-during-a-transaction [ ^ ]
一个常见的看法似乎是将锁定保留给DBMS。我还看到了几个关于使用IsAvailableForUpdate标志（一种手动扩展锁定）的建议，这就是我认为你的[Flag]专栏正在尝试
OP确认...
Posting solution for tidy up.
I suggested ... Investigate the use of UPDLOCK - see example here http://stackoverflow.com/questions/1921091/thread-safe-sql-transaction-how-to-lock-a-specific-row-during-a-transaction[^]
A common opinion seems to be to leave locking to the DBMS though. I''ve also seen several sugggestions around using an IsAvailableForUpdate flag (a sort of manual extension to locking) which is what I thought your [Flag] column was attempting
OP confirmed ...
是的。经过大量的测试，这似乎可以解决问题。但我不得不重写查询（只有一个没有任何更新的select语句）所以基本上是一个SELECT WITH（UPDLOCK READPAST ROWLOCK）。
Yeah. After lot of testing this seems to do the trick. But I had to rewrite the query (Only a select statement without any update) so basically a SELECT WITH (UPDLOCK READPAST ROWLOCK).
这篇关于SQL Server 2012使用READPAST和ROWLOCK来锁定多进程环境中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
