Post reply

Multithreading / Running replica procedures using separate connections / sessions.

  • September 19, 2007 at 9:17 am

    #70211

    Hi All,

     

    I have successfully configured multi threading on my SQL instance, so I have an ETL application that runs from a stored procedure, so that I done was to execute the same procedure from a separate connection, so this way I have 2 processes running, and the idea is to speed up the ETL application.

     

    Now, one problem that I have is that the procedures do have updates, and one final insert, is there a way in which I can control this in order to eliminate blocking, so for example if one process is updating a particular table, I don’t want the other table updating the same table as well, I prefer that it waits for 10 seconds before trying again.

     

    Does anyone know how to go about doing this ?

     

    Thanks

    Kindest Regards,

    John Burchel (Trainee Developer)

  • September 19, 2007 at 4:10 pm

    #734739

    When you issue an UPDATE statement you could specify a TABLE level Lock (TABLOCK) that way only one of them is accessing one table at a time. If you want to control the time to wait for a lock you can use SET LOCK_TIMEOUT millis

    Now is up to you to program the retry logic

    Cheers.


    * Noel

Viewing 2 posts - 1 through 1 (of 1 total)

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