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.