SQL Server - Does [Select] Lock [Update]

SQL Server - does [SELECT] lock [UPDATE]?

Yes - to a degree.

How long a SELECT holds on to a shared lock is depending on the isolation level of the transaction:

• READ UNCOMMITTED - no shared lock is acquired at all - UPDATE is not blocked
• READ COMMITTED - shared lock is acquired just for the duration of reading the data - UPDATE might be blocked for a very short period of time
• REPEATABLE READ and SERIALIZABLE - shared lock is acquired and held on to until the end of the transaction - UPDATE is blocked until the SELECT transaction ends

Technically, the UPDATE statement first gets an UPDATE lock - which is compatible with a shared lock (as used by the SELECT) - for the duration of the time while it's reading the current values of the rows to be updated.

Once that's done, the Update lock is escalated to an exclusive lock for the new data to be written to the table.

Why is shared lock allowing updates in SQL server?

Your shared lock is immediately released after the select gets executed even it is inside the transaction.

If you have an intention to update the record after select, in that case you need to acquire a UPDLOCK lock like following.

DECLARE @IdToUpdate INT
SELECT @IdToUpdate = ID from tblCustomer WITH (UPDLOCK) where CustomerId=1  
UPDATE [tblCustomer]
SET X=Y
WHERE ID=@IdToUpdate

This will take the necessary Update lock on the record in advance and will stop other sessions to acquire any lock (shared/exclusive) on the record

Understanding SQL Server LOCKS on SELECT queries

A SELECT in SQL Server will place a shared lock on a table row - and a second SELECT would also require a shared lock, and those are compatible with one another.

So no - one SELECT cannot block another SELECT.

What the WITH (NOLOCK) query hint is used for is to be able to read data that's in the process of being inserted (by another connection) and that hasn't been committed yet.

Without that query hint, a SELECT might be blocked reading a table by an ongoing INSERT (or UPDATE) statement that places an exclusive lock on rows (or possibly a whole table), until that operation's transaction has been committed (or rolled back).

Problem of the WITH (NOLOCK) hint is: you might be reading data rows that aren't going to be inserted at all, in the end (if the INSERT transaction is rolled back) - so your e.g. report might show data that's never really been committed to the database.

There's another query hint that might be useful - WITH (READPAST). This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively. The SELECT will not block, and it will not read any "dirty" un-committed data - but it might skip some rows, e.g. not show all your rows in the table.

Does SELECT FOR UPDATE prevent other connections inserting when the row is not present?

In Oracle, the SELECT ... FOR UPDATE has no effect on a non-existent row (the statement simply raises a No Data Found exception). The INSERT statement will prevent a duplicates of unique/primary key values. Any other transactions attempting to insert the same key values will block until the first transaction commits (at which time the blocked transaction will get a duplicate key error) or rolls back (at which time the blocked transaction continues).

SELECT FOR UPDATE with SQL Server

Recently I had a deadlock problem because Sql Server locks more then necessary (page). You can't really do anything against it. Now we are catching deadlock exceptions... and I wish I had Oracle instead.

Edit:
We are using snapshot isolation meanwhile, which solves many, but not all of the problems. Unfortunately, to be able to use snapshot isolation it must be allowed by the database server, which may cause unnecessary problems at customers site. Now we are not only catching deadlock exceptions (which still can occur, of course) but also snapshot concurrency problems to repeat transactions from background processes (which cannot be repeated by the user). But this still performs much better than before.

Row locking behaviour while updating

With Read Committed Snapshot Isolation (RCSI), versions of rows are stored in a version store, so readers can read a version of a row that existed at the time the statement started and before any changes have been made; while a transaction is open; without taking shared locks on rows or pages; and without blocking writers or other readers. From this post by Paul White:

To summarize, locking read committed sees each row as it was at the time it was briefly locked and physically read; RCSI sees all rows as they were at the time the statement began. Both implementations are guaranteed to never see uncommitted data,

One cost, of course, is that if you read a prior version of the row, it can change (even many times) before you're done doing whatever it is you plan to do with it. If you're making important decisions based on some past version of the row, it may be the case that you actually want an isolation level that forces you to wait until all changes have been committed.

Another cost is that version store is not free... it requires space and I/O in tempdb, so if tempdb is already a bottleneck on your system, this is something worth testing.

(In SQL Server 2019, with Accelerated Database Recovery, the version store shifts to the user database, which increases database size but mitigates some of the tempdb contention.)

Paul's post goes on to explain some other risks and caveats.

In almost all cases, this is still way better than NOLOCK, IMHO. Lots of links about the dangers there (and why RCSI is better) here:

• I'm using NOLOCK; is that bad?

And finally, from the documentation (adding one clarification from the comments):

When the READ_COMMITTED_SNAPSHOT database option is set ON, read committed isolation uses row versioning to provide statement-level read consistency. Read operations require only SCH-S table level locks and no page or row locks. That is, the SQL Server Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions. A user-defined function can return data that was committed after the time the statement containing the UDF began.

When the READ_COMMITTED_SNAPSHOT database option is set OFF, which is the default setting * on-prem but not in Azure SQL Database *, read committed isolation uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. Both implementations meet the ISO definition of read committed isolation.

---

Related Topics