1

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?

3

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? – ajbeaven Oct 8 '15 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). – Amit Banerjee Oct 8 '15 at 23:10
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.