Post reply

Locking table from stored procedure

  • January 4, 2011 at 9:45 pm

    #142907

    HI ,

    Breif: Can some one plase suggest me how to lock a table when inserting data into it by a process,to make sure other process which might run simultaneously not use the same data.

    Explanation:

    I had a table to store unique filenumbers say "A" .A stored procedure gets max(filenumber) from table "A" and inserts the new filenumber into the same table by adding 1 to the max(Filenumber).

    This stored procedure is accessed by different SSIS packages which might run simultaneously.I want to make sure when each package calls the stored procedure it creates a unique filenumber in table "A".

  • January 4, 2011 at 10:42 pm

    #1269305

    Greetings!

    Have you tried using row versioning?

    Depends on which version of SQL you are running, but this page should give you a good start!

    Happy Reading!

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

  • January 5, 2011 at 5:46 am

    #1269422

    The best way to do what you want is to use an Identity field for your filenumbers. That way it doesn't matter how many times the proc is called in the same second, each number will remain unique.

    GUIDs can be used too, but I have never seen a good reason to use them in my environment.

    If you really want to lock the table, though, open up Books Online and look at the subject "table locking [SQL Server]" without the quotes. There are several options to locking tables, using table hints can help. But I don't believe this is the best option. It gets complicated faster than you realize and if it's set up wrong, you either can't access your table data when you need to (causing lots of deadlocks or suspended threads) or it doesn't prevent all duplicate data issues. More than likely, though, it'll be the former.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • January 5, 2011 at 7:16 am

    #1269495

    Just to maintain unique numbers you can try sequece also.

  • January 6, 2011 at 8:20 am

    #1270126

    Aditya-313343 (1/4/2011)

    HI ,

    Breif: Can some one plase suggest me how to lock a table when inserting data into it by a process,to make sure other process which might run simultaneously not use the same data.

    Explanation:

    I had a table to store unique filenumbers say "A" .A stored procedure gets max(filenumber) from table "A" and inserts the new filenumber into the same table by adding 1 to the max(Filenumber).

    This stored procedure is accessed by different SSIS packages which might run simultaneously.I want to make sure when each package calls the stored procedure it creates a unique filenumber in table "A".

    This is a classic problem with multiple solutions - most of them fraught with flaws. I have had to help numerous clients clean up messes (both concurrency related and worse duplicate numbering) that come from this scenario!! The current 'most bestest' way I believe is to use UPDATE with OUTPUT clause, which handles all of the locking/concurrency issues for you in a single statement.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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