Weird locking issue
-
Hello everyone,
We have an unexplainable problem concernaing locking.
Here is what we can see using Enterprise Manager:
Process ID Lock Type Mode Satus Owner Index Resource
---------- ---------- ----- ------ ------ ------ ----------
112 KEY U GRANT Xact idx1 (fd0066dd24ee)
112 PAG IU GRANT Xact idx1 1:215772
112 TAB IX GRANT Xact
117 KEY U WAIT Xact idx1 (fd0066dd24ee)
117 PAG IU GRANT Xact idx1 1:215772
117 TAB IX GRANT Xact
87 KEY S WAIT Xact idx1 (fd0066dd24ee)
87 PAG IS GRANT Xact idx1 1:215772
87 TAB IS GRANT Xact
I understand why Process ID 117 is blocked by 112 but why Process ID 87 is blocked (waiting)? And why it is waiting on Process ID 117?
It is weird for me that a Process ID (117) that have not been granted any real lock yet can block another process (87)...
Best regards,
Carl
-
This was removed by the editor as SPAM
-
I'm not sure... but it appear that both 112 has an IX -(intent Exclusive lock) lock on the table. Process 87 wants a shared lock, but it can't share if 112 has exclusive. If 112 has an IS lock (shared) than process 87 would be ok. Process 117 would still wait since it is asking for an IX (exlusive) lock.
Francis
-
Hello Francis,
Thanks for your input...
Process 122 have an IX on the table (TAB) which does not forbid another process having an IX lock on the same table (TAB).
Try to QUERY one of your table with the HINT (updlock):
BEGIN TRANSACTION
SELECT * FROM x (UPDLOCK)
...
Than on another connexion try to QUERY this table:
SELECT * FROM x
You will be able to query it... you won't be blocked.
Best regards.
Carl
-
What is more confusing is...
If I run the query done by proccess ID 112 (without commiting) and than I run the query done by process ID 87, it is ok.. no blocking.
Process ID 112 still have the same locks.
It seem like a bug...
Carl
-
What if process 87 uses WITH NO_LOCK while 117 and 112 are doing their stuff? Is this an option? It does sound odd. ARe you running the latest Service Pack?
Francis
-
It worked.
It would involve a lot of modification in the code of our application... But nothing would ensure we are not reading something that is in the process of being modified with an exclusive lock on the key...
I run with service pack 3a not with service pack 4.
I read service pack 4 description and no solved bug math this behavior.
I plan to apply it soon.
Regards.
Carl
-
Here is the answer posted by a Microsoft SQL Server member of the "Storage Engine - Accces method team":
As Ping has already answered - this is the normal behavior of the locking manager. Despite the lock requested by SPID 87 is compatible with the lock already granted to 117, there is a waiter in the queue that happened to have been put there before SPID 87 requested the lock.
The reason why the things done this way is simple - the fairness. If the lock manager didn't follow this protocol and instead immedately satiflied the new lock requests whenever the lock mode of the current owner is compatble with the new requests - AND - if the lock requestors with the compatible mode locks kept coming before waiters would get a change to acquire the lock, the waiters would ultimately stuck in the queue and would never ever get the lock.
Microsoft SQL Server - Storage Engine - Access Methods
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply