Simultaneous call from many users from an application, @intID_1 is generates duplicate value ( or same value ) for more than one user. This cause a Primary Key vialotion error.
To avoid this I tried Locking and Isolation levels such as SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; WITH (HOLDLOCK) WITH (XLOCK) etc
at this stage I am getting a DeadLock Error!! when trying to call the procedure same time by many users.
Can I block the second user till the Transaction Commit, some how?
asked Jan 09 '10 at 04:16 AM in Default
With regards only to your stated requirments, reversing your clustered PK contraint keys to (intLocationID, intID) would make insert much more efficient. That time savings may resolve a lot of your problem. You physical storage order would probably make more sense that way as well if typically deal with Sales by location. That could make any solution better as well - including Kristen's solution.
Of course that may not be practical if you have a lot of un-stated requirements that rely on that the way it is...
answered Jan 10 '10 at 01:15 PM
Would INSERT ... SELECT work instead of INSERT ... VALUES
INSERT INTO tblSales ( [intID], [dtDate], [fltAmount], ) SELECT [intID] = ( SELECT Isnull( Max( intID ) + 1 , 1 ) FROM tblSales WHERE intLocationID = @intLocationID_2 ), @dtDate_8, @fltAmount_9
You'd have to come up with a way to return the ID assigned. If you also had an IDENTITY column you could get the Identity value assigned by the INSERT, and then select the intID from that record.
Reply to TG,
Reason for avoiding Identity column in intID is
answered Jan 09 '10 at 02:33 PM