Sample Header Ad - 728x90

What type of locks does BEGIN TRANSACTION cause against a specific table that's being read from?

2 votes
2 answers
1218 views
In the following example, what kinds of locks are generated from the explicit transaction block that wraps the SELECT INTO query? BEGIN TRANSACTION T1 SELECT Field1, Field2, Field3 INTO TableB FROM TableA WHERE Field3 > Value1 COMMIT TRANSACTION T1 Notes: 1) TableA has about 100 billion rows, but the filter causes the query to return only about 5 million rows. 2) Field3 is a clustered index on TableA, and the generated query plan is doing a clustered index seek. 3) The isolation level in my scenario is the default (READ COMMITTED). Do explicit transaction blocks like this potentially cause more locking (or a higher chance of lock escalation) against the table then if the query wasn't wrapped in an explicit transaction?
Asked by J.D. (40893 rep)
Dec 5, 2019, 10:14 PM
Last activity: Jul 21, 2022, 12:29 PM