Default lock settings SQL Server 2008 R2
I was looking for an answer to this, but I wasn't able to find the exact answer to my question.
The problem is that I'm actually having some SQL issues due to transactions lock at my database. I want to know, by default, does a SELECT statement lock a table? I found that if I use table hints such as HOLDLOCK or TABLOCK the select would lock the table untill it's completion, but when no table hint is specified what is the behavior of SQL? Joins would affect it's behavior?
And another question, picture the following scenario: I start a connection and transaction at my .NET application and bind them to a command, which runs a stored procedure containing only a SELECT statement. Will the select lock the table due to the transaction or not?
Thanks for the attention.
Best regards.
I want to know, by default, does a SELECT statement lock a table?
Yes. All transactions create locks. Not all locks are blocking (shared locks usually are not for example) but all transactions [that interact with the database] create locks. Locks are how the query engine keeps track of what it's writing or reading.
Even if you specify NOLOCK or READUNCOMMITTED (which you generally don't want to do) you'll still get locks because the data needs to be consistent even if it's dirty, and the locks ensure that it is. That is to say, even if it returns uncommitted records (dirty), there are no records that are incomplete or partially changed (inconsistent).
I found that if I use table hints such as HOLDLOCK or TABLOCK the select would lock the table [until] it's completion, but when no table hint is specified what is the behavior of SQL?
There are no default lock hints. The query engine determines what it needs dynamically. SELECT will tend towards Shared or RangeS key-range depending on the query. INSERT/UPDATE/DELETE will tend towards exclusive locks. The system will generally favor the lowest granularity, row locks, but will escalate them to page locks or table locks if it thinks it should. Note: they're called "lock hints" and not "lock commands" because the query engine will ignore them if it has to.
The closest thing you'll find to default lock hints is the transaction isolation level, which generally controls how queries in a transaction function and defaults to READ COMMITTED.
Dynamic locking, lock granularity, lock escalation, and the lock hierarchy are all explained in the MS doc I already linked in the comment.
There's a huge amount of nuance and complexity to the locking system. The same query on a different system might get different locks because the table sizes were different or memory pressures were different.
You can also modify the locking system significantly with ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT database options, but I would not make those changes lightly. It does make the database perform somewhat more like Oracle, however.
Joins would affect it's behavior?
Very much so. To do a join, the query engine typically has to either scan indexes or the actual tables. Indexes are subject to locks just like tables are, so I'd expect Shared or RangeS-S locks to appear on objects in the database.
And another question, picture the following scenario: I start a connection and transaction at my .NET application and bind them to a command, which runs a stored procedure containing only a SELECT statement. Will the select lock the table due to the transaction or not?
Yes.
If you're using System.Data.SqlClient.SqlTransaction, the default isolation level is ReadCommitted, which (unsurspisingly) maps to the READ COMMITTED isolation level. If you're using System.Transactions, however, then you will default to Serializable, which maps to SERIALIZEABLE (the strictest isolation level).
链接地址: http://www.djcxy.com/p/5624.html上一篇: UPDLOCK提示查询期间发生死锁