Thursday, September 12, 2013

SQL Server Table Lock during bulk insert

SQL Server Table Lock during bulk insert

Below is the sample query, consider A
INSERT INTO Target (Col1,Col2,Col3,Col4) ----------------Statement#1
Select A.Col1,B.Col2,A.Col3,C.Col4 ----------------Statement#2
FROM A WITH(NOLOCK) INNER JOIN B WITH(NOLOCK)
ON A.Id = B.ID
LEFT JOIN C WITH NOLOCK
ON C.Id = B.ID
Where A.Id = 11
At which stage the lock will be applied on table [exclusive lock?], how
SQL is going to execute the query?
Result will be fetched from table A, B and C based on join and where clause.
On ready result, start inserting data in table and at same time apply the
lock on table.
So when actual data is written on the page table is locked but not during
select even though it is INSERT INTO with SELECT?

No comments:

Post a Comment