Post reply

Lock Out Others, during an Insert process

  • March 26, 2005 at 1:11 pm

    #163998

    HI,

    I have a process that goes like this..

    InputTable: Purpose to receive users data and check for errors, once data checked, load into main storage table.It is not a temp table.

    MainStorageTable: To store and receive data.

    Users will have a MS access client to input data into InputTable. There will be two MS access front ends, on two different sites tha both have data input abilities into the MainstorageTable.

    Situation: Two users logon on and input data into the InputTable and action the StoredProcedure to check and post the data AT THE EXACT SAME TIME.

    Question : How can I send a message to one client to say that the Stored Procedure is already running by another as an error message or something. As the process should run separately and not parrallel with other users. That is because data from User1 may be required by User2 to have an accurate process in the data checking process of data input into the MainStorageTable.

    I know how to use raiserror and use @@error, but how does one test to see if a procedure is running ? What part of the procedure do you test to see if this so ?

    I dont know if I am asking the correct question here. Any advice is welcome, Thanks in advance !

  • March 28, 2005 at 6:03 am

    #549093

    2 ways of handling this

    #1  Basically you have 2 tables A.  Current activity, B. Conflicting stored-procedure combinations

    1st item of business is when the stored procedure fires that you want to queue READ from table B to see if the proc is part of conflicts and then READ from current activity table to see if it is there.  IF found report error back.

    #2  Wrap the stored-procedure that you want to queue in an UPDLOCK with BEGIN TRAN/END TRAN (can read more about UPDLOCK in BOL).

      Basically UPDLOCK for a table doesn't like other UPDLOCKs so they queue themselves and wait until the previous 1 or 1's are done and then will run.

    #1 gives you control to modify data to throttle your system

    #2 code is required to change to throttle your system in the event that the throttle is too low\high.

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • March 28, 2005 at 11:56 am

    #549173

    Thanks for help, however I am a newbie, so is there more info on #1 and #2 in BOL.

    I searched UPDLOCK in BOL, nothing came up.

    Can you guide me what to search for to learn more on #1 and #2

    Thanks

     

  • March 28, 2005 at 4:53 pm

    #549224

    I would research BEGIN TRANSACTION in BOL.  That will give you #2.  #1 is more art then science and BOL doesn't cover it.  I think your easiest bet will be to use UPDLOCK...  You may also research table hints and/or index hints to find UPDLOCK.

    I'll try and send some pseudo code tomorrow...



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • March 28, 2005 at 5:23 pm

    #549227

    How about adding a column to the input table that signifies that that data belongs to User1 or User2. Then have your stored procedure work on the data for the specified user.

    Eliminates fiddling around with transactions and table/index hints.

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply