x

Locking Issue with Concurrent Access of same stored procedure

 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.

more ▼

asked Jan 09 '10 at 04:16 AM in Default

Aiby gravatar image

Aiby
1 1 1 1

"Due to some reason I really want to avoid the intID field as Auto Increment." Can we ask why? Please post the structure, constraints, indexes, and what column(s) make up a clustered index for tblSales.
Jan 09 '10 at 11:19 AM TG
ID is contiguous per location, perhaps?
Jan 09 '10 at 02:26 PM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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...

more ▼

answered Jan 10 '10 at 01:15 PM

TG gravatar image

TG
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 09 '10 at 02:25 PM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 09 '10 at 02:33 PM

Aiby 1 gravatar image

Aiby 1
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x472
x37

asked: Jan 09 '10 at 04:16 AM

Seen: 1661 times

Last Updated: Jan 09 '10 at 04:16 AM