Locking table from stored procedure

Question

Post reply

Locking table from stored procedure

Aditya-313343

Right there with Babe

Points: 768
More actions
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".

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

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

Roob SSC Enthusiast Points: 142 More actions · Answer 1

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

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 2

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.

harinerella Mr or Mrs. 500 Points: 548 More actions · Answer 3

Just to maintain unique numbers you can try sequece also.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

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