2

I thought I understood the locking mechanism from SQL, but it seems that I do not. I do know that you can have shared and exclusive locks and that 2 shared locks can be applied at the same data at the same time. But can someone explain me why query 1 doesn't block query 2. I expect that the serializable keyword will give me a exclusive lock instead of a shared lock. To get concrete:

I created a user table:

  • id [int]
  • name [nvarchar50)

Than I opened sql management studio connected to that database with user1 and execute the following query:

BEGIN TRAN
  select * from [user] WITH(SERIALIZABLE, TABLOCK, HOLDLOCK)

I keep the transaction open on purpose.

Than I open a second sql mangement studio connect to the same database with user2 and execute the same query. I Expected that 2nd query keeps running, since query 1 is remaining a lock on the table, but it doesnt. The 2nd query is executed directly. Why is this? Are my lock hints ignored?

Maarten Kieft
  • 6,806
  • 3
  • 29
  • 34
  • 3
    This thread has your answer: https://stackoverflow.com/questions/12608780/understanding-sql-server-locks-on-select-queries – Rigerta Nov 07 '17 at 10:35
  • @YogeshSharma: Added the tags. – Maarten Kieft Nov 07 '17 at 10:40
  • @Rigerta: Well not really, I understand the answer in that thread fully and isn't new to me. My point is that I force a exclusive lock with my read query but it seems that it is not working. – Maarten Kieft Nov 07 '17 at 10:41
  • 1
    You can check your query's locks by querying `sys.dm_tran_locks` while you are running the select. You will only be getting a `S` (shared) lock, not an exclusive one. To force it to use an IX lock you will need to use the following: `WITH (XLOCK, ROWLOCK)` – Rigerta Nov 07 '17 at 10:44
  • @RigertaDemiri: Thanks! That was it. You are right, so I was passing the wrong hints as it seems? What does the serializeable hint do? I thought it will require a Exclusive lock until the current transaction is ended. p.s.: Mayby convert your comment into a answer so I can mark it? – Maarten Kieft Nov 07 '17 at 10:51
  • 1
    Ah misunderstood serializeable is more about the duration of the lock instead of the locking type: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table – Maarten Kieft Nov 07 '17 at 10:55
  • Possible duplicate of [Understanding SQL Server LOCKS on SELECT queries](https://stackoverflow.com/questions/12608780/understanding-sql-server-locks-on-select-queries) – Tanner Nov 07 '17 at 10:57

2 Answers2

2

First, it is very well explained on this thread that a SELECT statement cannot block another one, since both only acquire a Shared (S) lock on the resource, which means the row is available for reading. Read more here on all lock types.

However, you were trying to force a select statement into acquiring an Exclusive (X) lock by using WITH(SERIALIZABLE, TABLOCK, HOLDLOCK) hints.

Those hints "apply only to the table or view you are using it on and only for the duration of the transaction defined by the statement that they are used in". This is stated in the documentation.

The SERIALIZABLE hint is making shared locks more restrictive by holding them until a transaction is completed, instead of releasing the lock as soon as the data is no longer needed. But keep in mind, it is still a shared lock.

In order to force an exclusive lock you could do the following:

SELECT * 
FROM YourTable WITH (XLOCK, ROWLOCK)

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

The second query will give you information on all acquired locks from this statement. I got the following results, by selecting on some table:

enter image description here

Note that we got an Intent shared (IX) lock on the PAGE and on the OBJECT itself, which is the table we are selecting from and an Exclusive (X) lock on every key.

Rigerta
  • 3,959
  • 15
  • 26
1
  1. SERIALIZABLE = HOLDLOCK Makes shared locks
  2. TABLOCK Specifies that the acquired lock is applied at the table level.

(SERIALIZABLE, TABLOCK) = Shared (S) lock on Table

If need Exclusive (X) lock use on TABLOCKX or (TABLOCK,UPDLOCK) or (TABLOCK,XLOCK)

Stanislav Kundii
  • 2,874
  • 1
  • 11
  • 17