Home Forums Programming General Data in a row is being updated somewhere else during a stored procedure RE: Data in a row is being updated somewhere else during a stored procedure

  • October 19, 2017 at 12:25 pm

    #1964400

    Lynn Pettis - Thursday, October 19, 2017 12:09 PM

    Going out on a limb here, but based on your pseudo code, yes.  It is possible that there is no lock taken on table_a when the data is read and inserted into table_b.  During the processing another process comes along and updates the data.  Quite possible.  I don't have the time to test it at the moment, which is why I said going out on a limb.

    There is a way around this, something like this:

    IF EXISTS(SELECT * FROM table_a WHERE ID=@ID AND StatusID = 1) -- ID is a unique column in the table
    BEGIN
     BEGIN TRANSACTION
     DELETE FROM table_a

    This may need some tweaking but should prevent the data from being updated while processing.

    Thanks for your reply. The reason I did not delete first because there is a constraint on table_a, and I have to delete the data after all update/delete in the process has been done.

    Thanks for clarification that during the process the data can be updated by different query from different App. I am not comfortable with locking the table because it will create a queue where the other process will need to wait until this stored procedure has been completed. The SP could run more than 10 minutes. During the 10 minutes, I do not want the other app waiting. Instead, I want the system answer just return an answer to the user saying "do not update this data" or "the data is not available for the update in the next x minutes"