Locking a SQL Server Record from ASP + Batch Transactions

Discussion in 'ASP .Net' started by David C. Barber, Sep 27, 2007.

  1. 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!
    David C. Barber, Sep 27, 2007
    #1
    1. Advertising

  2. David C. Barber

    Aidy Guest

    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" <> wrote in message
    news:...
    > 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, Sep 27, 2007
    #2
    1. Advertising

  3. 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" <> wrote in message
    news:p...
    > 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" <> wrote in message
    > news:...
    > > 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!
    > >
    > >
    > >

    >
    >
    David C. Barber, Oct 3, 2007
    #3
  4. David C. Barber

    Aidy Guest

    > 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.
    Aidy, Oct 3, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Timasmith
    Replies:
    4
    Views:
    452
    Bjorn Borud
    Nov 1, 2006
  2. John Walker

    ASP.NET, SQL Record Locking

    John Walker, May 30, 2008, in forum: ASP .Net
    Replies:
    3
    Views:
    2,487
    sloan
    May 30, 2008
  3. JT
    Replies:
    1
    Views:
    117
    Bob Barrows [MVP]
    Sep 27, 2005
  4. David Heinemeier Hansson
    Replies:
    2
    Views:
    174
    Kevin Bullock
    Jun 3, 2004
  5. David Heinemeier Hansson
    Replies:
    11
    Views:
    279
    Gavin Sinclair
    Jun 10, 2004
Loading...

Share This Page