469,889 Members | 1,382 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,889 developers. It's quick & easy.

Locking a SQL Server Record from ASP + Batch Transactions

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!
Sep 27 '07 #1
3 1720
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" <da***@NOSPAMdbarber.comwrote in message
news:Gs******************************@comcast.com. ..
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!

Sep 27 '07 #2
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" <ai**@xxnoemailxx.comwrote in message
news:Pq******************************@bt.com...
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" <da***@NOSPAMdbarber.comwrote in message
news:Gs******************************@comcast.com. ..
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!



Oct 3 '07 #3
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.
Oct 3 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by Alban Hertroys | last post: by
3 posts views Thread by Elliott | last post: by
12 posts views Thread by Puvendran | last post: by
16 posts views Thread by Nid | last post: by
1 post views Thread by David McGeorge | last post: by
13 posts views Thread by Jeff Davis | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.