Locking tables

Results 1 to 2 of 2

Thread: Locking tables

  1. #1
    Florina Guest

    Default Locking tables

    Hi everyone,<BR><BR>I am currently developing an Update Form for a web implemented database using SQL Server 7.0. Whenever a submitter starts a new session I would like to save his Submission_ID into a session variable in order to use it later as foreign key into other tables in my database.<BR>Under SQL after RS.Update the pointer moves to the next blank record in the table. Therefore, I had to use after Update the<BR>MoveLast. <BR><BR>But obviously under concurrent access the Update and MoveLast are not atomically and I have somehow to enforce this to be one transaction. I need some advice on this issue.<BR><BR>Here is a truncated snapshot of my code:<BR><BR>Set submitRS = Server.CreateObject("ADODB.Recordset")<BR>query1=" SELECT * FROM Submissions order by Submission_ID"<BR>submitRS.Open query1,Conn,adOpenStatic,adLockPessimistic <BR>...<BR>submitRS.AddNew<BR>submitRS("Loginname" )=Loginname<BR>submitRS("Password")=Password<BR>.. .<BR>submitRS.Update<BR>submitRS.MoveLast<BR>Sessi on("Submission_ID")=submitRS("Submission_ID")<BR> <BR>First idea is to use Application.lock before opening the record set submitRS, but this has will keep the entire application locked and it&#039s not that efficient.<BR><BR>I would like to lock only the Submissions table to prevent other submitters to add a new record before one adds and reads his own Submission_ID.<BR><BR>I will be very grateful if someone can give me any hints on this issue.<BR><BR>Thanks a lot. <BR>

  2. #2
    Join Date
    Dec 1969
    Los Angeles, CA

    Default RE: Locking tables

    You can lock the table only<BR><BR>Use transactions in ADO<BR><BR>until you commit the transactin no one will be able to select with a pessimistic lock

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts