asked
How can multiple spids lock the same table object?
I ran into an application that when failed would leave its spid active and lock table A. Other queries were still able to read from table A. When the weekly index maintenance started on table A it would get blocked, but would apply another lock on table A. This 2nd lock would block any further query even if it was read only. If table A is already locked, how is the index rebuild able to acquire additional locks? Here are the events in order from sp_whoIsActive and sp_lock. 1. spid 98 "runaway query, 24+ hours". Lock Type Sch-S on object 1389248004 (table A), Status Grant 2. spid 95 "alter index rebuild". Lock Type Sch-M on object 1389248004, Status Wait, Blocked by 98 3. spid 150 "select query". Lock Type Sch-S on object 1389248004, Status Wait, Blocked by 95 The runaway app process is being fixed, but the behavior of allowing a 2nd lock for the index rebuild causes table A to go from a "light" readable lock to full blown untouchable by anything causing other apps to blow up.
Every query you run will take a SCH-S (schema stability) lock to ensure that the table's schema doesn't change while the query is running. This lock type is compatible with all the other lock types except for SCH-M (schema modification), which does not happen to be compatible with *any* other type of lock. According to technet, "During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released." - [
https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx][1] They also have a page where you can view the Complete Lock Compatibility Matrix over here - [
https://technet.microsoft.com/en-us/library/ms186396(v=sql.105).aspx][2], though Mark Broadbent has done them one better with a color-coded version here - [
https://code.msdn.microsoft.com/SQL-Server-Lock-Compatibili-5b4e48f5][3] In your example, something has gone wrong and session 98 essentially has a permanent SCH-S lock on the table. This does not affect any of your operational queries because it is compatible with all of them per the matrix referenced above. At some point in the future, your index rebuild comes along and wants to take the SCH-M lock so it can rebuild the index. However, it can't get the lock because there's already an SCH-S lock on the table. The rebuild has to wait until all other locks have been released before the SCH-M lock is granted. SQL Server is patient. When it can't get a lock, it will wait indefinitely until the locked resource becomes available. Once the SCH-M lock is lined up and waiting its turn for the resource, every other lock request that comes along has to wait in line behind it - remember it is not compatible with any of them and they aren't allowed to jump ahead of it. For further reading, Michael J. Swart did a nice little article that explains it very clearly here - [
http://michaeljswart.com/2013/04/the-sch-m-lock-is-evil/][4] [1]:
https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx [2]:
https://technet.microsoft.com/en-us/library/ms186396(v=sql.105).aspx [3]:
https://code.msdn.microsoft.com/SQL-Server-Lock-Compatibili-5b4e48f5 [4]:
http://michaeljswart.com/2013/04/the-sch-m-lock-is-evil/
17 People are following this question.
