Lock Compatibility

If a process has already locked a resource, the granting of lock requests by other transactions on the same resource is governed by the lock compatibility matrix within SQL Server. Table 38.5 shows the lock compatibility diagram for the SQL Server Lock Manager, indicating which lock types are compatible and which lock types are incompatible when requested on the same resource.

Table 38.5. SQL Server Lock Compatibility Matrix

		Existing Lock Type
Requested Lock Type	IS		S	U	IX	SIX	X	Sch-S	SCH-M	BU
Intent Shared	Yes		Yes	Yes	Yes	Yes	No	Yes	No	No
Shared	Yes		Yes	Yes	No	No	No	Yes	No	No
Update	Yes		Yes	No	No	No	No	Yes	No	No
Intent Exclusive	Yes		No	No	Yes	No	No	Yes	No	No
Shared with Intent Exclusive	Yes		No	No	No	No	No	Yes	No	No
Exclusive	No		No	No	No	No	No	Yes	No	No
Schema Stability	Yes		Yes	Yes	Yes	Yes	Yes	Yes	No	Yes
Schema Modify	No		No	No	No	No	No	No	No	No
Bulk Update	No		No	No	No	No	No	Yes	No	Yes

For example, if a transaction has acquired a shared lock on a resource, the possible lock types that can be acquired on the resource by other transactions are intent shared, shared, update, and schema stability locks. Intent exclusive, SIX, exclusive, schema modification, and bulk update locks are incompatible with a shared lock and cannot be acquired on the resource until the shared lock is released.