Very Computer

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

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

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

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.