SQL Server – Understanding and Avoiding Table Locks from Set of Locks

lockingsql-server-2008

I have a query on SQL Server 2008 that when using sp_whoisactive with get_locks = 1 has the following locks open….

<Object name="my_table" schema_name="dbo">
  <Locks>
    <Lock resource_type="KEY" index_name="pk_mypk" request_mode="X" request_status="GRANT" request_count="1" />
    <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
    <Lock resource_type="PAGE" page_type="*" index_name="pk_mypk" request_mode="IX" request_status="GRANT" request_count="1" />
  </Locks>
</Object>

I understand the Key and Page lock but am unsure what the Object one is. Is that a table lock? If so why did it bother taking key and page locks as well? Are there ways to work out why a particular query that is only touching a small set of rows would use a table lock, I'm guessing it's lock escalation.

Best Answer

Reference:

See above reference for more details.

Intent = Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Intent Locks

The Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.

Intent locks serve two purposes:

  • To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
  • To improve the efficiency of the Database Engine in detecting lock conflicts at the higher level of granularity.

For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because the Database Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

Related Question