x

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

more ▼

asked Nov 09, 2009 at 04:33 AM in Default

avatar image

user-395 (google)
11 1 1 3

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

1 answer: sort voted first

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

more ▼

answered Nov 09, 2009 at 09:05 AM

avatar image

Kristen ♦
2.2k 7 11 14

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

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:

x77
x65
x57
x4

asked: Nov 09, 2009 at 04:33 AM

Seen: 1051 times

Last Updated: Nov 09, 2009 at 09:05 AM

Copyright 2018 Redgate Software. Privacy Policy