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
> 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
> 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
> is treated as a transaction.
> 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
> 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
> 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
> 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