Post reply

Ensuring a procedure does not get run twice at the same time

  • September 17, 2009 at 3:38 am

    #135712

    Hi,

    I'm not sure how to progress with this problem.

    I've got a procedure that takes a small but noticeable amount of time (about 0.5 to 1 second). The procedure populates a table with unique data based on the contents of other tables.

    My issue is that the procedure can be called from several other procedures and may be called more than once at the same time. I am looking to stop the subsequent calls to the procedure from starting the work till the first call has completed.

    Ideas that I've thought of, but I'm not sure if they would work are:

    1. Create a separate table with a flag that is changed at the start and then changed back at the end. The flag would be locked so that calls while it is working will wait for the lock to be released.

    2. Lock the table that I am about to populate so that nothing else can use it till the transaction is complete. (This one I'm not sure on how I would do it without risking unnecessary delays in other procedures)

    What I would really like is an equivalent to the monitor.enter and monitor.exit that is in .NET. This would allow me to keep the separate processing without additional locking of tables/rows.

    Does anyone have any ideas or pointers?

    Thanks, Zadj

  • September 17, 2009 at 3:41 am

    #1054866

    app lock could work for you

    http://msdn.microsoft.com/en-us/library/ms189823.aspx


    Clear Sky SQL
    My Blog[/url]

  • September 18, 2009 at 3:19 am

    #1055440

    If your calls to this procedure can be asynchronous, I recommend using the service broker. The broker will monitor the processing of the procedure (assuming it's been called by the broker) and will limit concurrent processing to the number you specify--in your case, 1.

  • September 18, 2009 at 4:07 am

    #1055462

    Thanks for your ideas.

    Dave - Your solution of using the app lock seems to work perfectly. I've run a few tests locally with some sample code and it has worked great. I got a good example of it's use at http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx with methods for testing it.

    Carleton - Your idea of using the service broker is interesting. Unfortunately, the procedure needs to be run synchronously, and we have had issues with using the service broker in the past taking up large amounts of resources (although that was probably due to the way that we were using it 😉 )

    Again, thanks for the quick replies and ideas.

    Zadj

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

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