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...
> > If I have an IX lock on a table, what kind of DMLs am I restricted doing
?
> >
> >
>|||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...
>> 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
> ?
>> >
>> >
>>
>|||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 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...
> >> > If I have an IX lock on a table, what kind of DMLs am I restricted
> >> > doing
> > ?
> >> >
> >> >
> >>
> >>
> >
> >
>|||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...
>> 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...
>> >> 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
>> > ?
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||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...
> > 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 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...
> >> >> > If I have an IX lock on a table, what kind of DMLs am I restricted
> >> >> > doing
> >> > ?
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||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...
>> 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...
>> >> 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...
>> >> >> 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
>> >> > ?
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||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...
> > 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...
> >> > 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 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...
> >> >> >> > If I have an IX lock on a table, what kind of DMLs am I
> >> >> >> > restricted
> >> >> >> > doing
> >> >> > ?
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||This is a multi-part message in MIME format.
--060208030708050403020006
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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...
>
>>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...
>>
>>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...
>>
>>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...
>>>
>>>
>>>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
>>>
>>>
>>>?
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
>
--060208030708050403020006
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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?<br>
<br>
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.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Hassan wrote:
<blockquote cite="midO4wukBLFFHA.2832@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">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" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl">news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.SolidQualityLearning.com">www.SolidQualityLearning.com</a>
"Hassan" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:fatima_ja@.hotmail.com"><fatima_ja@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl">news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Yes I have an IX lock on a table and you mentioned that I would see if
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->if
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">one has an X lock on a page or a row. But could not find anything
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl">news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->can't
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">see
</pre>
<blockquote type="cite">
<pre wrap="">it in sp_lock. Or are you just assuming that, because I said that is
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->what
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">blocks an IX lock?
--
HTH
--
Kalen Delaney
SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.SolidQualityLearning.com">www.SolidQualityLearning.com</a>
"Hassan" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:fatima_ja@.hotmail.com"><fatima_ja@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl">news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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
</pre>
</blockquote>
</blockquote>
<pre wrap="">example
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">for some spids.. Any idea what types of locks these are
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl">news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">sp_lock shows you who's holding what locks.
--
HTH
--
Kalen Delaney
SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.SolidQualityLearning.com">www.SolidQualityLearning.com</a>
"Hassan" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:fatima_ja@.hotmail.com"><fatima_ja@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl">news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">So how can I find out whats holding an X lock to this table ... I
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">mean
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">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
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->on
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">the
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">table and wouldnt kill..
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl">news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi Hassan
IX lock on the table means that someone has X lock on a row or a
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">page.
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">So
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">you can do any modifications on other rows or pages. You just
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->can't
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">do
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">anything that requires X lock on the whole table. X lock is
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">incompatible
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">with IX, but IX can be combined with another IX (two processes
with
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">X
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">lock
</pre>
<blockquote type="cite">
<pre wrap="">on separate rows each have IX on the table).
--
HTH
--
Kalen Delaney
SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.SolidQualityLearning.com">www.SolidQualityLearning.com</a>
"Hassan" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:fatima_ja@.hotmail.com"><fatima_ja@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl">news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">If I have an IX lock on a table, what kind of DMLs am I
restricted
doing
</pre>
</blockquote>
</blockquote>
<pre wrap="">?
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--060208030708050403020006--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C514E6.A3E1B940
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Not hiding anything.. And yes its weird and I know a restart of the SQL =Services would fix it but I'd like to know more about the internals on =the PSS Structure that holds this info and that a kill command cannot =get to it
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:uxaP6LLFFHA.624@.TK2MSFTNGP15.phx.gbl...
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=20
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...
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...
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...
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...
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
?
=
--=_NextPart_000_000D_01C514E6.A3E1B940
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Not hiding anything.. And yes its weird =and I know a restart of the SQL Services would fix it but I'd like to know more =about the internals on the PSS Structure that holds this info and that a kill =command cannot get to it
"Mike Hodgson"
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Hassan wrote: sp_lock
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- -- 94 8 0 0 DB S GRANT
94 8 142321797 0 TAB IX GRANT
"Kalen Delaney" 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
http://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Hassan" =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" 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
http://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Hassan" =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" wrote in message
news:%23Z9XiyHFFHA.=3384@.tk2msftngp13.phx.gbl...
sp_lock shows you =who's holding what locks.
-- HTH
--
Kalen Delaney
SQL Server MVP
http://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Hassan" =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" 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
http://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Hassan" =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
?
= = = =
--=_NextPart_000_000D_01C514E6.A3E1B940--sql
No comments:
Post a Comment