[Wellington-pm] Perl database stuff....

[Ewen McNeill]

In message <20061226085714.C1220112DB2 at wat.la.naos.co.nz>, Ewen McNeill writes:
>A third possibility seems to be:
>
>INSERT INTO table (id, business_key, ....)
>SELECT nextval('sequence'), 'business_key', ....
>WHERE NOT EXISTS 
>      (SELECT 1 FROM table WHERE business_key='business_key' limit 1);
>
>[http://beta.nntp.perl.org/group/perl.dbi.users/2006/08/msg29954.html]

but it seems I was right to be cautious about this being a complete
solution.  A more involved approach:

http://www.thescripts.com/forum/thread172627.html

which involves doing a "no op" UPDATE on some row on the table to force
the database to take a lock.  Although just looking at that, it appears
to primarily be using the update as a sort of advisory locking and
relying on the INSERT... WHERE NOT EXISTS... for more robust protection
against things not playing along.  And of course it relies on an
explicit transaction to hold the lock scope open through the INSERT.

IME many many applications just ignore this sort of problem and "hope
for the best" (or claim there's no solution).  It seems particularly
common for people who started out with "toy" databases that aren't
ACID compliant.

Ewen