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?

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.