If implicit_transactions = off and I execute a SELECT query outside of an explicit transaction, will any locks be applied or do locks only make sense as part of a transaction?
Add a comment
|
2 Answers
The way SQL Server handles locks is based on the isolation level that you are using and the state of the transaction. The default isolation level for SQL Server is READ COMMITTED. So if you perform a SELECT command without an explicit transaction, then the dynamic locking will decide the lock granularity.
For READ COMMITTED isolation level, the SHARED locks acquired on the index keys/RID (row identifier) will be released after the scan/seek has completed.
Refer to Locking in SQL Server and Isolation Levels
-
and the state of the transaction... so if there is no transaction?– ajbeavenCommented Oct 8, 2015 at 23:04
-
A SELECT is not a logged operation but you will have an execution context associated with the operation to track the locks and other operations. A user transaction only happens when you perform a modification in the database (DML/DDL). Commented Oct 8, 2015 at 23:10
Locks are ALWAYS taken to some degree or another, even when using WITH (NOLOCK).
Remus Rusanu has an excellent answer detailing how read locks (and others) are taken, here
answered Oct 8, 2015 at 21:28
