Locking a SQL Server Record from ASP + Batch Transactions

[David C. Barber]

How do you lock a record in SQL Server from ASP 2? I need to read the
record, allow the user to edit it, and then have them click Save and rewrite
it. Obviously I don't want anyone else getting into this record while the
user has it up. I don't see how to have SQL Server lock this record for me
since the connection drops the moment the page is written, and would prefer
to avoid the kludge of adding and handling reservation fields to each record
if there's a better way.

Also, although I've set up batch transactions in VB6 w/o problems, I'm not
sure how to best do that in VB NET using a Sql Server Control. I'm not
sure if I have the same access to the connection that I do on VB6 ADO.

Thanks!

 

[Aidy]

What you're doing is basically a bad idea. What happens if the user locks
the record then goes away for lunch, shuts their browser down, has a power
cut etc? You should never hold locks across page processes, you'll have to
kludge it by adding a "editing by" type field or some other solution.
You're main problem is going to be issues with how long you want these
records to remain locked for.

 

[David C. Barber]

Aidy,

Thanks for the thought, and yes that can be a hazard. So can having two
people trying to edit the same record at the same time.

I would expect that if someone locks a record and goes away for lunch, the
session terminates in 20 minutes, any connection to the SQL Server database
goes away, and the lock drops out. The problem here is that standard
database locking may not work at all since the connection actually drops the
moment the page is written, since this is a stateless system.

And btw, you solution of setting up a reservation field to kludge it has
exactly the same problem as locking the record. Someone goes away with the
reservation field set, and no one else is allowed to get to it.

 

[Aidy]

And btw, you solution of setting up a reservation field to kludge it has

exactly the same problem as locking the record. Someone goes away with
the
reservation field set, and no one else is allowed to get to it.

Yes but that is a logical problem. Such systems could tell you "This record
is locked by so-and-so and has been for 25 minutes. Do you want to override
their lock?". Whereas if you have a SQL lock people are physically
prevented from accessing the data at all until a sysadmin comes along and
starts killing processes.