CODEWITHSUNDEEP
sql-servert-sqltransactions
Kevin Dai
Kevin Dai

Reputation: 77

why exclusive lock of insert statement doesn't work

Suppose there are two transactions executed in sequence:

Transaction 1:

    BEGIN TRANSACTION 
         INSERT INTO my_table values(1, 'First')
    --not committed

Transaction 2:

    BEGIN TRANSACTION 
          INSERT INTO my_table values(1, 'First')
    COMMIT TRANSACTION

Why Transaction 2 allow to obtain the exclusive lock to complete query, instead of suspending?

Upvotes: 1

Views: 435

Answers (2)

Jitendra Singh Rathor
Jitendra Singh Rathor

Reputation: 353

Here if no of records being inserted are small then lock will be taken on row. But this number is huge then SQL server might escalate row level lock to table level lock.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453338

Because the exclusive lock in the example in your question will likely be taken on a row.

With only IX locks taken on higher objects in the hierarchy such as page and object.

This does not block a transaction inserting a different row as IX locks are not mutually exclusive.

With the following table example

CREATE TABLE my_table
  (
     X INT,
     Y VARCHAR(100)
  )

and using DBCC TRACEON(1200, -1, 3604) to see the locking info it returns

Process 57 acquiring IX lock on OBJECT: 7:48719226:0 (class bit2000000 ref1) result: OK

Process 57 acquiring IX lock on PAGE: 7:1:296 (class bit2000000 ref0) result: OK

Process 57 acquiring X lock on RID: 7:1:296:5 (class bit2000000 ref0) result: OK

If you were to create a table such as the following with row locks disabled (not recommended) you may well encounter blocking as the X lock is taken at page level instead.

CREATE TABLE my_table
  (
     X INT,
     Y VARCHAR(100)
  ) 

  CREATE CLUSTERED INDEX IX on my_table(X) with(ALLOW_ROW_LOCKS = OFF)

Upvotes: 2

Related Questions