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]
>

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@.comcast.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@.comcast.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?
> >
> >
>|||> 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\Microsoft%20SQL%20Server\80\Tools\Books\acdata.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@.comcast.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...
>> 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@.comcast.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?
>> >
>> >
>>
>|||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...
> > 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\Microsoft%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@.comcast.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...
> >> 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@.comcast.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?
> >> >
> >> >
> >>
> >>
> >
> >
>

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[vbcol=seagreen]
> 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...
?
>
|||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...[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[vbcol=seagreen]
incompatible
>
|||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[vbcol=seagreen]
> 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
>
|||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...[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...
if[vbcol=seagreen]
can't[vbcol=seagreen]
what[vbcol=seagreen]
on[vbcol=seagreen]
can't
>
|||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
>
>
>

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...
> > 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" wrote in message news:uxaP6LLFFHA.624@.T=K2MSFTNGP15.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 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

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
>
>
>

IUSR_ to run function, drop table..

Helo all,

i dont know if that is the right way, but i was wanted to make kind of sql profiler on a webpage :) and cool features would be to usa ajax to fire it async in some time secvence.. ok, ok, here goes my code and expla... i have sql2005 (express edition,, cose i think its so cool and for a lot of projects can be used for), attached startup procedure, whit>sp_procoption 'kreni_instanco_moja', startup, true,and kreni_insta... is like:

------
setANSI_NULLSON
setQUOTED_IDENTIFIERON
GO
ALTERPROCEDURE [dbo].[kreni_instanco_moja]
AS
BEGIN
exec kp.dbo.start_trace
END
------

ok and my start_trace is:

------
ALTERPROCEDURE [dbo].[start_trace]
AS
BEGIN
DECLARE
@.traceidnumINT,@.onBIT,@.file_pathNVARCHAR(50),@.maxsizebigint;
SET @.on= 1;SET @.maxsize=5;
SET @.file_path='O:\BU_DATABASE\'+convert(varchar,getdate(), 102);
EXEC sp_trace_create @.traceid= @.traceidnumOUTPUT, @.options= 2, @.tracefile= @.file_path, @.maxfilesize= @.maxsize, @.stoptime=null, @.filecount=0;
EXEC sp_trace_setevent 2, 12, 1, @.on-- ili 12 ili 13
EXEC sp_trace_setstatus 2, 1-- 1 = START, 0 = STOP
END
------

actual code that brings table and shows sql executed is:

------
use kp
droptable trace
select textdatainto tracefrom
fn_trace_gettable(N'O:\BU_DATABASE\'+convert(varchar,getdate(), 102)+'.trc',1)
select textdatafrom trace
go
------

and it works fine,, but when its fired from my local admin connection, and try that whit IUSR*?? not going... ok, i think that IUSR have to have privilage to open that O:\BU_DATABASE ... file, and to have priv to drop tabel and to fire that function..fn_trace_get ... ?? so, that is where i got stuck up... any help would be veryyyy apreciated... hey, we can put some kind of project if anybody is interested! :) to have admin page and to monitor sql code that is executed,,, and remotely

if i was not clear in some explaining, you can ask me and ill try better

thenx, kpendic

Yes, the connecting user needs all the privileges you stated (file, drop etc.), plus system wide ALTER TRACE permission.

|||

Yes Gunteman, and even ALTER TRC permission... but do you know how to achive this maybe??

thenx

sql

its been a while since Ive used sql....

...and I was using mySql b4, but on ms sql server when you create a table whats the syntax for specifying a field that auto increments for each new record added?

'm trying the following:

1> create table maillist(id autoincrement(), email varchar)
2> go
Msg 170, Level 15, State 1, Server ****, Line 1
Line 1: Incorrect syntax near ')'.
1>IDENTITY is the keyword used in SQL Server

CREATE TABLE [dbo].[Debug] (
[DebugID] [int] IDENTITY (1, 1) NOT NULL ,
[DateEntered] [datetime] NULL ,
[Message] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

|||i take it

dbo = the db name

debug = the table

dbugid = my field name

int = type

what is: on [primary]?|||what does the (1,1) signify?|||The seed value and incremental step for the identity column. (e.g starts from 1 and increases 1 at a time 1,2,3,4,5...)|||ahhh

I getcha :)|||dbo is the owner of the table (dbo is best).
Debug is the table.
debugid is the field name.

Primary is the file group (you can normally ignore it, this is script generated by the Enterprise Manager).

Iterator Table and rand() function

A couple of people asked that I post my iterator table and rand() function. The iterator table is basically a list of numbers 1-32767 and serves the same use as a "table of numbers." You can find good information about tables of numbers here:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

To create an iterator table:

create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)
go

/* -- */
/* This routine is used to populate the small_iterator table. */
/* This query ran in 1 second in development and should run at */
/* a similar speed in production. */
/* -- */

truncate table SMALL_ITERATOR

insert into small_iterator
select number from master.dbo.spt_values (nolock)
where name is null
and number <= 255

insert into small_iterator
select 256 * j.iter + i.iter
from small_iterator i
inner join small_iterator j
on j.iter > 0
and j.iter <= 127
order by 256 * j.iter + i.iter

delete from small_iterator where iter = 0

select count(*) [count],
min (iter) [min iterator],
max (iter) [max iterator]
from SMALL_ITERATOR

go

dbcc dbreindex (small_iterator, '', 100)
go

update statistics small_iterator
go

exec sp_recompile small_iterator
go

Here is my RAND scalar UDF; it comes in handy at times for generating mock data:

create view dbo.vRand
as
select rand () as vRand
go

create function dbo.rand ()
returns float
as
begin

return (select vRand from dbo.vRand)

end

go

create function dbo.randList
( @.pm_listSize integer
)
returns @.randList table
( rid integer,
iRand float
)
as
begin

declare @.upperBound integer

set @.upperBound = ceiling (convert(float, (@.pm_listSize+1))
/ convert (float, 32767))

insert into @.randList
select 32767*(j.iter-1) + i.iter - 1 as rid,
dbo.rand() as iRand
from small_iterator i (nolock)
inner join small_iterator j (nolock)
on j.iter <= @.upperBound
and 32767*(j.iter-1) + i.iter - 1 <= @.pm_listSize
and 32767*(j.iter-1) + i.iter - 1 > 0

return

end

Iteration within Stored Procedure

A Stored Procedure inserts a record in a base table. I want to add n records (n=1 to 5) into a related table. Another SP (tdAuthorityInsert) is already set up to insert one record into the related table.

So, two questions
1. What is the form of an iterative loop in a Stored Procedure?
2. How do you call a Stored Procedure from inside another?

Thanks, td

1. while

2. just use exec your_stored_procecure_name

|||

Thanks KH. I'm afraid, though, I am very new & your help is a bit too cryptic for me. Sorry but I require a bit more.

In the interim I had figured out the EXEC (I presume this is an abbreviation of EXECUTE) but I have trouble with the parameters. The SP goes something like this:

...AS
@.RequestID BigInt
.....
EXEC InsertReviewer @.RequestID,@.RequestID,@.RequestDate,GETDATE()

The problem is that the SP doing the calling has a parameter of @.Request. So does the called SP. I want to pass the ID through with other parameters.

The WHILE, is it used with a BEGIN/END eg
WHILE @.NumReviewers<5
BEGIN

END

Thanks very much for your patience

td

|||Is this what you trying to do ?
declare @.NumReviewers int
-- Initialize the @.NumReviewers
select @.NumReviewers = 1
WHILE @.NumReviewers <= 5
BEGIN
-- execute your SP and pass in the @.NumReviewers
exec your_sp_name @.NumReviewers
-- increment the @.NumReviewers
select @.NumReviewers = @.NumReviewers + 1
END

Iteration through many tables to perform update

...Ok so I have one field that exists in an many ARCHIVE tables(approx 25).
The field name is the same throughout these tables and each table is created
in weekly intervals.
I want to be able to update the field for all the rows in each of these
table in one lump of an update sProc.
My initial ideas areto query sysobjects for the tables I want and then
iterate through them one by one until the update is complete (maybe using
dynamic sql). This is a once off update and performance time is the key.
Any ideas / examples?>I forgot to add, My table has 10million rows and i estimate an runtime of
5.5hrs. Here's the clincher though. I only have a 5 hr window to complete th
e
update.
"marcmc" wrote:

> ...Ok so I have one field that exists in an many ARCHIVE tables(approx 25)
.
> The field name is the same throughout these tables and each table is creat
ed
> in weekly intervals.
> I want to be able to update the field for all the rows in each of these
> table in one lump of an update sProc.
> My initial ideas areto query sysobjects for the tables I want and then
> iterate through them one by one until the update is complete (maybe using
> dynamic sql). This is a once off update and performance time is the key.
> Any ideas / examples?>
>|||Are all these tables of the same structure, i.e. exact same column names and
data types across all tables? If so, it sounds like a partitioned view.
You could then update the particular column.
Briefly, you create CHECK constraints on the partitioning column on each
table. The partitioning column must be part of the primary key. Then you
create a view like:
create view MyView
as
select * from MyTable1
union all
select * from MyTable2
union all
...
go
update MyView
set
MyCol = 'XYZ'
As for your window, large updates take time. You may want to do a
background iterative method, as long as logical consistency is not an issue.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:8661C481-F4F0-4ED4-97E2-74BB47D70F4A@.microsoft.com...
I forgot to add, My table has 10million rows and i estimate an runtime of
5.5hrs. Here's the clincher though. I only have a 5 hr window to complete
the
update.
"marcmc" wrote:

> ...Ok so I have one field that exists in an many ARCHIVE tables(approx
> 25).
> The field name is the same throughout these tables and each table is
> created
> in weekly intervals.
> I want to be able to update the field for all the rows in each of these
> table in one lump of an update sProc.
> My initial ideas areto query sysobjects for the tables I want and then
> iterate through them one by one until the update is complete (maybe using
> dynamic sql). This is a once off update and performance time is the key.
> Any ideas / examples?>
>