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

Question

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?

Answer 1

In this specific example, because it includes a SELECT...INTO statement, I think it's fair to say that yes, there is a difference in the locking behavior.

Source Table (and metadata tables)

The SELECT...INTO statement is not an "atomic statement," it runs in two parts: creating the new table, and then performing the SELECT and INSERT portion of the query.

At a minimum, the version with the explicit transaction will hold all the metadata locks related to creating the target table, as well as table-level locks on the target table itself, until the end of the transaction.

Here's a brief excerpt from EXEC master.dbo.sp_WhoIsActive @get_locks = 1; after running the version of this query with the explicit transaction:

    <Object name="sysrowsets" schema_name="sys">
      <Locks>
        <Lock resource_type="KEY" index_name="clust" request_mode="X" request_status="GRANT" request_count="2" />
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="sysrscols" schema_name="sys">
      <Locks>
        <Lock resource_type="KEY" index_name="clst" request_mode="X" request_status="GRANT" request_count="6" />
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="sysschobjs" schema_name="sys">
      <Locks>
        <Lock resource_type="KEY" index_name="clst" request_mode="X" request_status="GRANT" request_count="2" />
        <Lock resource_type="KEY" index_name="nc1" request_mode="X" request_status="GRANT" request_count="1" />
        <Lock resource_type="KEY" index_name="nc2" request_mode="X" request_status="GRANT" request_count="1" />
        <Lock resource_type="KEY" index_name="nc3" request_mode="X" request_status="GRANT" request_count="2" />
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="TableB" schema_name="dbo">
      <Locks>
        <Lock resource_type="ALLOCATION_UNIT.BULK_OPERATION_PAGE" request_mode="S" request_status="GRANT" request_count="1" />
        <Lock resource_type="HOBT" request_mode="Sch-M" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" request_mode="X" request_status="GRANT" request_count="55" />
      </Locks>
    </Object>

This could result in metadata-related blocking (if other sessions are creating objects), and of course only the creating session will have access to the target table.

---

Destination Table

This portion is more likely directly related to your question.

In addition to those differences, I've seen evidence that adding an explicit transaction can cause other locking behavior to change when using SELECT...INTO - particularly when the source "table" is a streaming UDF, but it might be true in other scenarios as well.

The gist of the behavior I noticed was that SELECT...INTO with an explicit transaction resulted in much higher occurrences of intra-query parallelism deadlocks. I don't have an explanation for this, but it's likely that locking (in the source and destination) is different when the explicit transaction is present. And the only advice I could really offer is to add an OPTION (MAXDOP 1) hint to avoid parallelism altogether if these deadlocks become a problem.

I talked about this oddity on my blog, and also reported a bug to Microsoft about it.

Answer 2

5 million rows is a lot, theoretically it would cause shared lock escalation for TableA to table-level lock in any case (explicit or implicit transaction)

But you can find this out experimentally, you just need to setup Extended Events session and monitor for "lock_escalation" event

Then run your query in explicit and implicit transaction and check out XE session logs to see the lock_escalation events