ROWLOCK usage

hi. i don't have much experience with locking using lock hints so wondered i
f
someone could help me with usage of ROWLOCK. i am writing a number of procs
which will perform validation on data prior to performing updates. i need
read consistency for the duration of these procs whilst guaranteeing maximum
concurrency. therefore i intend to use the following pattern:
create procedure MyProcedure
@.MyParam int
as
-- wrap in transaction
begin transaction
-- obtain lock
select 1
from MyTable (HOLDLOCK, UPDLOCK)
where MyPKColumn = @.MyParam
-- other operations here involving SELECTs on MyTable
-- perform update
update MyTable
set MyOthercolumn = 'NewValue'
where MyPKColumn = @.MyParam
-- release all locks
commit transaction
what i don't like about this is that i am beginning my transaction earlier
than i would prefer, but otherwise this appears to meet my requirements. is
this a good strategy, or is there a better way? what issues might i face?
many thanks
kh> what i don't like about this is that i am beginning my transaction earlier
> than i would prefer, but otherwise this appears to meet my requirements.
> is
> this a good strategy, or is there a better way? what issues might i face?
You are probably also locking pages (or perhaps even the entire table). IF
you take this path you might want to look into specifying ROWLOCK so that
you only lock the particular row that you are working on.
It seems like you are trying to reinvent the wheel.
You don't account for the situation where the data has changed since the
user initially retrieved the data.
User A retrieves the data for PKcol = 1
User B retrieves the data for PKcol = 1
User A goes to lunch
User B starts updating the data (within the application GUI)
Userr B hits "save" and writes the data to the database
User A comes back from lunch, finishes updating the data within the GUI, and
clicks save.
The data that User B entered is overwritten by User A.
One way around this problem is to pass the old and new values to the stored
procedure. The WHERE clause would use the primary key and it would compare
the @.old params to the data that is in the table. If @.@.rowcount = 0 the
data was different and the update did not happen.
Now that I have you worried about that type of concurrency issue, lets get
back to your validation question.
Can't you validate data within the GUI?
If not you should perform data validation (I assume that this is the "--
other operations here involving SELECTs on MyTable" outside of a
transaction. Heck, I don't even see why you need a transaction in this
stored procedure. It does not seem to buy you anything.
I don't know what type of data validation you need to do. Lets say that you
cannot have multiple UserNames or FileNames within a table. You could do
something like this before update statement. The RETURN will cause the
stored procedure to end. It will also pass back (via the return code) the
value within the parens.
--validation check
IF EXISTS (SELECT * FROM dbo.MyTable WHERE UserName = @.MyUserName )
BEGIN
RETURN (1)
END
--validation check
IF EXISTS (SELECT * FROM dbo.MyTable WHERE FileName= @.MyFileName )
BEGIN
RETURN (2)
END
--everything is a-ok, lets update
UPDATE dbo.MyTable SET MyOthercolumn = 'NewValue'
WHERE MyPKColumn = @.MyParam
RETURN (0)
Keith Kratochvil
"kh" <kh@.newsgroups.nospam> wrote in message
news:FBDE5CBC-4CB8-4CE2-8928-13BB3624F0C4@.microsoft.com...
> hi. i don't have much experience with locking using lock hints so wondered
> if
> someone could help me with usage of ROWLOCK. i am writing a number of
> procs
> which will perform validation on data prior to performing updates. i need
> read consistency for the duration of these procs whilst guaranteeing
> maximum
> concurrency. therefore i intend to use the following pattern:
> create procedure MyProcedure
> @.MyParam int
> as
> -- wrap in transaction
> begin transaction
> -- obtain lock
> select 1
> from MyTable (HOLDLOCK, UPDLOCK)
> where MyPKColumn = @.MyParam
> -- other operations here involving SELECTs on MyTable
> -- perform update
> update MyTable
> set MyOthercolumn = 'NewValue'
> where MyPKColumn = @.MyParam
> -- release all locks
> commit transaction
> what i don't like about this is that i am beginning my transaction earlier
> than i would prefer, but otherwise this appears to meet my requirements.
> is
> this a good strategy, or is there a better way? what issues might i face?
> many thanks
> kh
>|||keith. many thanks. some notes for clarity:

> You are probably also locking pages (or perhaps even the entire table). I
F
> you take this path you might want to look into specifying ROWLOCK so that
> you only lock the particular row that you are working on.
sorry, copy and paste error: the lock hints should of course be (HOLDLOCK,
ROWLOCK) and since I am selecting using the Primary Key I am (hopefully) onl
y
locking a single record.

> You don't account for the situation where the data has changed since the
> user initially retrieved the data. <snip>
there is no user access to the database accept via our app server. users can
go for lunch as often as they like, data will never be left in an uncommitte
d
state other than during stored procedure execution. the usage of ROWLOCK
hopefully avoids the situation you describe since it is only used during wel
l
defined units of execution.

> One way around this problem is to pass the old and new values to the store
d
> procedure. The WHERE clause would use the primary key and it would compar
e
> the @.old params to the data that is in the table. If @.@.rowcount = 0 the
> data was different and the update did not happen.
i am intentionally taking a 'pessimistic concurrency' approach here

> Can't you validate data within the GUI? <snip>
the validation involves selects and inserts into other tables (auditing,
etc) and relates to the requirements of downstream applications rather than
business rules within our own application. it is therefore not appropriate t
o
perform this validation within our UI or app server.

> Heck, I don't even see why you need a transaction in this
> stored procedure. It does not seem to buy you anything.
the only reason that the validation takes place within a transaction is so
that the ROWLOCK is held and i can guarantee that the data has not changed
between the beginning of the validation and the ultimate commit of this data
to the database.
kh|||> users can go for lunch as often as they like
That sounds great. I would like to take 3 or 4 lunches per day!

> the only reason that the validation takes place within a transaction is so
> that the ROWLOCK is held and i can guarantee that the data has not changed
> between the beginning of the validation and the ultimate commit of this
> data
> to the database.
That sounds reasonable. You know your system better than any of us. I
guess you are taking the correct approach.
Keith Kratochvil|||cheers keith. so in summary:
- i know my app better than you
- you know sql server better than me
- my users will shortly need a strict exercise regime
kh

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

ROWLOCK hint does not seem to have any effect

Hi
Here is the situation.
Many clients simulteneously update the same table, but *never*
the same rows. Each client has its *own* subset of rows.
So there is theoretically no concurrency problem.
Yet we are having locking issues.
We use ROWLOCK hint but it looks like it does not do anything.
Here is my simple test of it.
From one connection, I begin a transaction and update a record in a table
with the ROWLOCK hint.
Then I leave it as is
and
From another connection, I try to update a different record in the same
table also with the ROWLOCK hint.
Second command does not do anything until a transaction in the first
connection is commited.
If it is being locked by row, why is this happenning ?
Thanks
Alex
Hi
If SQL Server has to do a Table or Range Scan to get to data, the 2
operations may step on each other.
Correct indexing, espacially clustered indexes can help a lot.
Post DML and DDL so that we can look at it.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alex" wrote:

> Hi
> Here is the situation.
> Many clients simulteneously update the same table, but *never*
> the same rows. Each client has its *own* subset of rows.
> So there is theoretically no concurrency problem.
> Yet we are having locking issues.
> We use ROWLOCK hint but it looks like it does not do anything.
> Here is my simple test of it.
> From one connection, I begin a transaction and update a record in a table
> with the ROWLOCK hint.
> Then I leave it as is
> and
> From another connection, I try to update a different record in the same
> table also with the ROWLOCK hint.
> Second command does not do anything until a transaction in the first
> connection is commited.
> If it is being locked by row, why is this happenning ?
> Thanks
> Alex
>
>
>
sql

ROWLOCK hint does not seem to do anything

Hi
Here is the situation.
Hi
Here is the situation.
Many clients simulteneously update the same table, but *never*
the same rows. Each client has its *own* subset of rows.
So there is theoretically no concurrency problem.
Yet we are having locking issues.
We use ROWLOCK hint but it looks like it does not do anything.
Here is my simple test of it.
From one connection, I begin a transaction and update a record in a table
with the ROWLOCK hint.
Then I leave it as is
and
From another connection, I try to update a different record in the same
table also with the ROWLOCK hint.
Second command does not do anything until a transaction in the first
connection is commited.
If it is being locked by row, why is this happenning ?
Thanks
AlexAlex
SQL Server will assign ROWLOCK by default
Read Vyas's article describing row level security
http://vyaskn.tripod.com/ row_level...as
es.htm
"Alex" <alex@.nospam.com> wrote in message
news:d6edfg$cmc$1@.lust.ihug.co.nz...
> Hi
> Here is the situation.
> Hi
> Here is the situation.
> Many clients simulteneously update the same table, but *never*
> the same rows. Each client has its *own* subset of rows.
> So there is theoretically no concurrency problem.
> Yet we are having locking issues.
> We use ROWLOCK hint but it looks like it does not do anything.
> Here is my simple test of it.
> From one connection, I begin a transaction and update a record in a table
> with the ROWLOCK hint.
> Then I leave it as is
> and
> From another connection, I try to update a different record in the same
> table also with the ROWLOCK hint.
> Second command does not do anything until a transaction in the first
> connection is commited.
> If it is being locked by row, why is this happenning ?
> Thanks
> Alex
>
>
>|||On Wed, 18 May 2005 13:40:34 +1000, Alex wrote:
(snip)
>From one connection, I begin a transaction and update a record in a table
>with the ROWLOCK hint.
>Then I leave it as is
>and
>From another connection, I try to update a different record in the same
>table also with the ROWLOCK hint.
Hi Alex,
Do you have a PRIMARY KEY, UNIQUE or other INDEX on this table? Is the
WHERE clause such that this index can be used to find the row to be
updated by the seecond statement?
If the answer to either of these is "no", then SQL Server has to scan
the whole table for the UPDATE. And it will encounter the locked row
during that process. Repeat the experiment, but with the option to show
execution plan set on. After issuing commit or rollback in the first
connection, the second should finish; now you can check the execution
plan.
Also, if the indexed column itself is changed by the first UPDATE
statement, there will be a lock on that index page as well. This can
also cause a wwait in your second query, if that index is used in the
execution plan.
For more detailed info, more information is definitely needed. A repro
script (CREATE TABLE statements, INSERT statements and the statments you
executed on the two connections) would be ideal. See
www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Rowlock appears to be locking entire table.

Without going into a lot of history, I am using embedded SQL in a Micro-Focus

COBOL application.

I have a need to be able to select a specific row from my table and lock that row until I release it.

I have successfully used a declare cursor with a rowlock hint that does lock that row, unfortunately

it also locks the entire table.

I know that SQL Server is best utilized by allowing it it to manage locks, but this is a unique situation

and all I want is a clear answer as to how this should work when I have to do it.

here is a sample of what I have tried. I'm hoping someone has successfully done something

like this (maybe not even in cobol).

EXEC SQL DECLARE TECHLK CURSOR FOR
select
TECH_ID
from TECH_REC
with (rowlock,nowait)
where (TECH_ID = :SQL-TECH-ID)
END-EXEC.

EXEC SQL
OPEN TECHLK
END-EXEC.

EXEC SQL
FETCH TECHLK INTO
:SQL-TECH-ID
END-EXEC.

any help would be appreciated.. thx, Ray Dodson

1. How did you identify that entire table is locked?

2. What queries does your application actually sent to the server? You can figure that out using Profile tool.

3. SQL Server maintain record lock granularity untill it has enough resources for that. Otherwise lock granularity escalated to the table level.

|||

This reply asked more questions than it answered.

1. I used the management item in seql server enterprise manager to see the

locking status.

Actually, I found after this post was sent that my keys were not set up correctly (imported

from another database). that appears to be why I was locking the entire table. I have rectified that

and things seem to be working correctly now.

thanks for you time... Ray Dodson

ROWLOCK

hi everone
if we have a table with huge amount of data in it
how much performance loss will occur (%) , if we use rowlock in our queries
on that table
Thanks in advance
Erdem KEMER
The only way to tell is to test... Most people let SQLs optimizer choose the
locking unless you are seeing some particular problem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"erdem" <erdem@.kulube.net> wrote in message
news:%23DIxxKlcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our
> queries on that table
> Thanks in advance
> Erdem KEMER
>
|||erdem,
As Wayne suggested, I would leave the locking hints out unless you are
experiencing poor index selection from the optimiser, or poor
performance. If row locks become too expensive, SQL Server will escalate
to a table lock. If you are finding that row locks are too expensive and
table locks do not cut it, try specifying page locks. I would have
thought this to be unnecessary though.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
erdem wrote:
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our queries
> on that table
> Thanks in advance
> Erdem KEMER
>
|||As another poster already pointed out, you shouldn't use locking hints
without a very good reason; SQL Server does a pretty good job of choosing the
proper lock for the job.
But you can estimate the performance impact if you know your row size and
the number of records being updated in each query. For instance, assume
rows of 800 bytes (incl. overhead), so 10 rows fit on each 8K data page. If
your update affects one hundred rows, then row locking would incur 10X the
overhead as page locking (and 100X the overhead of a table lock). That
doesn't mean ten times slower, as lock overhead is only a fraction of the
total work done in an update, but the impact is still significant, especially
on a cpu-bound server.
Rowlocks increase concurrency though, so the performance of other queries
running may increase substantially. In the case above, rowlocks would reduce
lock contention by other processes by (roughly) 90%. Of course, if the
server is thrashing mightily trying to acquire and release tens of thousands
of rowlocks, all queries will slow down, and it might wind up being slower
after all.
This is why SQL Server usually does a better job of choosing the locks than
we do. It makes a decision based on the actual size of the update, current
available memory, etc. Overriding this is useful only in rare cases.

ROWLOCK

hi everone
if we have a table with huge amount of data in it
how much performance loss will occur (%) , if we use rowlock in our queries
on that table
Thanks in advance
Erdem KEMERThe only way to tell is to test... Most people let SQLs optimizer choose the
locking unless you are seeing some particular problem
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"erdem" <erdem@.kulube.net> wrote in message
news:%23DIxxKlcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our
> queries on that table
> Thanks in advance
> Erdem KEMER
>|||erdem,
As Wayne suggested, I would leave the locking hints out unless you are
experiencing poor index selection from the optimiser, or poor
performance. If row locks become too expensive, SQL Server will escalate
to a table lock. If you are finding that row locks are too expensive and
table locks do not cut it, try specifying page locks. I would have
thought this to be unnecessary though.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
erdem wrote:
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our queries
> on that table
> Thanks in advance
> Erdem KEMER
>|||As another poster already pointed out, you shouldn't use locking hints
without a very good reason; SQL Server does a pretty good job of choosing the
proper lock for the job.
But you can estimate the performance impact if you know your row size and
the number of records being updated in each query. For instance, assume
rows of 800 bytes (incl. overhead), so 10 rows fit on each 8K data page. If
your update affects one hundred rows, then row locking would incur 10X the
overhead as page locking (and 100X the overhead of a table lock). That
doesn't mean ten times slower, as lock overhead is only a fraction of the
total work done in an update, but the impact is still significant, especially
on a cpu-bound server.
Rowlocks increase concurrency though, so the performance of other queries
running may increase substantially. In the case above, rowlocks would reduce
lock contention by other processes by (roughly) 90%. Of course, if the
server is thrashing mightily trying to acquire and release tens of thousands
of rowlocks, all queries will slow down, and it might wind up being slower
after all.
This is why SQL Server usually does a better job of choosing the locks than
we do. It makes a decision based on the actual size of the update, current
available memory, etc. Overriding this is useful only in rare cases.

ROWLOCK

hi everone
if we have a table with huge amount of data in it
how much performance loss will occur (%) , if we use rowlock in our queries
on that table
Thanks in advance
Erdem KEMERThe only way to tell is to test... Most people let SQLs optimizer choose the
locking unless you are seeing some particular problem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"erdem" <erdem@.kulube.net> wrote in message
news:%23DIxxKlcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our
> queries on that table
> Thanks in advance
> Erdem KEMER
>|||erdem,
As Wayne suggested, I would leave the locking hints out unless you are
experiencing poor index selection from the optimiser, or poor
performance. If row locks become too expensive, SQL Server will escalate
to a table lock. If you are finding that row locks are too expensive and
table locks do not cut it, try specifying page locks. I would have
thought this to be unnecessary though.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
erdem wrote:
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our querie
s
> on that table
> Thanks in advance
> Erdem KEMER
>|||As another poster already pointed out, you shouldn't use locking hints
without a very good reason; SQL Server does a pretty good job of choosing th
e
proper lock for the job.
But you can estimate the performance impact if you know your row size and
the number of records being updated in each query. For instance, assume
rows of 800 bytes (incl. overhead), so 10 rows fit on each 8K data page. I
f
your update affects one hundred rows, then row locking would incur 10X the
overhead as page locking (and 100X the overhead of a table lock). That
doesn't mean ten times slower, as lock overhead is only a fraction of the
total work done in an update, but the impact is still significant, especiall
y
on a cpu-bound server.
Rowlocks increase concurrency though, so the performance of other queries
running may increase substantially. In the case above, rowlocks would reduc
e
lock contention by other processes by (roughly) 90%. Of course, if the
server is thrashing mightily trying to acquire and release tens of thousands
of rowlocks, all queries will slow down, and it might wind up being slower
after all.
This is why SQL Server usually does a better job of choosing the locks than
we do. It makes a decision based on the actual size of the update, current
available memory, etc. Overriding this is useful only in rare cases.sql

RowLock

I have to lock the row while insertion or at updation when many users
accessthe particular row.i f the first user access the row after at that tim
e
the row should be locked after his updation the row will unlock since many
users not have to updation at the same time.so ihave lock and release the ro
w
in the stored procedureIf I understood your post correctly, you need some sort of check-out
management. This is not provided natively by SQL Server 2000, and can be
achieved in several ways.
If you specify your needs and provide the DDL and some sample data, maybe we
can help you find a solution.
ML|||hi ML,
I will explain in clearly.
By Using frontend(PHP forms)my clients can update a particular row in
particular table at that same time.while updating i have to update th versio
n
number in the table.so i took the maxvalue of that column at that particular
row and update version mumber.while at that time of updating max count two o
r
more clients gets the same value of that the particular column at that same
time.so it will crasing the number.so i have to lock that paricular while on
e
updating if another tries it displays a message some one is updating,so
please wait a moment and try after his updation is over the row lockis
released and another client able to sccess that row and update.
so how can lock that row and release the lock
it is long query,the query depends many so i can't able to send the query
directly
regards,
balakarthik
"ML" wrote:

> If I understood your post correctly, you need some sort of check-out
> management. This is not provided natively by SQL Server 2000, and can be
> achieved in several ways.
> If you specify your needs and provide the DDL and some sample data, maybe
we
> can help you find a solution.
>
> ML|||balakarthik
You have already asked this in .server and already have been given some
references there. Please do not multipost, it make is very confusing for
those people who are trying to help, who don't know what has already been
said.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:E98C44C0-69AC-489A-9B52-D43785C4D684@.microsoft.com...
> hi ML,
> I will explain in clearly.
> By Using frontend(PHP forms)my clients can update a particular row in
> particular table at that same time.while updating i have to update th
> version
> number in the table.so i took the maxvalue of that column at that
> particular
> row and update version mumber.while at that time of updating max count two
> or
> more clients gets the same value of that the particular column at that
> same
> time.so it will crasing the number.so i have to lock that paricular while
> one
> updating if another tries it displays a message some one is updating,so
> please wait a moment and try after his updation is over the row lockis
> released and another client able to sccess that row and update.
>
> so how can lock that row and release the lock
> it is long query,the query depends many so i can't able to send the query
> directly
>
> regards,
> balakarthik
> "ML" wrote:
>|||You need to use SELECT MAX...FROM...WITH(UPDLOCK, HOLDLOCK) to get the
maximum value, and the locks must be held until the INSERT/UPDATE completes.
If you issue INSERT...SELECT MAX...FROM ...WITH(UPDLOCK, HOLDLOCK) then the
INSERT statement provides an implicit transaction. If you issue SELECT
@.localVariable = MAX...FROM...WITH(UPDLOCK, HOLDLOCK) INSERT...VALUES, then
you need to enclose both statements within a transaction. WITH(UPDLOCK,
HOLDLOCK) prevents other transactions from issuing an insert or update that
has a value for the column that is between MAX and infinity until the lock
is released. Both UPDLOCK and HOLDLOCK are required because you need an
update range-lock, not an individual update lock, or a shared range-lock.
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:E98C44C0-69AC-489A-9B52-D43785C4D684@.microsoft.com...
> hi ML,
> I will explain in clearly.
> By Using frontend(PHP forms)my clients can update a particular row in
> particular table at that same time.while updating i have to update th
version
> number in the table.so i took the maxvalue of that column at that
particular
> row and update version mumber.while at that time of updating max count two
or
> more clients gets the same value of that the particular column at that
same
> time.so it will crasing the number.so i have to lock that paricular while
one
> updating if another tries it displays a message some one is updating,so
> please wait a moment and try after his updation is over the row lockis
> released and another client able to sccess that row and update.
>
> so how can lock that row and release the lock
> it is long query,the query depends many so i can't able to send the query
> directly
>
> regards,
> balakarthik
> "ML" wrote:
>
maybe we|||I see.
Let us forget for a minute about rows, columns, tables and databases. Let's
discuss entities.
Let's say your entity is a single document in a drawer. But this is not an
ordinary drawer, since many users can read the same document at any given
moment. However, only one of them should be allowed to change the contents o
f
that document at any given time.
What you need to do is to let other users know that changes to a document
cannot be made if that document is "checked-out".
In a database you need to do something like this:
1) make sure all data access is done through procedures; and
2) make sure procedures cannot make changes to documents that are
checked-out; and
3) make sure the appropriate procedures report check-outs to the database.
One way is to add a column (or two) to the master table (BTW: since I
haven't seen your DDL this is more a guess than a fully valid suggestion):
CheckedOutBy - it holds the username of the person who checked out the
document. When null, the document is not checked out.
(Maybe even CheckedOutSince - it holds the time of the check-out.)
So, in a use case you'd do something like:
when a user gets a document that hasn't been checked-out (CheckOutBy is
null), the reading procedure sets CheckedOutBy to store the name of the
current user; and when a user finishes his changes the procedure then sets
CheckedOutBy back to null;
any user that tries to check out a document that is already checked out by
another user is warned that his changes will not succeed, and the applicatio
n
should prevent any changes to be made.
But, this is just *one* way of doing it.
ML|||Thanks sir i wil try
"ML" wrote:

> I see.
> Let us forget for a minute about rows, columns, tables and databases. Let'
s
> discuss entities.
> Let's say your entity is a single document in a drawer. But this is not an
> ordinary drawer, since many users can read the same document at any given
> moment. However, only one of them should be allowed to change the contents
of
> that document at any given time.
> What you need to do is to let other users know that changes to a document
> cannot be made if that document is "checked-out".
> In a database you need to do something like this:
> 1) make sure all data access is done through procedures; and
> 2) make sure procedures cannot make changes to documents that are
> checked-out; and
> 3) make sure the appropriate procedures report check-outs to the database.
> One way is to add a column (or two) to the master table (BTW: since I
> haven't seen your DDL this is more a guess than a fully valid suggestion):
> CheckedOutBy - it holds the username of the person who checked out the
> document. When null, the document is not checked out.
> (Maybe even CheckedOutSince - it holds the time of the check-out.)
> So, in a use case you'd do something like:
> when a user gets a document that hasn't been checked-out (CheckOutBy is
> null), the reading procedure sets CheckedOutBy to store the name of the
> current user; and when a user finishes his changes the procedure then sets
> CheckedOutBy back to null;
> any user that tries to check out a document that is already checked out by
> another user is warned that his changes will not succeed, and the applicat
ion
> should prevent any changes to be made.
> But, this is just *one* way of doing it.
>
> ML

Row lock - Hold Lock

If I issue a Rowlock and holdlock statements in my select statement, would it
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.
First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.
|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:

> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>
>
|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...[vbcol=seagreen]
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:

> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>
>
|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...[vbcol=seagreen]
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:

Row lock - Hold Lock

If I issue a Rowlock and holdlock statements in my select statement, would it
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> > If I issue a Rowlock and holdlock statements in my select statement, would
> > it
> > still escalate to pagelock or tablelock or it would keep the rowlock until
> > the transaction is done ?.
> >
> > Thanks.
>
>|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
>> First off you can not limit the lock to a row by specifying the hint.
>> That
>> only tells it to start there but it is still free to escalate up to a
>> table
>> if the conditions are right. Locks never escalate from row to page, they
>> always go straight to table if they escalate at all. Adding HOLDLOCK to
>> a
>> select does little or nothing to the way the locks are done. By default
>> SQL
>> Server will lock the row as it is reading it and you don't need a hint to
>> do
>> that. It releases it when it is done reading the row. HOLDLOCK is
>> usually
>> used to hold the locks until the end of a transaction that was started
>> with
>> a BEGIN TRAN and has multiple statements in it. What is the intended
>> purpose
>> of the hint and why are you worried about it escalating? If you have
>> proper
>> indexes and a proper WHERE clause it should never escalate unless you are
>> trying to touch a major portion of the total rows.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> > If I issue a Rowlock and holdlock statements in my select statement,
>> > would
>> > it
>> > still escalate to pagelock or tablelock or it would keep the rowlock
>> > until
>> > the transaction is done ?.
>> >
>> > Thanks.
>>|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> > Thanks for the info.......
> >
> > We have a table with 15 columns. The first column is 'username' that has a
> > clustered index. 12 of the rest of the columns are
> > deleted/inserted/updated
> > by individual users. If the users are running the same process at the same
> > time, each user inserts ~30000 rows into the table after deleting the rows
> > that are belong to them.
> >
> > Out of the profiler, I have seen a few lock escalations. What is the best
> > way to index these columns ?
> >
> > username column is like below;
> >
> > username
> >
> > user1
> > user1
> > user1
> > user1
> > user1
> > user1
> > user2
> > user2
> > user2
> > user2
> > user2
> > user3
> > user3
> > user3
> > user3
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> First off you can not limit the lock to a row by specifying the hint.
> >> That
> >> only tells it to start there but it is still free to escalate up to a
> >> table
> >> if the conditions are right. Locks never escalate from row to page, they
> >> always go straight to table if they escalate at all. Adding HOLDLOCK to
> >> a
> >> select does little or nothing to the way the locks are done. By default
> >> SQL
> >> Server will lock the row as it is reading it and you don't need a hint to
> >> do
> >> that. It releases it when it is done reading the row. HOLDLOCK is
> >> usually
> >> used to hold the locks until the end of a transaction that was started
> >> with
> >> a BEGIN TRAN and has multiple statements in it. What is the intended
> >> purpose
> >> of the hint and why are you worried about it escalating? If you have
> >> proper
> >> indexes and a proper WHERE clause it should never escalate unless you are
> >> trying to touch a major portion of the total rows.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> >> > If I issue a Rowlock and holdlock statements in my select statement,
> >> > would
> >> > it
> >> > still escalate to pagelock or tablelock or it would keep the rowlock
> >> > until
> >> > the transaction is done ?.
> >> >
> >> > Thanks.
> >>
> >>
> >>
>
>|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
>> That doesn't make a lot of sense. You can't Delete or Insert a column.
>> I
>> assume you mean the users Insert, Update or Delete the rows. How many
>> rows
>> in the whole table? If you attempt to Delete 30K rows in a relatively
>> small
>> table then SQL Server will most likely try to take out a table level
>> lock.
>> Does the DELETE include the clustered column in the WHERE clause? You
>> can
>> stop escalation to the table level by always having at least one shared
>> lock
>> in the table. But if you delete the rows in smaller batches you won't
>> escalate as long as they are not all wrapped in a single transaction.
>>
>> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and
>> is
>> efficient.
>> WHILE 1 = 1
>> BEGIN
>> DELETE FROM YourTable WHERE UserName = xxx
>> IF @.@.ROWCOUNT = 0
>> BREAK
>> END
>> SET ROWCOUNT 0
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>> > Thanks for the info.......
>> >
>> > We have a table with 15 columns. The first column is 'username' that
>> > has a
>> > clustered index. 12 of the rest of the columns are
>> > deleted/inserted/updated
>> > by individual users. If the users are running the same process at the
>> > same
>> > time, each user inserts ~30000 rows into the table after deleting the
>> > rows
>> > that are belong to them.
>> >
>> > Out of the profiler, I have seen a few lock escalations. What is the
>> > best
>> > way to index these columns ?
>> >
>> > username column is like below;
>> >
>> > username
>> >
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user2
>> > user2
>> > user2
>> > user2
>> > user2
>> > user3
>> > user3
>> > user3
>> > user3
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> First off you can not limit the lock to a row by specifying the hint.
>> >> That
>> >> only tells it to start there but it is still free to escalate up to a
>> >> table
>> >> if the conditions are right. Locks never escalate from row to page,
>> >> they
>> >> always go straight to table if they escalate at all. Adding HOLDLOCK
>> >> to
>> >> a
>> >> select does little or nothing to the way the locks are done. By
>> >> default
>> >> SQL
>> >> Server will lock the row as it is reading it and you don't need a hint
>> >> to
>> >> do
>> >> that. It releases it when it is done reading the row. HOLDLOCK is
>> >> usually
>> >> used to hold the locks until the end of a transaction that was started
>> >> with
>> >> a BEGIN TRAN and has multiple statements in it. What is the intended
>> >> purpose
>> >> of the hint and why are you worried about it escalating? If you have
>> >> proper
>> >> indexes and a proper WHERE clause it should never escalate unless you
>> >> are
>> >> trying to touch a major portion of the total rows.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> >> > If I issue a Rowlock and holdlock statements in my select statement,
>> >> > would
>> >> > it
>> >> > still escalate to pagelock or tablelock or it would keep the rowlock
>> >> > until
>> >> > the transaction is done ?.
>> >> >
>> >> > Thanks.
>> >>
>> >>
>> >>
>>sql