DeadLock and Transactions

Question

question

user-395 (google) asked Nov 9, '09

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

transaction deadlock merge upsert

10 |1200

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

Answer 1 · 2009-11-09T09:05:02Z

Kristen answered Nov 9, '09

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

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

question