Locking and Concurrency - SQL Server 2012 Internals and Troubleshooting - page 171

FIGURE 6-13
The transaction continues through to update a single row. This triggers the need to convert the
table-level shared lock to a SIX lock as the row must be exclusively locked. Figure 6-14 clearly shows
that the row is locked with an exclusive KEY lock, but also that the table/object has converted its
lock from shared (S) to shared with intent exclusive (SIX).
FIGURE 6-14
Bulk Update Lock Mode (BU)
Bulk Update i rst appeared in SQL Server 2005. It is designed to allow multiple table-level locks on
a single heap while using the Bulk API. This is important for parallel loading in data warehousing.
However, in order to see it, you need to be loading into a heap and you must have specii ed a
Tablock on the target table. The Tablock is a hint to say you'll take a table lock, but SQL Server
sees that the Bulk API is making the assertion. So a BU lock is issued instead. Because multiple BU
locks are permitted on the same table, you are therefore empowered to perform parallel loading
into the heap, as each loader will take its own compatible BU lock. Note that dirty reads are also
permitted against the target table.
NOTE If you do not specify a Tablock hint when bulk loading data into a heap,
then you will see exclusive page locks instead. If the target table has a clustered
index, then use trace l ag 610 and you will also see page locks on the bulk insert.
See the Data Loading Performance Guide ( http://msdn.microsoft.com/en-us/
library/dd425070.aspx ) from the SQLCAT team for further details.
The following SQL code example is using the BULK INSERT statement to load into a replica heap
of the dbo.factinternetsales table. Notice that a Tablock hint has also been used. You can see
the BU lock that is issued as a result in Figure 6-15 by querying sys.dm_tran_locks in a separate
session as before. You'll have to be quick though! The sample data i les contain only 60,398 rows
(code i le Ch6BulkUpdateLockMode.sql ).