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.



Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net