Re: Abort state on duplicated PKey in transactions

I had a similar issue.

I needed to make sure I had a unique row- insert if not there, update if
there.

So I resorted to locking the whole table, then select, then insert/update.

What Tom told me to do was to use lock table tablename in exclusive mode
for my case.

This blocks select for updates, but doesn't block selects.

So you must check with a select for update, then only do the insert if it's
ok.

If you don't check with a select for update it will not block, and bad
things could happen :).

However I couldn't do a "for update" with an aggregate, so in my
generalised "putrow" routine I can't use "in exclusive mode".

I basically wanted to do a select count(*) from datable where whereclause
for update.

If the count was 0 then only insert, else if 1 update, else make some noise
:).

The alternative is to actually fetch the rows which can be slower.

Regards,
Link.

At 12:20 PM 08-09-2001 -0500, Haroldo Stenger wrote:
>transaction should have to be redone if the insertion failed. A
>solution, could be to query for the existance of the PK, just before the
>insertion. But there is a little span between the test and the
>insertion, where another insertion from another transaction could void
>the existance test. Any clever ideas on how to solve this? Using
>triggers maybe? Other solutions?