Rowlock never escalates to Paglock?

Hi,
I have a table with an indexed column named "id". The table contains about
100,000 records.
I use "ROWLOCK" lock hint to tell SQL Server not to escalate to higher
level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
Two transactions are as following:
--Tran1
begin tran
update dbo.table1 with (rowlock) set someValue=0 where id=1000
waitfor delay '00:00:10'
commit tran
--Tran2
begin tran
select * from dbo.table1 where id=1001
commit tran
If I run Tran1 first and then Tran2, the second transaction got to wait
until the first one is done.
So it seems the page where id=1000 locates was locked, preventing id=1001 to
be read. Well... ROWLOCK means we want to lock a "row", not a whole page,
right?
Run sp_lock while the Tran1 is running, and I got the result:
66 7 141243558 1 KEY (6d0040d1d33f) X GRANT
66 7 141243558 1 PAG 1:1753 IX GRANT
66 7 141243558 0 TAB IX
GRANT
66 7 141243558 1 KEY (6c00321b0c6a) X GRANT
67 1 85575343 0 TAB IS
GRANT
As I know, PAG IX lock means only partial of an page is locked. It's what I
expected and, obviously contrary with the result I had.
Could anyone explain this to me? If this doesn't work, any other way to
make sure lock only applied to a single row of a table?
Thank you in advance
Ryan> As I know, PAG IX lock means only partial of an page is locked. It's
what I
> expected and, obviously contrary with the result I had.
> Could anyone explain this to me? If this doesn't work, any other way
to
> make sure lock only applied to a single row of a table?
An IX lock is not the result of escalation and is not a 'partial' lock.
This is an intent-exclusive lock acquired at a higher level to indicate
that a more granular lock is also held. IX locks are compatible with
other intent locks.
Your example shows that you've successfully acquired row locks on 2
different rows. Another SPID can access different rows, even if on the
same page.
See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for more info.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Ryan" <ryan@.cradle.com.tw> wrote in message
news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a table with an indexed column named "id". The table contains
about
> 100,000 records.
> I use "ROWLOCK" lock hint to tell SQL Server not to escalate to
higher
> level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
> Two transactions are as following:
> --Tran1
> begin tran
> update dbo.table1 with (rowlock) set someValue=0 where id=1000
> waitfor delay '00:00:10'
> commit tran
> --Tran2
> begin tran
> select * from dbo.table1 where id=1001
> commit tran
> If I run Tran1 first and then Tran2, the second transaction got to
wait
> until the first one is done.
> So it seems the page where id=1000 locates was locked, preventing
id=1001 to
> be read. Well... ROWLOCK means we want to lock a "row", not a whole
page,
> right?
> Run sp_lock while the Tran1 is running, and I got the result:
> 66 7 141243558 1 KEY (6d0040d1d33f) X GRANT
> 66 7 141243558 1 PAG 1:1753 IX
GRANT
> 66 7 141243558 0 TAB
IX
> GRANT
> 66 7 141243558 1 KEY (6c00321b0c6a) X GRANT
> 67 1 85575343 0 TAB
IS
> GRANT
> As I know, PAG IX lock means only partial of an page is locked. It's
what I
> expected and, obviously contrary with the result I had.
> Could anyone explain this to me? If this doesn't work, any other way
to
> make sure lock only applied to a single row of a table?
>
> Thank you in advance
>
> Ryan
>
>|||Thank you for your reply.
> Your example shows that you've successfully acquired row locks on 2
> different rows. Another SPID can access different rows, even if on the
> same page.
I don't get it. The second transaction must wait until the first one is
completed. If the first transaction only holds a lock on the row, id=1000,
why another SPID got to wait when id=1001 is interested?
Ryan
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%230Sep%23imDHA.964@.TK2MSFTNGP10.phx.gbl...
> > As I know, PAG IX lock means only partial of an page is locked. It's
> what I
> > expected and, obviously contrary with the result I had.
> >
> > Could anyone explain this to me? If this doesn't work, any other way
> to
> > make sure lock only applied to a single row of a table?
> An IX lock is not the result of escalation and is not a 'partial' lock.
> This is an intent-exclusive lock acquired at a higher level to indicate
> that a more granular lock is also held. IX locks are compatible with
> other intent locks.
> Your example shows that you've successfully acquired row locks on 2
> different rows. Another SPID can access different rows, even if on the
> same page.
> See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for more info.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Ryan" <ryan@.cradle.com.tw> wrote in message
> news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I have a table with an indexed column named "id". The table contains
> about
> > 100,000 records.
> >
> > I use "ROWLOCK" lock hint to tell SQL Server not to escalate to
> higher
> > level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
> > Two transactions are as following:
> >
> > --Tran1
> > begin tran
> > update dbo.table1 with (rowlock) set someValue=0 where id=1000
> > waitfor delay '00:00:10'
> > commit tran
> >
> > --Tran2
> > begin tran
> > select * from dbo.table1 where id=1001
> > commit tran
> >
> > If I run Tran1 first and then Tran2, the second transaction got to
> wait
> > until the first one is done.
> >
> > So it seems the page where id=1000 locates was locked, preventing
> id=1001 to
> > be read. Well... ROWLOCK means we want to lock a "row", not a whole
> page,
> > right?
> >
> > Run sp_lock while the Tran1 is running, and I got the result:
> >
> > 66 7 141243558 1 KEY (6d0040d1d33f) X GRANT
> > 66 7 141243558 1 PAG 1:1753 IX
> GRANT
> > 66 7 141243558 0 TAB
> IX
> > GRANT
> > 66 7 141243558 1 KEY (6c00321b0c6a) X GRANT
> > 67 1 85575343 0 TAB
> IS
> > GRANT
> >
> > As I know, PAG IX lock means only partial of an page is locked. It's
> what I
> > expected and, obviously contrary with the result I had.
> >
> > Could anyone explain this to me? If this doesn't work, any other way
> to
> > make sure lock only applied to a single row of a table?
> >
> >
> >
> > Thank you in advance
> >
> >
> > Ryan
> >
> >
> >
>|||Hi Ryan
Are you actually seeing another process waiting? As Dan explained, IX locks
are compatible with other IX locks. If you have a case of another
transaction blocking while attempted to access a DIFFERENT row, please post
the sp_lock output showing the process with the WAIT status.
Also, a ROWLOCK hint does not prevent true escalation. It only encourages
SQL Server to start with row (or key)locks, but if the conditions are right
and enough rows are locked, SQL Server can ALWAYS escalate to a table lock.
It will never escalate from row to page locks, escalation is only to table
locks.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ryan" <ryan@.cradle.com.tw> wrote in message
news:efP6YjCnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> Thank you for your reply.
> > Your example shows that you've successfully acquired row locks on 2
> > different rows. Another SPID can access different rows, even if on the
> > same page.
> I don't get it. The second transaction must wait until the first one is
> completed. If the first transaction only holds a lock on the row,
id=1000,
> why another SPID got to wait when id=1001 is interested?
>
> Ryan
>
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:%230Sep%23imDHA.964@.TK2MSFTNGP10.phx.gbl...
> > > As I know, PAG IX lock means only partial of an page is locked. It's
> > what I
> > > expected and, obviously contrary with the result I had.
> > >
> > > Could anyone explain this to me? If this doesn't work, any other way
> > to
> > > make sure lock only applied to a single row of a table?
> >
> > An IX lock is not the result of escalation and is not a 'partial' lock.
> > This is an intent-exclusive lock acquired at a higher level to indicate
> > that a more granular lock is also held. IX locks are compatible with
> > other intent locks.
> >
> > Your example shows that you've successfully acquired row locks on 2
> > different rows. Another SPID can access different rows, even if on the
> > same page.
> >
> > See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for more info.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > I have a table with an indexed column named "id". The table contains
> > about
> > > 100,000 records.
> > >
> > > I use "ROWLOCK" lock hint to tell SQL Server not to escalate to
> > higher
> > > level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
> > > Two transactions are as following:
> > >
> > > --Tran1
> > > begin tran
> > > update dbo.table1 with (rowlock) set someValue=0 where id=1000
> > > waitfor delay '00:00:10'
> > > commit tran
> > >
> > > --Tran2
> > > begin tran
> > > select * from dbo.table1 where id=1001
> > > commit tran
> > >
> > > If I run Tran1 first and then Tran2, the second transaction got to
> > wait
> > > until the first one is done.
> > >
> > > So it seems the page where id=1000 locates was locked, preventing
> > id=1001 to
> > > be read. Well... ROWLOCK means we want to lock a "row", not a whole
> > page,
> > > right?
> > >
> > > Run sp_lock while the Tran1 is running, and I got the result:
> > >
> > > 66 7 141243558 1 KEY (6d0040d1d33f) X GRANT
> > > 66 7 141243558 1 PAG 1:1753 IX
> > GRANT
> > > 66 7 141243558 0 TAB
> > IX
> > > GRANT
> > > 66 7 141243558 1 KEY (6c00321b0c6a) X GRANT
> > > 67 1 85575343 0 TAB
> > IS
> > > GRANT
> > >
> > > As I know, PAG IX lock means only partial of an page is locked. It's
> > what I
> > > expected and, obviously contrary with the result I had.
> > >
> > > Could anyone explain this to me? If this doesn't work, any other way
> > to
> > > make sure lock only applied to a single row of a table?
> > >
> > >
> > >
> > > Thank you in advance
> > >
> > >
> > > Ryan
> > >
> > >
> > >
> >
> >
>|||Dear Kalen,
> Are you actually seeing another process waiting? As Dan explained, IX
locks
> are compatible with other IX locks. If you have a case of another
> transaction blocking while attempted to access a DIFFERENT row, please
post
> the sp_lock output showing the process with the WAIT status.
Could you please link to :
http://sbu.cradle.com.tw/TimeSheet/RC/ROWLOCK.gif
I captured the windows of Tran1, Tran2, and the result of running sp_lock in
the above picture.
Ryan
> Also, a ROWLOCK hint does not prevent true escalation. It only encourages
> SQL Server to start with row (or key)locks, but if the conditions are
right
> and enough rows are locked, SQL Server can ALWAYS escalate to a table
lock.
> It will never escalate from row to page locks, escalation is only to table
> locks.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ryan" <ryan@.cradle.com.tw> wrote in message
> news:efP6YjCnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> > Thank you for your reply.
> >
> > > Your example shows that you've successfully acquired row locks on 2
> > > different rows. Another SPID can access different rows, even if on
the
> > > same page.
> >
> > I don't get it. The second transaction must wait until the first one is
> > completed. If the first transaction only holds a lock on the row,
> id=1000,
> > why another SPID got to wait when id=1001 is interested?
> >
> >
> > Ryan
> >
> >
> >
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:%230Sep%23imDHA.964@.TK2MSFTNGP10.phx.gbl...
> > > > As I know, PAG IX lock means only partial of an page is locked.
It's
> > > what I
> > > > expected and, obviously contrary with the result I had.
> > > >
> > > > Could anyone explain this to me? If this doesn't work, any other
way
> > > to
> > > > make sure lock only applied to a single row of a table?
> > >
> > > An IX lock is not the result of escalation and is not a 'partial'
lock.
> > > This is an intent-exclusive lock acquired at a higher level to
indicate
> > > that a more granular lock is also held. IX locks are compatible with
> > > other intent locks.
> > >
> > > Your example shows that you've successfully acquired row locks on 2
> > > different rows. Another SPID can access different rows, even if on
the
> > > same page.
> > >
> > > See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for more
info.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > SQL FAQ links (courtesy Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > --
> > >
> > > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > > news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > I have a table with an indexed column named "id". The table
contains
> > > about
> > > > 100,000 records.
> > > >
> > > > I use "ROWLOCK" lock hint to tell SQL Server not to escalate to
> > > higher
> > > > level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
> > > > Two transactions are as following:
> > > >
> > > > --Tran1
> > > > begin tran
> > > > update dbo.table1 with (rowlock) set someValue=0 where id=1000
> > > > waitfor delay '00:00:10'
> > > > commit tran
> > > >
> > > > --Tran2
> > > > begin tran
> > > > select * from dbo.table1 where id=1001
> > > > commit tran
> > > >
> > > > If I run Tran1 first and then Tran2, the second transaction got to
> > > wait
> > > > until the first one is done.
> > > >
> > > > So it seems the page where id=1000 locates was locked, preventing
> > > id=1001 to
> > > > be read. Well... ROWLOCK means we want to lock a "row", not a whole
> > > page,
> > > > right?
> > > >
> > > > Run sp_lock while the Tran1 is running, and I got the result:
> > > >
> > > > 66 7 141243558 1 KEY (6d0040d1d33f) X
GRANT
> > > > 66 7 141243558 1 PAG 1:1753 IX
> > > GRANT
> > > > 66 7 141243558 0 TAB
> > > IX
> > > > GRANT
> > > > 66 7 141243558 1 KEY (6c00321b0c6a) X
GRANT
> > > > 67 1 85575343 0 TAB
> > > IS
> > > > GRANT
> > > >
> > > > As I know, PAG IX lock means only partial of an page is locked.
It's
> > > what I
> > > > expected and, obviously contrary with the result I had.
> > > >
> > > > Could anyone explain this to me? If this doesn't work, any other
way
> > > to
> > > > make sure lock only applied to a single row of a table?
> > > >
> > > >
> > > >
> > > > Thank you in advance
> > > >
> > > >
> > > > Ryan
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Is SQL Server using an index for the query? My guess is that SQL Server has to check a number of
rows, whether the value is 1000 or not and this is causing the blocking. I wonder whether SQL Server
would benefit from a unique or PK constraint in the column in this case? "I know there can only be
one row with a certain value, no need look further...".
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ryan" <ryan@.cradle.com.tw> wrote in message news:%23p%23A82DnDHA.2000@.TK2MSFTNGP12.phx.gbl...
> Dear Kalen,
>
> > Are you actually seeing another process waiting? As Dan explained, IX
> locks
> > are compatible with other IX locks. If you have a case of another
> > transaction blocking while attempted to access a DIFFERENT row, please
> post
> > the sp_lock output showing the process with the WAIT status.
> Could you please link to :
> http://sbu.cradle.com.tw/TimeSheet/RC/ROWLOCK.gif
> I captured the windows of Tran1, Tran2, and the result of running sp_lock in
> the above picture.
> Ryan
>
>
>
> >
> > Also, a ROWLOCK hint does not prevent true escalation. It only encourages
> > SQL Server to start with row (or key)locks, but if the conditions are
> right
> > and enough rows are locked, SQL Server can ALWAYS escalate to a table
> lock.
> > It will never escalate from row to page locks, escalation is only to table
> > locks.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > news:efP6YjCnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> > > Thank you for your reply.
> > >
> > > > Your example shows that you've successfully acquired row locks on 2
> > > > different rows. Another SPID can access different rows, even if on
> the
> > > > same page.
> > >
> > > I don't get it. The second transaction must wait until the first one is
> > > completed. If the first transaction only holds a lock on the row,
> > id=1000,
> > > why another SPID got to wait when id=1001 is interested?
> > >
> > >
> > > Ryan
> > >
> > >
> > >
> > >
> > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > > news:%230Sep%23imDHA.964@.TK2MSFTNGP10.phx.gbl...
> > > > > As I know, PAG IX lock means only partial of an page is locked.
> It's
> > > > what I
> > > > > expected and, obviously contrary with the result I had.
> > > > >
> > > > > Could anyone explain this to me? If this doesn't work, any other
> way
> > > > to
> > > > > make sure lock only applied to a single row of a table?
> > > >
> > > > An IX lock is not the result of escalation and is not a 'partial'
> lock.
> > > > This is an intent-exclusive lock acquired at a higher level to
> indicate
> > > > that a more granular lock is also held. IX locks are compatible with
> > > > other intent locks.
> > > >
> > > > Your example shows that you've successfully acquired row locks on 2
> > > > different rows. Another SPID can access different rows, even if on
> the
> > > > same page.
> > > >
> > > > See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for more
> info.
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > Dan Guzman
> > > > SQL Server MVP
> > > >
> > > > --
> > > > SQL FAQ links (courtesy Neil Pike):
> > > >
> > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > http://www.sqlserverfaq.com
> > > > http://www.mssqlserver.com/faq
> > > > --
> > > >
> > > > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > > > news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > > > > Hi,
> > > > >
> > > > > I have a table with an indexed column named "id". The table
> contains
> > > > about
> > > > > 100,000 records.
> > > > >
> > > > > I use "ROWLOCK" lock hint to tell SQL Server not to escalate to
> > > > higher
> > > > > level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
> > > > > Two transactions are as following:
> > > > >
> > > > > --Tran1
> > > > > begin tran
> > > > > update dbo.table1 with (rowlock) set someValue=0 where id=1000
> > > > > waitfor delay '00:00:10'
> > > > > commit tran
> > > > >
> > > > > --Tran2
> > > > > begin tran
> > > > > select * from dbo.table1 where id=1001
> > > > > commit tran
> > > > >
> > > > > If I run Tran1 first and then Tran2, the second transaction got to
> > > > wait
> > > > > until the first one is done.
> > > > >
> > > > > So it seems the page where id=1000 locates was locked, preventing
> > > > id=1001 to
> > > > > be read. Well... ROWLOCK means we want to lock a "row", not a whole
> > > > page,
> > > > > right?
> > > > >
> > > > > Run sp_lock while the Tran1 is running, and I got the result:
> > > > >
> > > > > 66 7 141243558 1 KEY (6d0040d1d33f) X
> GRANT
> > > > > 66 7 141243558 1 PAG 1:1753 IX
> > > > GRANT
> > > > > 66 7 141243558 0 TAB
> > > > IX
> > > > > GRANT
> > > > > 66 7 141243558 1 KEY (6c00321b0c6a) X
> GRANT
> > > > > 67 1 85575343 0 TAB
> > > > IS
> > > > > GRANT
> > > > >
> > > > > As I know, PAG IX lock means only partial of an page is locked.
> It's
> > > > what I
> > > > > expected and, obviously contrary with the result I had.
> > > > >
> > > > > Could anyone explain this to me? If this doesn't work, any other
> way
> > > > to
> > > > > make sure lock only applied to a single row of a table?
> > > > >
> > > > >
> > > > >
> > > > > Thank you in advance
> > > > >
> > > > >
> > > > > Ryan
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||That would be my guess as well. What is the DDL for that table?
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23uYt%238FnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> Is SQL Server using an index for the query? My guess is that SQL Server
has to check a number of
> rows, whether the value is 1000 or not and this is causing the blocking. I
wonder whether SQL Server
> would benefit from a unique or PK constraint in the column in this case?
"I know there can only be
> one row with a certain value, no need look further...".
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Ryan" <ryan@.cradle.com.tw> wrote in message
news:%23p%23A82DnDHA.2000@.TK2MSFTNGP12.phx.gbl...
> > Dear Kalen,
> >
> >
> > > Are you actually seeing another process waiting? As Dan explained, IX
> > locks
> > > are compatible with other IX locks. If you have a case of another
> > > transaction blocking while attempted to access a DIFFERENT row, please
> > post
> > > the sp_lock output showing the process with the WAIT status.
> >
> > Could you please link to :
> > http://sbu.cradle.com.tw/TimeSheet/RC/ROWLOCK.gif
> >
> > I captured the windows of Tran1, Tran2, and the result of running
sp_lock in
> > the above picture.
> >
> > Ryan
> >
> >
> >
> >
> >
> >
> > >
> > > Also, a ROWLOCK hint does not prevent true escalation. It only
encourages
> > > SQL Server to start with row (or key)locks, but if the conditions are
> > right
> > > and enough rows are locked, SQL Server can ALWAYS escalate to a table
> > lock.
> > > It will never escalate from row to page locks, escalation is only to
table
> > > locks.
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > > news:efP6YjCnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> > > > Thank you for your reply.
> > > >
> > > > > Your example shows that you've successfully acquired row locks on
2
> > > > > different rows. Another SPID can access different rows, even if
on
> > the
> > > > > same page.
> > > >
> > > > I don't get it. The second transaction must wait until the first
one is
> > > > completed. If the first transaction only holds a lock on the row,
> > > id=1000,
> > > > why another SPID got to wait when id=1001 is interested?
> > > >
> > > >
> > > > Ryan
> > > >
> > > >
> > > >
> > > >
> > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > > > news:%230Sep%23imDHA.964@.TK2MSFTNGP10.phx.gbl...
> > > > > > As I know, PAG IX lock means only partial of an page is locked.
> > It's
> > > > > what I
> > > > > > expected and, obviously contrary with the result I had.
> > > > > >
> > > > > > Could anyone explain this to me? If this doesn't work, any
other
> > way
> > > > > to
> > > > > > make sure lock only applied to a single row of a table?
> > > > >
> > > > > An IX lock is not the result of escalation and is not a 'partial'
> > lock.
> > > > > This is an intent-exclusive lock acquired at a higher level to
> > indicate
> > > > > that a more granular lock is also held. IX locks are compatible
with
> > > > > other intent locks.
> > > > >
> > > > > Your example shows that you've successfully acquired row locks on
2
> > > > > different rows. Another SPID can access different rows, even if
on
> > the
> > > > > same page.
> > > > >
> > > > > See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for more
> > info.
> > > > >
> > > > > --
> > > > > Hope this helps.
> > > > >
> > > > > Dan Guzman
> > > > > SQL Server MVP
> > > > >
> > > > > --
> > > > > SQL FAQ links (courtesy Neil Pike):
> > > > >
> > > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > > http://www.sqlserverfaq.com
> > > > > http://www.mssqlserver.com/faq
> > > > > --
> > > > >
> > > > > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > > > > news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > > > > > Hi,
> > > > > >
> > > > > > I have a table with an indexed column named "id". The table
> > contains
> > > > > about
> > > > > > 100,000 records.
> > > > > >
> > > > > > I use "ROWLOCK" lock hint to tell SQL Server not to escalate to
> > > > > higher
> > > > > > level lock like PAGLOCK. But it seems SQL Server ignores my
"hint".
> > > > > > Two transactions are as following:
> > > > > >
> > > > > > --Tran1
> > > > > > begin tran
> > > > > > update dbo.table1 with (rowlock) set someValue=0 where id=1000
> > > > > > waitfor delay '00:00:10'
> > > > > > commit tran
> > > > > >
> > > > > > --Tran2
> > > > > > begin tran
> > > > > > select * from dbo.table1 where id=1001
> > > > > > commit tran
> > > > > >
> > > > > > If I run Tran1 first and then Tran2, the second transaction got
to
> > > > > wait
> > > > > > until the first one is done.
> > > > > >
> > > > > > So it seems the page where id=1000 locates was locked,
preventing
> > > > > id=1001 to
> > > > > > be read. Well... ROWLOCK means we want to lock a "row", not a
whole
> > > > > page,
> > > > > > right?
> > > > > >
> > > > > > Run sp_lock while the Tran1 is running, and I got the result:
> > > > > >
> > > > > > 66 7 141243558 1 KEY (6d0040d1d33f) X
> > GRANT
> > > > > > 66 7 141243558 1 PAG 1:1753 IX
> > > > > GRANT
> > > > > > 66 7 141243558 0 TAB
> > > > > IX
> > > > > > GRANT
> > > > > > 66 7 141243558 1 KEY (6c00321b0c6a) X
> > GRANT
> > > > > > 67 1 85575343 0 TAB
> > > > > IS
> > > > > > GRANT
> > > > > >
> > > > > > As I know, PAG IX lock means only partial of an page is locked.
> > It's
> > > > > what I
> > > > > > expected and, obviously contrary with the result I had.
> > > > > >
> > > > > > Could anyone explain this to me? If this doesn't work, any
other
> > way
> > > > > to
> > > > > > make sure lock only applied to a single row of a table?
> > > > > >
> > > > > >
> > > > > >
> > > > > > Thank you in advance
> > > > > >
> > > > > >
> > > > > > Ryan
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sorry, I don't follow you. To see ROWLOCK in action, we need to provide one
or more index for SQL Server to lock certain key ranges, right? In this
case, the best candidate seems to be the column "id", a non-clustered
primary key.
The DDL is as shown below. For simplicity, I removed some update triggers,
columns and associated indexes. To make sure the triggers of the table do
not affect the result, I removed them and had the same outcome.
To view the Estimated Execution Plans and all other figures, please visit
the following URL:
http://sbu.cradle.com.tw/Newsgroup/ROWLOCK.htm
CREATE TABLE [dbo].[prj_PDBA] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[job_type] [varchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[pricingType] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prj_PDBA] ADD
CONSTRAINT [PK_prj_PDBA] PRIMARY KEY NONCLUSTERED
(
[id]
) ON [PRIMARY]
GO
Thank you
Ryan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u%23bVi8InDHA.2512@.TK2MSFTNGP09.phx.gbl...
> That would be my guess as well. What is the DDL for that table?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:%23uYt%238FnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> > Is SQL Server using an index for the query? My guess is that SQL Server
> has to check a number of
> > rows, whether the value is 1000 or not and this is causing the blocking.
I
> wonder whether SQL Server
> > would benefit from a unique or PK constraint in the column in this case?
> "I know there can only be
> > one row with a certain value, no need look further...".
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Ryan" <ryan@.cradle.com.tw> wrote in message
> news:%23p%23A82DnDHA.2000@.TK2MSFTNGP12.phx.gbl...
> > > Dear Kalen,
> > >
> > >
> > > > Are you actually seeing another process waiting? As Dan explained,
IX
> > > locks
> > > > are compatible with other IX locks. If you have a case of another
> > > > transaction blocking while attempted to access a DIFFERENT row,
please
> > > post
> > > > the sp_lock output showing the process with the WAIT status.
> > >
> > > Could you please link to :
> > > http://sbu.cradle.com.tw/TimeSheet/RC/ROWLOCK.gif
> > >
> > > I captured the windows of Tran1, Tran2, and the result of running
> sp_lock in
> > > the above picture.
> > >
> > > Ryan
> > >
> > >
> > >
> > >
> > >
> > >
> > > >
> > > > Also, a ROWLOCK hint does not prevent true escalation. It only
> encourages
> > > > SQL Server to start with row (or key)locks, but if the conditions
are
> > > right
> > > > and enough rows are locked, SQL Server can ALWAYS escalate to a
table
> > > lock.
> > > > It will never escalate from row to page locks, escalation is only to
> table
> > > > locks.
> > > >
> > > > --
> > > > HTH
> > > > --
> > > > Kalen Delaney
> > > > SQL Server MVP
> > > > www.SolidQualityLearning.com
> > > >
> > > >
> > > > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > > > news:efP6YjCnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> > > > > Thank you for your reply.
> > > > >
> > > > > > Your example shows that you've successfully acquired row locks
on
> 2
> > > > > > different rows. Another SPID can access different rows, even if
> on
> > > the
> > > > > > same page.
> > > > >
> > > > > I don't get it. The second transaction must wait until the first
> one is
> > > > > completed. If the first transaction only holds a lock on the row,
> > > > id=1000,
> > > > > why another SPID got to wait when id=1001 is interested?
> > > > >
> > > > >
> > > > > Ryan
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > > > > news:%230Sep%23imDHA.964@.TK2MSFTNGP10.phx.gbl...
> > > > > > > As I know, PAG IX lock means only partial of an page is
locked.
> > > It's
> > > > > > what I
> > > > > > > expected and, obviously contrary with the result I had.
> > > > > > >
> > > > > > > Could anyone explain this to me? If this doesn't work, any
> other
> > > way
> > > > > > to
> > > > > > > make sure lock only applied to a single row of a table?
> > > > > >
> > > > > > An IX lock is not the result of escalation and is not a
'partial'
> > > lock.
> > > > > > This is an intent-exclusive lock acquired at a higher level to
> > > indicate
> > > > > > that a more granular lock is also held. IX locks are compatible
> with
> > > > > > other intent locks.
> > > > > >
> > > > > > Your example shows that you've successfully acquired row locks
on
> 2
> > > > > > different rows. Another SPID can access different rows, even if
> on
> > > the
> > > > > > same page.
> > > > > >
> > > > > > See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for
more
> > > info.
> > > > > >
> > > > > > --
> > > > > > Hope this helps.
> > > > > >
> > > > > > Dan Guzman
> > > > > > SQL Server MVP
> > > > > >
> > > > > > --
> > > > > > SQL FAQ links (courtesy Neil Pike):
> > > > > >
> > > > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > > > http://www.sqlserverfaq.com
> > > > > > http://www.mssqlserver.com/faq
> > > > > > --
> > > > > >
> > > > > > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > > > > > news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > > > > > > Hi,
> > > > > > >
> > > > > > > I have a table with an indexed column named "id". The table
> > > contains
> > > > > > about
> > > > > > > 100,000 records.
> > > > > > >
> > > > > > > I use "ROWLOCK" lock hint to tell SQL Server not to escalate
to
> > > > > > higher
> > > > > > > level lock like PAGLOCK. But it seems SQL Server ignores my
> "hint".
> > > > > > > Two transactions are as following:
> > > > > > >
> > > > > > > --Tran1
> > > > > > > begin tran
> > > > > > > update dbo.table1 with (rowlock) set someValue=0 where id=1000
> > > > > > > waitfor delay '00:00:10'
> > > > > > > commit tran
> > > > > > >
> > > > > > > --Tran2
> > > > > > > begin tran
> > > > > > > select * from dbo.table1 where id=1001
> > > > > > > commit tran
> > > > > > >
> > > > > > > If I run Tran1 first and then Tran2, the second transaction
got
> to
> > > > > > wait
> > > > > > > until the first one is done.
> > > > > > >
> > > > > > > So it seems the page where id=1000 locates was locked,
> preventing
> > > > > > id=1001 to
> > > > > > > be read. Well... ROWLOCK means we want to lock a "row", not a
> whole
> > > > > > page,
> > > > > > > right?
> > > > > > >
> > > > > > > Run sp_lock while the Tran1 is running, and I got the result:
> > > > > > >
> > > > > > > 66 7 141243558 1 KEY (6d0040d1d33f) X
> > > GRANT
> > > > > > > 66 7 141243558 1 PAG 1:1753
IX
> > > > > > GRANT
> > > > > > > 66 7 141243558 0 TAB
> > > > > > IX
> > > > > > > GRANT
> > > > > > > 66 7 141243558 1 KEY (6c00321b0c6a) X
> > > GRANT
> > > > > > > 67 1 85575343 0 TAB
> > > > > > IS
> > > > > > > GRANT
> > > > > > >
> > > > > > > As I know, PAG IX lock means only partial of an page is
locked.
> > > It's
> > > > > > what I
> > > > > > > expected and, obviously contrary with the result I had.
> > > > > > >
> > > > > > > Could anyone explain this to me? If this doesn't work, any
> other
> > > way
> > > > > > to
> > > > > > > make sure lock only applied to a single row of a table?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Thank you in advance
> > > > > > >
> > > > > > >
> > > > > > > Ryan
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||"Ryan" <ryan@.cradle.com.tw> wrote in message
news:ONizxlQnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Sorry, I don't follow you. To see ROWLOCK in action, we need to provide
one
> or more index for SQL Server to lock certain key ranges, right? In this
> case, the best candidate seems to be the column "id", a non-clustered
> primary key.
> The DDL is as shown below. For simplicity, I removed some update
triggers,
> columns and associated indexes. To make sure the triggers of the table do
> not affect the result, I removed them and had the same outcome.
> To view the Estimated Execution Plans and all other figures, please visit
> the following URL:
> http://sbu.cradle.com.tw/Newsgroup/ROWLOCK.htm
> CREATE TABLE [dbo].[prj_PDBA] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [job_type] [varchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
> [pricingType] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[prj_PDBA] ADD
> CONSTRAINT [PK_prj_PDBA] PRIMARY KEY NONCLUSTERED
> (
> [id]
> ) ON [PRIMARY]
> GO
>
Ok i think I've got it.
You have some other non-primary clustered index on this table. So this
update is likely moving the data row from under one clustered index key to
another, and requires locks on both keys.
Notice that Transaction2 is using a Bookmark Lookup in its query plan. A
Bookmark Lookup on a table with a clustered index resolves not to a row
locator, but to a clustered index key. Transaction1 has the has that
clustered index key exclusively locked (notice the two KEY locks).
To fix this, make PK_prj_PDBA clustered. Then the update will not require
data rows to be physically moved at all, and will require only a single KEY
lock on the key containing the updated row.
David|||You are right ! Thank you so much, and thank to all others.
Ryan
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23yB0gESnDHA.2272@.tk2msftngp13.phx.gbl...
> "Ryan" <ryan@.cradle.com.tw> wrote in message
> news:ONizxlQnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> > Sorry, I don't follow you. To see ROWLOCK in action, we need to provide
> one
> > or more index for SQL Server to lock certain key ranges, right? In this
> > case, the best candidate seems to be the column "id", a non-clustered
> > primary key.
> >
> > The DDL is as shown below. For simplicity, I removed some update
> triggers,
> > columns and associated indexes. To make sure the triggers of the table
do
> > not affect the result, I removed them and had the same outcome.
> >
> > To view the Estimated Execution Plans and all other figures, please
visit
> > the following URL:
> > http://sbu.cradle.com.tw/Newsgroup/ROWLOCK.htm
> >
> > CREATE TABLE [dbo].[prj_PDBA] (
> > [id] [int] IDENTITY (1, 1) NOT NULL ,
> > [job_type] [varchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
> > [pricingType] [bit] NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> >
> > ALTER TABLE [dbo].[prj_PDBA] ADD
> > CONSTRAINT [PK_prj_PDBA] PRIMARY KEY NONCLUSTERED
> > (
> > [id]
> > ) ON [PRIMARY]
> > GO
> >
> Ok i think I've got it.
> You have some other non-primary clustered index on this table. So this
> update is likely moving the data row from under one clustered index key to
> another, and requires locks on both keys.
> Notice that Transaction2 is using a Bookmark Lookup in its query plan. A
> Bookmark Lookup on a table with a clustered index resolves not to a row
> locator, but to a clustered index key. Transaction1 has the has that
> clustered index key exclusively locked (notice the two KEY locks).
> To fix this, make PK_prj_PDBA clustered. Then the update will not require
> data rows to be physically moved at all, and will require only a single
KEY
> lock on the key containing the updated row.
> David
>
>|||I finally found the truth, with the helpful hints from the above threads.
The table has an update trigger, which will update some other rows in the
same table with a where:() predicate using the clustered key to locate the
desired rows.
I couldn't find the fact because I simply add a command "RETURN" in the
beginng of the trigger. That's a way I "disabled" the trigger. After
pysically deleting the trigger, I found ROWLOCK was in action. But, I can't
figure it out why this way doesn't work as expected at the moment of
writing.
For your reference.
Ryan|||"Ryan" <ryan@.cradle.com.tw> wrote in message
news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a table with an indexed column named "id". The table contains
about
> 100,000 records.
> I use "ROWLOCK" lock hint to tell SQL Server not to escalate to higher
> level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
> Two transactions are as following:
> --Tran1
> begin tran
> update dbo.table1 with (rowlock) set someValue=0 where id=1000
> waitfor delay '00:00:10'
> commit tran
> --Tran2
> begin tran
> select * from dbo.table1 where id=1001
> commit tran
>
You've got key locks not row locks. table1 appears to be clustered.
Post the DDL for these tables, and a script to reproduce the effect.
David|||> You've got key locks not row locks. table1 appears to be clustered.
> Post the DDL for these tables, and a script to reproduce the effect.
>
Oops, I thought this thread looked familiar.
David