SQL 事务超时 [英] SQL Transaction Times Out

问题描述

当我在管理工作室中执行以下 SQL 时，查询返回预期结果:

When I execute the following SQL in the management studio the query returns the expected result:

SELECT MAX(ID) FROM table WHERE field = value;

但是，当我的客户端应用程序 (WinForms) 在事务中间执行此 SQL 时，查询超时而不返回值.我怀疑这是因为事务包括添加/修改同一个表"的其他 SQL 语句.

However when my client application (WinForms) execute's this SQL in the middle of a Transaction the query Times Out without returning a value. I suspect this is because the Transaction includes other SQL statements which are adding to / amending the same 'table'.

我该如何解决这个问题?

How do I work around this?

推荐答案

"SELECT MAX(ID)" 是一个聚合函数，会在表上做很多锁——共享读锁可能会被所有的表中的记录，当然是所有符合您条件的记录，还有其他记录，取决于使用的是行锁还是页锁.如果在 MAX 想要锁定的这些记录中的任何 上存在未完成的写锁定，则 MAX 将超时​​.您需要同时了解其他锁.如果您使用的是 SQL Server，则可以使用 SQL Profiler 获取跟踪，该跟踪将显示该表上的所有锁，包括来自其他事务的锁.

"SELECT MAX(ID)" is an aggregate function and will do a lot of locking on the table - a shared read lock can potentially be taken on all the records in the table, certainly on all those that match your criteria, and others depending on whether row or page locks are used. If there is an outstanding write lock on any of these records that the MAX wants to lock, then the MAX will time out. You need to understand what other locks are in place at the same time. If you are using SQL Server, then you can use SQL Profiler to get a trace which will show you all the locks on that table, including locks from other transactions.

这篇关于SQL 事务超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！