IX locks question

From what I'm getting, IX locks are just a safety mechanism to allow a
query
to lock at a higher grain (i.e. say, a table lock) if the lock manager needs
to
escalate row locks to page or table locks. Is this a correct assumption?
So, everytime I perform a DELETE or UPDATE can I expect that I will get
X locks on the rows affected and IX locks on the pages and on the table?
Is there a sample example against pubs or Northwind somewhere online that
illustrates this?To state it another way, an IX lock means that an exclusive lock may be held
at a lower level. For example, a row level exclusive lock will also acquire
a table level IX lock. The IX lock will prevent a conflicting table-level
lock from being acquired without having to check individual locks that are
lower in the hierarchy.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.co
mcast.com...
> From what I'm getting, IX locks are just a safety mechanism to allow a
> query
> to lock at a higher grain (i.e. say, a table lock) if the lock manager
> needs
> to
> escalate row locks to page or table locks. Is this a correct assumption?
> So, everytime I perform a DELETE or UPDATE can I expect that I will get
> X locks on the rows affected and IX locks on the pages and on the table?
> Is there a sample example against pubs or Northwind somewhere online that
> illustrates this?
>|||I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
me.
Is IX lock a safety mechanism of the lock manager?
This is what I think occurs based on my reading and toying around with
pubs...
please correct me where I'm wrong.
Process 1 deletes a row from the authors table. An X lock is placed on the
row being
deleted. IX is applied at the page and the table levels to signal to the
lock manager that there's a lower
level lock because something's going on at a page or row level.
Now, Process 2 comes along to delete rows from the authors table. the lock
manager says "Hold on, there
are lower level row locks that must be checked before you may proceed". At
that point, it checks
to see if the rows it's deleting will cause a page or table lock. If so,
Process 2 will wait because
Process 1 already has an IX lock. If no page or table lock will be needed,
Process 2 will place
X locks on the rows affected and also place it's own IX lock at the page and
table level.
Am I close?
TIA
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> To state it another way, an IX lock means that an exclusive lock may be
held
> at a lower level. For example, a row level exclusive lock will also
acquire
> a table level IX lock. The IX lock will prevent a conflicting table-level
> lock from being acquired without having to check individual locks that are
> lower in the hierarchy.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.co
mcast.com...
assumption?[vbcol=seagreen]
that[vbcol=seagreen]
>|||> Am I close?
You are correct in your description of Process 1 but it's probably better to
think about Process 2 in terms of lock escalation.
When Process 2 deletes a row, the IX locks are successfully acquired because
these are compatible with Process 1's existing IX locks. Process 2 gets the
exclusive lock on the row to be because it's on a different row than Process
1 is deleting.
When process 2 deletes a lot more rows, SQL Server will try to convert those
many row locks to a single table X lock. However, because that table-level
X lock isn't compatible with the existing Process 1 table IX lock, Process 2
waits until the lock is released.
You can read more about lock escalation in the SQL 2000 Books Online
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_con_7a_5ovi.htm).
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.co
mcast.com...
> I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
> me.
> Is IX lock a safety mechanism of the lock manager?
> This is what I think occurs based on my reading and toying around with
> pubs...
> please correct me where I'm wrong.
> Process 1 deletes a row from the authors table. An X lock is placed on
> the
> row being
> deleted. IX is applied at the page and the table levels to signal to the
> lock manager that there's a lower
> level lock because something's going on at a page or row level.
> Now, Process 2 comes along to delete rows from the authors table. the
> lock
> manager says "Hold on, there
> are lower level row locks that must be checked before you may proceed".
> At
> that point, it checks
> to see if the rows it's deleting will cause a page or table lock. If so,
> Process 2 will wait because
> Process 1 already has an IX lock. If no page or table lock will be
> needed,
> Process 2 will place
> X locks on the rows affected and also place it's own IX lock at the page
> and
> table level.
> Am I close?
> TIA
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> held
> acquire
> assumption?
> that
>|||Thank you, thank you! That's what I'm looking for!
I'm a visual learner.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eLLSBYfyGHA.1304@.TK2MSFTNGP05.phx.gbl...
> You are correct in your description of Process 1 but it's probably better
to
> think about Process 2 in terms of lock escalation.
> When Process 2 deletes a row, the IX locks are successfully acquired
because
> these are compatible with Process 1's existing IX locks. Process 2 gets
the
> exclusive lock on the row to be because it's on a different row than
Process
> 1 is deleting.
> When process 2 deletes a lot more rows, SQL Server will try to convert
those
> many row locks to a single table X lock. However, because that
table-level
> X lock isn't compatible with the existing Process 1 table IX lock, Process
2
> waits until the lock is released.
> You can read more about lock escalation in the SQL 2000 Books Online
>
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_con_7a_5ovi.htm).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.co
mcast.com...
with[vbcol=seagreen]
the[vbcol=seagreen]
so,[vbcol=seagreen]
a[vbcol=seagreen]
manager[vbcol=seagreen]
get[vbcol=seagreen]
>