Locking Issue with Concurrent Access of same stored procedure

Question

 CREATE PROCEDURE [spSaveSales]                    
@intLocationID_2 	Int,                    
@dtDate_8 		SmallDateTime,                    
@fltAmount_9 		Numeric (16,2)                    
@intID_1 		[BigInt] Output                    
 As                    
 Begin                    
Begin Transaction                    
SELECT @intID_1 = Isnull( Max( intID ) + 1 , 1 ) FROM tblSales                      
                               WHERE intLocationID      = @intLocationID_2                    
INSERT INTO tblSales                     
	 (                     
	 [intID],                    
	 [dtDate],                    
	 [fltAmount],                    
	)                    
VALUES                     
	( @intID_1,                    
	 @dtDate_8,                    
	 @fltAmount_9,                    
	)                    
Commit Transaction                    
SELECT @intID_1                    
 End

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

SELECT @intID_1 = Isnull( Max( intID ) + 1 , 1 ) FROM tblSales  WITH (XLOCK)                    
                               WHERE intLocationID      = @intLocationID_2

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? Note:- Due to some reason I really want to avoid the intID field as Auto Increment.

Answer 1

0

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

TG
1.8k ● 1 ● 3

(comments are locked)

Answer 2

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.

Answer 3

Reply to TG,

CREATE TABLE [dbo].[tblSales ] (            
    [intID] [bigint] NOT NULL ,            
    [intLocationID] [int] NOT NULL ,            
    [intTransactionID] [bigint] NULL ,            
    [intTransactionTypeID] [int] NULL ,            
    [tnyVoucherTypeID] [tinyint] NULL ,            
    [intVoucherNo] [numeric](18, 0) NULL ,            
    [intBookNo] [int] NULL ,            
    [dtDate] [smalldatetime] NULL ,            
    [fltAmount] [numeric](18, 2) NULL ,            
            
    --            
    --            
    --            
            
    [numLinkKeyID] [numeric](18, 0) NULL ,            
    [dtTimeStamp] [smalldatetime] NOT NULL             
 ) ON [PRIMARY]            
GO            
            
            
            
 /****** Object:  Table [dbo].[tblSales]    Script Date: 09/01/2010 10:32:31 PM ******/            
 ALTER TABLE [dbo].[tblSales] ADD             
CONSTRAINT [PK_tblSales] PRIMARY KEY  CLUSTERED             
(            
	[intID],            
	[intLocationID]            
)  ON [PRIMARY]             
GO

Reason for avoiding Identity column in intID is 1) ID is generated by a Pattern according to certain parameters such as Location, Zone, Dealer etc. 2) Need continues numbers from each location ( While RollBacking Idex is missing ) 3) Many Conditions we may need to reset those intID value of a location to specific Number etc These are few which i could recollect for the time being.

Locking Issue with Concurrent Access of same stored procedure

Follow this question

Related Questions