3

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?

  1. Result will be fetched from table A, B and C based on join and where clause.
  2. 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?

Hi10
  • 531
  • 1
  • 5
  • 21

2 Answers2

2

Those two steps are the logical steps for query execution. What SQL Server can do/do at physical level is another story. At this moment:

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

for every output record (see SELECT clause) it takes an X lock on a RID or a KEY within target table (RID for heap / KEY for clustered index) and it inserts that record. This steps are repeated for every output record. So, it doesn't read all records from source tables and only after this step it starts inserting records into target table. Because of NOLOCK table hint on source table it will takes only Sch-S (schema stability) locks on these tables.

If you want to take an X lock on target table then you could use

INSERT INTO Target WITH(TABLOCKX) (Col1,Col2,Col3,Col4)
SELECT ...

If you want minimally logged inserts then please read this article.

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
  • Ok, its row by row fetch and insert? Thats what you mean to say? Also what will be the duration of lock on target table, if Select query take 500ms to get the result? – Hi10 Sep 13 '13 at 08:33
  • 1 & 2) Basically, yes. 3) From the moment when X lock is acquired till the end of transaction (if you have an [explicit transaction](http://technet.microsoft.com/en-us/library/ms175127(v=sql.105).aspx): COMMIT/ROLLBACK) or till the end of statement execution ([auto-commit transaction](http://technet.microsoft.com/en-us/library/ms187878(v=sql.105).aspx)). Without `TABLOCKX` the X lock (at record level: RID/KEY) is acquired just before the insertion of every record into target table. With `TABLOCKX` hint the X lock is acquired (at table level) once at the beginning of statement execution. – Bogdan Sahlean Sep 13 '13 at 08:43
  • 1
    Appreciating your answer. – Hi10 Sep 13 '13 at 08:47
0

Did you specify any "Table Lock" hint. If you want to Row-level lock Set "Table Lock" to off.

or check this it will help you...

http://technet.microsoft.com/en-us/library/ms180876(v=sql.105).aspx

Aurangzeb Halepoto
  • 117
  • 7
  • No I have not specified any, my question here is what time lock will be put as soon as insert statement is detected or post select when actual insert is happening? – Hi10 Sep 13 '13 at 06:28