Schema Locks - Pro SQL Server Internals - page 435

The second session would still wait for the schema stability (Sch-S) lock. There are no shared (S) locks in the
READ UNCOMMITTED transaction isolation level, and the schema stability (Sch-S) lock is the only way to keep a schema
stable during execution. However, the session with the DELETE statement would wait for an intent exclusive (IX) lock
instead. That lock type needs to be acquired anyway, and it can replace a schema stability (Sch-S) lock because it is
also incompatible with schema modification (Sch-M) locks and prevents schema from being altered.
Mixing schema modification locks with other lock types in the same transaction increases the possibility of the
deadlocks. Let's assume that we have two sessions: the first one starts the transaction, and it updates the row in the
table. At this point, it holds exclusive (X) lock on the row and two intent exclusive (IX) locks on the page and table.
If another session tries to read (or update) the same row, it would be blocked. At this point, it will wait for the shared
(S) lock on the row and have the intent shared (IS) locks held on the page and the table. That stage is illustrated in
Figure 23-3 . (Page-level intent locks are omitted.)
Figure 23-3. Deadlock due to mixed DDL and DML statements (Step 1)
If at this point the first session wants to alter the table, it would need to acquire a schema modification (Sch-M)
lock. That lock type is incompatible with any other lock types, and the session would be blocked by the intent shared
(IS) lock held by the second session. We would then have a deadlock, as shown in Figure 23-4 .
Figure 23-4. Deadlock due to mixed DDL and DML statements (Step 2)