What kind of lock is placed for SELECT statement within a transaction in SQL Server

Question

I believe that each SELECT statement in SQL Server will cause either a Shared or Key lock to be placed. But will it place that same type of lock when it is in a transaction? Will Shared or Key locks allow other processes to read the same records?

For example I have the following logic

Begin Trans
-- select data that is needed for the next 2 statements
SELECT * FROM table1 where id = 1000; -- Assuming this returns 10, 20, 30

insert data that was read from the first query
INSERT INTO table2 (a,b,c) VALUES(10, 20, 30);

-- update table 3 with data found in the first query
UPDATE table3
SET d = 10,
   e = 20,
   f = 30;

COMMIT;

At this point will my select statement still create a shared or key lock or will it get escalated to exclusive lock? Will other transaction be able to read records from the table1 or will all transaction wait until the my transaction is committed before others are able to select from it?

In an application does it makes since to move the select statement outside of a transaction and just keep the insert/update in one transaction?

Burford

Answer 1

A SELECT will always place a shared lock - unless you use the WITH (NOLOCK) hint (then no lock will be placed), use a READ UNCOMMITTED transaction isolation level (same thing), or unless you specifically override it with query hints like WITH (XLOCK) or WITH (UPDLOCK).

A shared lock allows other reading processes to also acquire a shared lock and read the data - but they prevent exclusive locks (for insert, delete, update operations) from being acquired.

In this case, with just three rows selected, there will be no lock escalation (that only happens when more than 5000 locks are being acquired by a single transaction).

Depending on the transaction isolation level, those shared locks will be held for different amounts of times. With READ COMMITTED, the default level, the locks is released immediately after the data has been read, while with REPEATABLE READ or SERIALIZABLE levels, the locks will be held until the transaction is committed or rolled back.

Destructive

Thank you so much for this valuable information. so if SQL Server uses Read Committed by default for any select statement. There is not since of attempting to separate my SELECT queries outside the transaction – Burford

@MikeA: no, doesn't make any difference - the shared locks will be acquired just very briefly for actually reading the data (just to prevent another process from changing them while you're reading them), and then they'll be released again - inside or outside that transaction - same process – Destructive

In case you are not aware of this: READ COMMITTED selects do not always lock. Locks are only taken on pages with uncommitted changes. You can read a table under RC that has been XLOCK, TABLOCK'ed by another transaction. (I tried that.) – Chadwick

When using READ COMMITTED SNAPSHOT and SNAPSHOT isolation levels SELECT statements doesn't request shared locks. – Edlun