I have procedure code something like the following.

update t
set ...
where k = @k

if @@rowcount = 0
insert into t ...

This code is executed by a multithreaded application. On occassion two
threads will simultaneously have the same value for @k, and the insert fails
with message something like the following.

Msg 2601, Level 14, State 1:
Server 'foo', Procedure 'p_foo', Line 35:
Attempt to insert duplicate key row in object 't' with unique index 'pkt'

I understand why the above code doesn't work.

Does anyone have a good solution for making an update/insert work without
error?

Later,
BEDick

"Brian Dick" <bdick (AT) cox (DOT) net> wrote in
news:J3rUc.15$Nq3.38 (AT) news-srv1 (DOT) fmr.com:

Have you wrapped the unit of work in a transaction?

BEGIN TRANSACTION

update ....

if @@rowcount ...

COMMIT TRANSACTION

The above will ensure the lock is held by the first thread to make
the UPDATE and blocking the other. The other thread will unblock at
the COMMIT and will find nothing and won't execute the INSERT.

HTH
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com

Yes, the code is in a transaction. The problem is that the update can't lock
a row that doesn't exist (i.e. @@rowcount = 0).

"Pablo Sanchez" <honeypot (AT) blueoakdb (DOT) com> wrote

I'm not sure if my message took ...

I'll try again ...

What if ya do this:

begin tran

insert t
where not exists (select 1
from t
where k = @k)

if @@rowcount = 0
update t
set ...
where k = @k

commit tran


I assume "t" is indexed on "k" for the "not exists" clause to perform.

Cheers,

Sara ...

"Brian Dick" <bdick (AT) cox (DOT) net> wrote

"Brian Dick" <bdick (AT) cox (DOT) net> wrote:
You may code it the other way round: first try to insert,
then check for a 2601 error; if the error arose (duplicate
key insert), then update. I mean,

insert into t ...
if @@error = 2601
update ...

Beware this approach is probably more "lock-prone" than
the original. Also, it may be less efficient if updates
are more frequent than inserts.

Regards,
Mariano Corral

I tried that and it's slow.

"Mariano Corral" <corral (AT) iname (DOT) com> wrote

That's not a valid insert command. However, I tried the following variation
of your suggestion and the preliminary results look good.

insert into t
select @k, ...
where not exists (select 1 from t where k = @k)

"Sara Law" <saradba (AT) hotmail (DOT) com> wrote

Consider putting a timestamp column on your table.


"Brian Dick" <bdick (AT) cox (DOT) net> wrote