CODEWITHSUNDEEP
sqlsql-serverdatabaselocking
microwth
microwth

Reputation: 1066

Can an UPDATE of a Transaction T1 run at the same time with T2 that does a SELECT get to those rows before the Select does?

Let's say we have a table with many rows and a primary key (index).

T1 will do a SELECT that would search for some rows using the WHERE clause, locking them with Shared locks. At the same time, T2 will do an update on a row that falls into the range of T1's requested rows.

The question is, can the Update get to those rows before he Select does?

How is the engine locking rows when Selecting, one-by-one ,like : read this row,lock it, now move to the next, etc. In this case the Update might get to the rows before the Select gets them? and what if no index was used but a table scan instead?

The Update statement has a Select component too. How does the Update actually lock a row?

One by one, first reads it, then locks it with X, next one etc. In this scenario the Select could get to the rows before the Update does?

And is the Select part of the Update affected by the isolation level?

The question is targeted on traditional ANSI isolation systems and not Oracle/MVCC

Upvotes: 1

Views: 122

Answers (1)

Szymon
Szymon

Reputation: 43023

There's quite a few questions here but I'll try to address some of them.

Both SELECT and UPDATE will lock as they go through the index or records in the table. Records already locked by SELECT will not be available for UPDATE and the other way round. This may even cause a deadlock, depending on the order of those operations (which is beyond your control).

If you need to update before select, you need to control it from your code level. If you start both at once, SQL Server will just start executing them and locking.

SELECT is affected by the isolation level, e.g. when your isolation level will be read uncommitted, select will read the data and not put any locks.

Upvotes: 1

Related Questions