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:
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?
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:
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.