在 C# 中使用 Updlock 和 Readpast
我希望同时读取和更新数据库中的记录。此时，我已经设置了一个示例程序，可以从数据库中获取具有特定状态的记录。
我的目标是在执行选择命令时锁定行。此代码块执行时不会出现任何错误，但行在事务的生命周期内不会锁定。这些提示是否受支持或者有什么方法可以做到这一点？
这是 Visual Studio 代码。我稍后会添加更新命令。
SqlDataReader data;
DataTable table = new DataTable();
string query = string.Format(@"select * from [{0}] with (updlock,readpast) where [status] = '{1}'","table","abc");
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
SqlCommand cmd = new SqlCommand(query, conn, trans);
data = cmd.ExecuteReader();
table.Load(data);
System.Threading.Thread.Sleep(15000); // to test locking
}
这是我在 SQL Server 中测试的原始事务。这很好用。
BEGIN TRAN TEST
select * from table with (updlock, readpast) where status = 'abc';
WAITFOR DELAY '00:00:15'
COMMIT
I am looking to concurrently read and update records from a database. At this point, I have a sample program set up that grabs records from a database with a certain status.
I am aiming for the rows to lock when the select command executes. This code block executes without any errors, however the rows do not lock during the lifespan of the transaction. Are these hints supported or is there a way that this can be done?
This is the Visual Studio code. I will be adding update commands at a later time.
SqlDataReader data;
DataTable table = new DataTable();
string query = string.Format(@"select * from [{0}] with (updlock,readpast) where [status] = '{1}'","table","abc");
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
SqlCommand cmd = new SqlCommand(query, conn, trans);
data = cmd.ExecuteReader();
table.Load(data);
System.Threading.Thread.Sleep(15000); // to test locking
}
This is the original transaction I tested with in SQL Server. This works just fine.
BEGIN TRAN TEST
select * from table with (updlock, readpast) where status = 'abc';
WAITFOR DELAY '00:00:15'
COMMIT
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
测试时，我使用 SQL Server 并运行 select 语句来检查是否可以读取锁定的行。我忽略了向选择查询添加 UPDLOCK 和 READPAST 提示。这段代码按照预期的方式工作。
我最终运行了 C# 事务，等待时间为 30 秒才完成。在这 30 秒内，我在 SQL Server 中运行这些命令来测试行是否被锁定；
这些行确实锁定并且无法更新。但是，仍然可以使用 select 语句读取它们而不需要提示。这对于我正在升级的系统来说应该没问题。
When testing this, I was using SQL server and running a select statement to check whether I could read the locked rows. I neglected to add UPDLOCK and READPAST hints to the select query. This code works how it is supposed to.
I ended up running the C# transaction with a wait time of 30 seconds before completion. During these 30 seconds, I ran these commands in SQL Server to test that the rows locked;
The rows do lock and cannot be updated. They can, however, still be read using a select statement without hints. This should be fine for the system I am upgrading.