Does a read apply locks when run outside of a transaction?

Question

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?

score 3 · Accepted Answer · edited Oct 08 '15 at 21:38

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

score 2 · Answer 2 · edited Apr 13 '17 at 12:42

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

edited Apr 13 '17 at 12:42

Community

1

answered Oct 08 '15 at 21:28

Hannah Vernon

70,928
22
177
323

Does a read apply locks when run outside of a transaction?

2 Answers2