Row-locking

Row-locking

Post by cleo » Sat, 16 Mar 2002 13:03:08



My client-server application needs to prevent 2 users from
modifying the same data (ie same record) in a table.  I
always issue a select statement with rowlock for that
particular record before allowing user to edit the data in
the application to ensure that the user is editing the
latest data.

I have tried using rowlock together with "set transaction
isolation level serializable". This still allows both
users to select the same record with rowlock. It only
prevents either user to issue a update statement to that
record. This does not solve my problem as the user who
cannot update changes to the locked record will have
to "search" for the other user who is also making changes
to the same record.

I need a lock that prevents the user from locking the same
record once the record is being locked by somebody else.ie
if first user has issued a select statement with a rowlock
for a particular record,
then a second user who tries to issue a select statement
with rowlock for the same record will not be allowed.

ie this lock should behave like updlock but  does not lock
the whole table.

Is this possible? If Yes, what are the
conditions/statements that I have to issue?

If No, any alternative suggestions/solutions will be most  
welcomed.

Thank you.

Cleo

 
 
 

Row-locking

Post by Matthew Band » Sat, 16 Mar 2002 22:46:45


By default, select statements elease their locks as soon as the select
statement is finished executing.

If you want to hold the locks open for the length of a transaction, you can
use the HOLDLOCK option on the select statement, or you can use the
serializable isolation level.  With either of these options, the locks are
only held for the length of a transaction.  By default, any single statement
is treated as a transaction.

Try:

 set transaction isolation level serializable

and then

BEGIN TRAN

before running your select statement.  This should hold the select
statement's locks until you execute either:

ROLLBACK TRAN

to cancel the transaction
or

COMMIT TRAN

to commit the transaction.

More information on the way locking and the isolation levels work can be
found in the BOL.

Bear in mind that this will hold the lock until you execute the rollback or
commit and that anyone else wanting access to that row (or rows) will be
stuck waiting (forever, by default).  You will probably want to build a lock
timeout error handler into your application.

I hope that this helps.

Matthew Bando


Quote:> My client-server application needs to prevent 2 users from
> modifying the same data (ie same record) in a table.  I
> always issue a select statement with rowlock for that
> particular record before allowing user to edit the data in
> the application to ensure that the user is editing the
> latest data.

> I have tried using rowlock together with "set transaction
> isolation level serializable". This still allows both
> users to select the same record with rowlock. It only
> prevents either user to issue a update statement to that
> record. This does not solve my problem as the user who
> cannot update changes to the locked record will have
> to "search" for the other user who is also making changes
> to the same record.

> I need a lock that prevents the user from locking the same
> record once the record is being locked by somebody else.ie
> if first user has issued a select statement with a rowlock
> for a particular record,
> then a second user who tries to issue a select statement
> with rowlock for the same record will not be allowed.

> ie this lock should behave like updlock but  does not lock
> the whole table.

> Is this possible? If Yes, what are the
> conditions/statements that I have to issue?

> If No, any alternative suggestions/solutions will be most
> welcomed.

> Thank you.

> Cleo


 
 
 

Row-locking

Post by Andrew J. Kell » Sat, 16 Mar 2002 22:51:09


Not that I advocate this method but if you think you need this then try
this:

--USER 1
BEGIN TRAN

SELECT * FROM YourTable WITH (UPDLOCK) WHERE .....

 -- Make sure you have an index and WHERE clause that will allow a row level
lock.

COMMIT TRAN

As long as both users are using the HOLDLOCK hint the row will be locked by
the first user.  Other users reading without the UPDLOCK hint will still be
able to read the row until you actually update it until it is committed.

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


Quote:> My client-server application needs to prevent 2 users from
> modifying the same data (ie same record) in a table.  I
> always issue a select statement with rowlock for that
> particular record before allowing user to edit the data in
> the application to ensure that the user is editing the
> latest data.

> I have tried using rowlock together with "set transaction
> isolation level serializable". This still allows both
> users to select the same record with rowlock. It only
> prevents either user to issue a update statement to that
> record. This does not solve my problem as the user who
> cannot update changes to the locked record will have
> to "search" for the other user who is also making changes
> to the same record.

> I need a lock that prevents the user from locking the same
> record once the record is being locked by somebody else.ie
> if first user has issued a select statement with a rowlock
> for a particular record,
> then a second user who tries to issue a select statement
> with rowlock for the same record will not be allowed.

> ie this lock should behave like updlock but  does not lock
> the whole table.

> Is this possible? If Yes, what are the
> conditions/statements that I have to issue?

> If No, any alternative suggestions/solutions will be most
> welcomed.

> Thank you.

> Cleo

 
 
 

Row-locking

Post by Andrew J. Kell » Sun, 17 Mar 2002 00:24:02


Matthew,

FYI, HOLDLOCK will not stop the other users from selecting the same row
since it only takes out a shared lock.

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


> By default, select statements elease their locks as soon as the select
> statement is finished executing.

> If you want to hold the locks open for the length of a transaction, you
can
> use the HOLDLOCK option on the select statement, or you can use the
> serializable isolation level.  With either of these options, the locks are
> only held for the length of a transaction.  By default, any single
statement
> is treated as a transaction.

> Try:

>  set transaction isolation level serializable

> and then

> BEGIN TRAN

> before running your select statement.  This should hold the select
> statement's locks until you execute either:

> ROLLBACK TRAN

> to cancel the transaction
> or

> COMMIT TRAN

> to commit the transaction.

> More information on the way locking and the isolation levels work can be
> found in the BOL.

> Bear in mind that this will hold the lock until you execute the rollback
or
> commit and that anyone else wanting access to that row (or rows) will be
> stuck waiting (forever, by default).  You will probably want to build a
lock
> timeout error handler into your application.

> I hope that this helps.

> Matthew Bando



> > My client-server application needs to prevent 2 users from
> > modifying the same data (ie same record) in a table.  I
> > always issue a select statement with rowlock for that
> > particular record before allowing user to edit the data in
> > the application to ensure that the user is editing the
> > latest data.

> > I have tried using rowlock together with "set transaction
> > isolation level serializable". This still allows both
> > users to select the same record with rowlock. It only
> > prevents either user to issue a update statement to that
> > record. This does not solve my problem as the user who
> > cannot update changes to the locked record will have
> > to "search" for the other user who is also making changes
> > to the same record.

> > I need a lock that prevents the user from locking the same
> > record once the record is being locked by somebody else.ie
> > if first user has issued a select statement with a rowlock
> > for a particular record,
> > then a second user who tries to issue a select statement
> > with rowlock for the same record will not be allowed.

> > ie this lock should behave like updlock but  does not lock
> > the whole table.

> > Is this possible? If Yes, what are the
> > conditions/statements that I have to issue?

> > If No, any alternative suggestions/solutions will be most
> > welcomed.

> > Thank you.

> > Cleo

 
 
 

1. Row lock automagically being escalated to Table Locks

Hi ALL

Environment
NT 4.0 SP5, sqlserver 7.0  sp1, MTS, VB COM objs, ADO 2.1
This one is giving me some extra gray......
We have an order entry system, where we want to "create" invoices from about
15000- 20000 orders
at a time we want this to be a single transaction speed is not the issue
concurrency IS we DO not want
SQL to escalate the lock to table, but as we watch with sp_lock, rows lock
occur for the first 2-3000
rows then it becomes a table lock.
I have read that if a query involves a table scan then it will escalate to a
table, that is why we have the hint to use the PK_OrderMain index, I thought
sp_indexoption was going to help but all you can do is stop row/page
locks does any one know how to stop sqlserver from automagically putting
table locks on ??????

our VB code looks some thing like this

sql =    SELECT  blah FROM OrderMain WITH (UPDLOCK,INDEX=PK_OrderMain)
    WHERE OrderMain.OrderID in
    (SELECT OrderMain.OrderID FROM OrderMain WITH (READCOMMITTED)
    WHERE OrderMain.OrderStatus='A'

rs.open sql .....
while not rs.eof
    ...process orders
loop

if not errorflag
    rs.updatebatch
end if

2. Posting from the Informix-list

3. Row locking versus page locking

4. sql to oracle

5. SYBASE page locks outperform ORACLE row locks

6. Having problem with creating oracle database on NFS mounted datafiles

7. Row-locking instead of page-locking ?

8. Repost: database/table/row locking

9. Row locking and serializability

10. SQL Row Locking?

11. Row Locking In a Stored Procedure

12. Rows Locked