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
Showing posts with label lock. Show all posts
Showing posts with label lock. Show all posts
Friday, March 30, 2012
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
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
Wednesday, March 28, 2012
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
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
Wednesday, March 21, 2012
Row lock versus Page lock in SQL 2000.
Hi
We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.
We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.
Consider a scenario in which User 1 is clicking on a button which calls a SQL stored procedure. This procedure selects Group A of records of Database Page1.
At the same time if User 2 also clicks the same button which calls same SQL stored procedure. This procedure selects Group B of records of Database Page1.
So, its the same Page1 but different sets of records. At this moment, both the calls have shared locked on the Page1 inside the procedure.
Now, in call 1, inside the procedure after selecting Group A of records, the next statement is and update to those records. As soon as update statement executes, SQL Server throws a deadlock exception as follows :
Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
We are able to understand why its happening. Its because, Group A and Group B of records are on the same Page1. But both the users have shared lock on the Page1. So, no one gets the exclusive lock in records for update, even though, the records are different.
How can I resolve this issue? How can I get lock on wanted rows instead of entire page?
Please advice. Thanks a bunch.
PankajWhy are you implementing *read* locks? Concurrent access via pessimistic locking is very low. Try implementing optimistic locking.
If you absolutely cannot implement optimistic locking (think 3 times about this before deciding on pessimistic locking), then you can make it so user 2 doesn't get to see the records at all and instead gets a message saying try back later.sql
Row Lock On Update Statement
What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,
You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,
You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
Row Lock On Update Statement
What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
Row Lock On Update Statement
What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
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:
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
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
Subscribe to:
Posts (Atom)