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.