Locking a SQL Server Record from ASP + Batch Transactions

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • David C. Barber
    #1

    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!


    Tags: None
    • Aidy
      #2
      Re: Locking a SQL Server Record from ASP + Batch Transactions

      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" <david@NOSPAMdb arber.comwrote in message
      news:GsSdnSGnWL soiGbbnZ2dnUVZ_ tKinZ2d@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!
      >
      >
      >

        Comment

        • David C. Barber
          #3
          Re: Locking a SQL Server Record from ASP + Batch Transactions

          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" <aidy@xxnoemail xx.comwrote in message
          news:Pq-dnWJjvYD07WbbnZ 2dnUVZ8v2vnZ2d@ 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" <david@NOSPAMdb arber.comwrote in message
          news:GsSdnSGnWL soiGbbnZ2dnUVZ_ tKinZ2d@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!

          >
          >

            Comment

            • Aidy
              #4
              Re: Locking a SQL Server Record from ASP + Batch Transactions

              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.


                Comment

                Previous template Next
                Working...