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
Last activity: Jul 21, 2022, 12:29 PM