question

user-395 (google) avatar image
user-395 (google) asked

DeadLock and Transactions

I have a Store Procedure being called to fill one table whenever we receive incoming files. Sometimes we receive more than one file and the procedure will be called simultaneously. Inside the Procedure the statements are quite simple as given below

                    
IF NOT EXISTS                     
(SELECT.... WHERE A=1 B=2)                     
                    
INSERT ...                     
                    
ELSE                     
                    
UPDATE ... WHERE A=1 and B=2                     
                    
END                    

doing this I started getting a duplicate records error, I assume that 2 same records tried to INSERT. To avoid this I put these staements inside a Transaction with SERIALIZABLE ISOLATION LEVEL.

things got even worse and I started getting deadlock error!!!

I tried with the default isolation level (Read Committed) before and I got duplicate records check error again!

Is there something wrong I am doing here??

transactiondeadlockmergeupsert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kristen avatar image
Kristen answered

You could try

            
BEGIN TRANSACTION            
            
UPDATE U            
SET ...            
FROM MyTable AS U            
WHERE ...            
            
IF @@ROWCOUNT = 0            
BEGIN            
    INSERT INTO MyTable            
    (            
        Col1, Col2, ...            
    )            
    SELECT ...            
END            
            
COMMIT            

I'm not 100% sure if this will create the lock that is needed to prevent the "window of opportunity" between the UPDATE not updating anything, but someone in that moment inserting a row

Another approach might be

            
BEGIN TRANSACTION            
            
INSERT INTO MyTable            
(            
    Col1, Col2, ...            
)            
SELECT ...            
WHERE NOT EXISTS            
(            
    SELECT *            
    FROM MyTable            
    WHERE MyPK = @Param1 ...            
)            
            
IF @@ROWCOUNT = 0            
BEGIN            
    UPDATE U            
    SET ...            
    FROM MyTable AS U            
    WHERE ...            
            
END            
            
COMMIT            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.