locking tables applications

Results 1 to 2 of 2

Thread: locking tables applications

  1. #1
    Join Date
    Dec 1969

    Default locking tables applications

    I have an application that performs a series of inserts and updates to a few different tables:<BR><BR>insert user information<BR>update sales information<BR>update log files<BR>etc.<BR><BR>All of these take place on the same page of code (no redirects, etc.)<BR><BR>Is there any way to prevent another visitor from beginning this process until the process is done.<BR><BR>I know that if I wrote the WHOLE script in a Stored Procedure I could put it all inside ONE transaction, but this is a very complicated series of updates (it&#039;s a combinatorial solution program with a recursion routine) and it would take me a LONG time to rebuild it in T-SQL (combined with the fact that it would have to be entirely dynamic SQL). So I&#039;m looking for interim solutions.<BR><BR>Application.lock only locks the application object, right?<BR><BR>Is it feasible to set an application variable, say<BR>application("thisapplication_ready") to 1 when the thing is available and have it set to "0" when it&#039;s not and put everyone in loops until the the application object is ready again?<BR><BR>Anyone ever done this?<BR><BR>Thanks,

  2. #2
    Join Date
    Dec 1969

    Default .beginTrans

    The connection object has methods to begin, commit, and rollback transactions. So you could do something like<BR><BR>oConn.BeginTrans<BR><BR>oConn.execute( SQL1)<BR>oConn.execute(SQL2)<BR><BR>oConn.CommitTr ans<BR><BR>From a transaction viewpoint it would be the same as wrapping the whole thing inside one transaction in a sp.<BR><BR>It&#039;s generally better not to do things this way, caus its possible your connection could break before you commit or rollback then you could leave records locked until SQL realizes it&#039;s broken. Plus its just good practice to keep transactions as short as possible and multiple trips to DB adds time. <BR><BR>Still in your case this could be a small acceptable risk and like you said.. much easier than rewriting.

Posting Permissions

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