We are using ADO.NET to connect to a SQL 2005 server, and doing a number of inserts/updates and selects in it. We changed one of the updates to be inside a transaction however it appears to (b)lock the entire table when we do it, regardless of the IsolationLevel we set on the transaction.
我们使用ADO.NET连接到SQL 2005服务器,并在其中进行大量插入/更新和选择。我们将其中一个更新更改为在事务中,但它似乎(b)在执行时锁定整个表,而不管我们在事务上设置的IsolationLevel。
The behavior that I seem to see is that:
我似乎看到的行为是:
- If you have no transactions then it's an all out fight (losers getting dead locked)
- If you have a few transactions then they win all the time and block all others out unless
- If you have a few transactions and you set something like nolock on the rest then you get transactions and nothing blocked. This is because every statement (select/insert/delete/update) has an isolationlevel regardless of transactions.
如果你没有交易那么这是一场全力以赴的战斗(失败者被锁死)
如果你有一些交易,那么他们会一直赢,并阻止所有其他交易,除非
如果您有一些交易,并且您在其余部分设置了类似nolock的内容,那么您将获得交易而不会被阻止。这是因为无论事务如何,每个语句(select / insert / delete / update)都有一个隔离级别。
Is this correct?
它是否正确?
2 个解决方案
#1
The answer to your question is: It depends.
你的问题的答案是:这取决于。
If you are updating a table, SQL Server uses several strategies to decide how many rows to lock, row level locks, page locks or full table locks.
如果要更新表,SQL Server使用多种策略来确定要锁定的行数,行级锁,页锁或完整表锁。
If you are updating more than a certain percentage of the table (configurable as I remember), then SQL Server gives you a table level lock, which may block selects.
如果您正在更新超过表的某个百分比(我记得可配置),那么SQL Server会为您提供表级锁定,这可能会阻止选择。
The best reference is:
最好的参考是:
- Understanding Locking in SQL Server: http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx (for SQL Server 2000)
- Introduction to Locking in SQL Server: http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
- Isolation Levels in the Database Engine: http://msdn.microsoft.com/en-us/library/ms189122.aspx (for SQL server 2008, but 2005 version is available).
了解SQL Server中的锁定:http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx(适用于SQL Server 2000)
SQL Server中的锁定简介:http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
数据库引擎中的隔离级别:http://msdn.microsoft.com/en-us/library/ms189122.aspx(适用于SQL Server 2008,但2005版本可用)。
Good luck.
#2
Your update statement (i.e one that changes data) will hold locks regardless of the isolation level and whether you have explicitly defined a transaction of not.
您的更新语句(即更改数据的语句)将保持锁定,无论隔离级别如何,以及您是否明确定义了不是的事务。
What you can control is the granularity of the locks by using query hints. So if the update is locking the entire table, then you can specify a query hint to only lock the affected rows (ROWLOCK hint). That is unless your query is updating the whole table of course.
您可以通过使用查询提示来控制锁的粒度。因此,如果更新锁定整个表,则可以指定查询提示以仅锁定受影响的行(ROWLOCK提示)。这是除非您的查询正在更新整个表格。
So to answer your question, the first connection to request locks on a resource will hold those locks for the duration of the transaction. You can specify that a select does not hold locks by using the read uncommitted isolation level, statements that change data insert/update/delete always hold locks regardless. The next connection to request locks on the same resource will wait until the first has finished and will then hold its locks. Dead locking is a specific scenario where two connections are holding locks and each is waiting for the other connection's resource, to avoid the engine waiting forever, one connection is chosen as the deadlock victim.
因此,要回答您的问题,请求资源锁定的第一个连接将在事务持续期间保留这些锁定。您可以通过使用读取未提交的隔离级别来指定选择不保存锁定,更改数据的语句插入/更新/删除始终保持锁定。请求锁定在同一资源上的下一个连接将等到第一个完成后再保持其锁定。死锁是一种特定情况,其中两个连接持有锁,每个连接都在等待另一个连接的资源,以避免引擎永远等待,选择一个连接作为死锁牺牲品。
#1
The answer to your question is: It depends.
你的问题的答案是:这取决于。
If you are updating a table, SQL Server uses several strategies to decide how many rows to lock, row level locks, page locks or full table locks.
如果要更新表,SQL Server使用多种策略来确定要锁定的行数,行级锁,页锁或完整表锁。
If you are updating more than a certain percentage of the table (configurable as I remember), then SQL Server gives you a table level lock, which may block selects.
如果您正在更新超过表的某个百分比(我记得可配置),那么SQL Server会为您提供表级锁定,这可能会阻止选择。
The best reference is:
最好的参考是:
- Understanding Locking in SQL Server: http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx (for SQL Server 2000)
- Introduction to Locking in SQL Server: http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
- Isolation Levels in the Database Engine: http://msdn.microsoft.com/en-us/library/ms189122.aspx (for SQL server 2008, but 2005 version is available).
了解SQL Server中的锁定:http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx(适用于SQL Server 2000)
SQL Server中的锁定简介:http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
数据库引擎中的隔离级别:http://msdn.microsoft.com/en-us/library/ms189122.aspx(适用于SQL Server 2008,但2005版本可用)。
Good luck.
#2
Your update statement (i.e one that changes data) will hold locks regardless of the isolation level and whether you have explicitly defined a transaction of not.
您的更新语句(即更改数据的语句)将保持锁定,无论隔离级别如何,以及您是否明确定义了不是的事务。
What you can control is the granularity of the locks by using query hints. So if the update is locking the entire table, then you can specify a query hint to only lock the affected rows (ROWLOCK hint). That is unless your query is updating the whole table of course.
您可以通过使用查询提示来控制锁的粒度。因此,如果更新锁定整个表,则可以指定查询提示以仅锁定受影响的行(ROWLOCK提示)。这是除非您的查询正在更新整个表格。
So to answer your question, the first connection to request locks on a resource will hold those locks for the duration of the transaction. You can specify that a select does not hold locks by using the read uncommitted isolation level, statements that change data insert/update/delete always hold locks regardless. The next connection to request locks on the same resource will wait until the first has finished and will then hold its locks. Dead locking is a specific scenario where two connections are holding locks and each is waiting for the other connection's resource, to avoid the engine waiting forever, one connection is chosen as the deadlock victim.
因此,要回答您的问题,请求资源锁定的第一个连接将在事务持续期间保留这些锁定。您可以通过使用读取未提交的隔离级别来指定选择不保存锁定,更改数据的语句插入/更新/删除始终保持锁定。请求锁定在同一资源上的下一个连接将等到第一个完成后再保持其锁定。死锁是一种特定情况,其中两个连接持有锁,每个连接都在等待另一个连接的资源,以避免引擎永远等待,选择一个连接作为死锁牺牲品。