In EM, under Management -> Current Activity -> Locks / Object, I have a
table that shows 4,621 Page locks (which I think is ok, because they are all
me, and I'm currently waiting for a bit fat batch to complete.  But on the
list, in the Mode column, they are all listed as Mode = IX.

Now, because Mikey is a code monkey and not a DBA, this sends Mikey off to
the BOL index.  I'm not sure I quite understand the difference between an X
lock and an IX lock.  If I'm running a big fat batch that's updating 1.9 M
records, shouldn't I be seeing X locks?  If I'm reading this right, all
these IX locks mean "Intent to Lock", but not yet locked.  I'm confused.

IX doesn't mean "intent to lock". If you have an X lock on a row, you will have same IX lock that
the page where the row is and also on the table. Think of it as a communication mechanism. We don't
want someone to take for instance an X table lock if someone else has an X row lock. You should see
a bunch of X row locks.

Show quoteHide quote

IX locks are only placed on pages or tables.  It does mean intent to lock,
and it is a mechanism to ensure concurrency in the lock heirarchy.

Here's the way it works:  assuming you want to update three rows on 2
different pages in the same table.

SQL Server first tries to get an IX lock on the table to prevent any other
transaction from locking the entire table exclusively while it's trying to
obtain page and row locks.  If successful, SQL Server tries to obtain IX
locks on each affected page to prevent any other transaction from locking
that page exclusively while it's placing the locks on the individual rows.
If the page IX locks are obtained, SQL Server tries to obtain X locks on the
individual rows.  Once the exclusive locks are obtained, the updates can be
performed.  More than one transaction can obtain IX locks on the same table,
and/or page, provided the sets of rows to be updated by each transaction are
disjoint.  This prevents one transaction from escalating an exclusive lock
to a page or table lock if another transaction is preparing to update a
different set of rows on the same page or in the same table respectively.


Show quoteHide quote

Thanks!  That really cleared it up!

Undo Functionality
Transaction in SP
Case Statement?
Adding non-table field to a view?
problem with context info