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

Question

I believe that every SELECT in SQL Server will cause a Shared or Key lock. But will it include the 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 create a shared or blocking key, or will it switch to exclusive lock? Will another transaction be able to read records from table1, or will all transactions wait until my transaction is completed before others can select it?

This does this in the application, so how do I move the select statement outside of the transaction and just keep the insert / update in one transaction?

Answer 1

A SELECT will always place a common lock - if you do not use the WITH (NOLOCK) hint (then the lock will not be placed), use the READ UNCOMMITTED transaction isolation level (the same) or if you do not specifically redefine it with WITH (XLOCK) or WITH (UPDLOCK) .

A public lock allows other read processes to also obtain a common lock and read data - but they do not allow for exclusive locks (for insert, delete, update operations).

In this case, no lock will exist with the three selected rows (this only happens when one transaction receives more than 5000 locks).

Depending on the level of transaction isolation, these common locks will be stored a different number of times. With READ COMMITTED , the default level, locks are released immediately after reading data, and with REPEATABLE READ or SERIALIZABLE locks are held until the transaction is completed or rolled back.