IX lock on a table

If I have an IX lock on a table, what kind of DMLs am I restricted doing ?Hi Hassan
IX lock on the table means that someone has X lock on a row or a page. So
you can do any modifications on other rows or pages. You just can't do
anything that requires X lock on the whole table. X lock is incompatible
with IX, but IX can be combined with another IX (two processes with X lock
on separate rows each have IX on the table).
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
> If I have an IX lock on a table, what kind of DMLs am I restricted doing ?
>|||So how can I find out whats holding an X lock to this table ... I mean which
spid ? if you've read my previous thread, Im having some orphaned
connections and these spids that Im trying to kill have IX locks on the
table and wouldnt kill..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> Hi Hassan
> IX lock on the table means that someone has X lock on a row or a page. So
> you can do any modifications on other rows or pages. You just can't do
> anything that requires X lock on the whole table. X lock is incompatible
> with IX, but IX can be combined with another IX (two processes with X
lock
> on separate rows each have IX on the table).
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
?[vbcol=seagreen]
>|||sp_lock shows you who's holding what locks.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> So how can I find out whats holding an X lock to this table ... I mean
> which
> spid ? if you've read my previous thread, Im having some orphaned
> connections and these spids that Im trying to kill have IX locks on the
> table and wouldnt kill..
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> lock
> ?
>|||Yup cant really find anything due to it being orphaned, but found something
else for other spids when i ran sp_lock
Came across a type of "APP" and resource as "DNSQ4b945027" as an example
for some spids.. Any idea what types of locks these are
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
> sp_lock shows you who's holding what locks.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
So[vbcol=seagreen]
incompatible[vbcol=seagreen]
>|||Those are Application locks, defined by the user (or application). You can
read about Application Lock in the Books Online.
So how do you know someone is holding an X lock on a table, if you can't see
it in sp_lock. Or are you just assuming that, because I said that is what
blocks an IX lock?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Yup cant really find anything due to it being orphaned, but found
> something
> else for other spids when i ran sp_lock
> Came across a type of "APP" and resource as "DNSQ4b945027" as an example
> for some spids.. Any idea what types of locks these are
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
> So
> incompatible
>|||Yes I have an IX lock on a table and you mentioned that I would see if if
one has an X lock on a page or a row. But could not find anything
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
> Those are Application locks, defined by the user (or application). You can
> read about Application Lock in the Books Online.
> So how do you know someone is holding an X lock on a table, if you can't
see
> it in sp_lock. Or are you just assuming that, because I said that is what
> blocks an IX lock?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
example[vbcol=seagreen]
mean[vbcol=seagreen]
the[vbcol=seagreen]
page.[vbcol=seagreen]
do[vbcol=seagreen]
X[vbcol=seagreen]
>|||How are you seeing the IX lock? Run sp_lock, and look at all the rows for
the same spid that has the IX lock. In fact, you can pass the spid as an
argument to sp_lock.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
> Yes I have an IX lock on a table and you mentioned that I would see if if
> one has an X lock on a page or a row. But could not find anything
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
> see
> example
> mean
> the
> page.
> do
> X
>|||sp_lock <orphaned spid>
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
94 8 0 0 DB S GRANT
94 8 142321797 0 TAB IX GRANT
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl...
> How are you seeing the IX lock? Run sp_lock, and look at all the rows for
> the same spid that has the IX lock. In fact, you can pass the spid as an
> argument to sp_lock.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
if[vbcol=seagreen]
can't[vbcol=seagreen]
what[vbcol=seagreen]
on[vbcol=seagreen]
can't[vbcol=seagreen]
>|||You can't have just an IX lock on a table (can you?). An intent lock is
always as a result of a more granular lock. So if you have an TAB IX
lock then you must also have, for spid 94, a PAG X lock, a KEY X lock or
a RID X lock. Are there other locks on that spid that you're not
showing us or is that it?
To answer your original question, if there is an existing IX lock on a
table, any lock request other than an intent lock (IX or IS) on the
table will be blocked. So you can get a S, U or X lock on a page or row
in the table (assuming it's not the page or row that spid 94 is X
locking), which will give you an IS or IX lock on the table. So that
means you can do a SELECT, INSERT, UPDATE or DELETE on the table as long
as you only need to lock rows or pages in the table and not the whole table.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:

>sp_lock <orphaned spid>
>spid dbid ObjId IndId Type Resource Mode Status
>-- -- -- -- -- -- -- --
>94 8 0 0 DB S GRANT
>94 8 142321797 0 TAB IX GRANT
>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl...
>
>if
>
>can't
>
>what
>
>on
>
>can't
>
>
>