Locking tables

[Guest_imported]

Hi everyone,

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.
Under SQL after RS.Update the pointer moves to the next blank record in the table. Therefore, I had to use after Update the
MoveLast.

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.

Here is a truncated snapshot of my code:

Set submitRS = Server.CreateObject("ADODB.Recordset&quot
query1="SELECT * FROM Submissions order by Submission_ID"
submitRS.Open query1,Conn,adOpenStatic,adLockPessimistic
...
submitRS.AddNew
submitRS("Loginname&quot=Loginname
submitRS("Password&quot=Password
...
submitRS.Update
submitRS.MoveLast
Session("Submission_ID&quot=submitRS("Submission_ID&quot

First idea is to use Application.lock before opening the record set submitRS, but this has will keep the entire application locked and it's not that efficient.

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.

I will be very grateful if someone can give me any hints on this issue.

Thanks a lot.

 

[culshaja]

I don't know about the way you have done it, but it is more efficient to create a stored procedure in SQL Server with the required input parameters and the correct output paramater. You can then use the Command object in ADO to invoke the stored procedure and the output parameter is passed back to ASP. This takes up less system resources than the method you use above and is more efficient on the SQL Server.

As a thought about above you could set a bookmark on the new record just before issuing the Update, and then use it to go back to that record. Don't know if that will work as it is an insertion though!

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305