so the only real solution to this now is in application code outside of a transatction, i.e. PHP,Perl,VB,C,Python, etc, right?

Lincoln Yeoh wrote:
AFAIK the "except" select won't see other inserts in uncommitted transactions. If those transactions are committed you will end up with the same problem. You can try it yourself, by manually doing two separate transactions in psql.

You either have to lock the whole table, or lock at the application layer. Some time back I suggested a "lock on arbitrary string" feature for postgresql for this and various other purposes, but that feature probably wouldn't scale in terms of management (it requires 100% cooperation amongst all apps/clients involved).

There's no "select * from table where pkey=x for insert;" which would block on uncommitted inserts/updates of pkey=x and other selects for insert/update.

In contrast "select ... for update" blocks on committed stuff.


At 09:55 AM 3/27/03 +0800, Christopher Kings-Lynne wrote:

Hi Guys,

I just thought I'd share with you guys a very clever solution to the old
'update row.  if no rows affected, then insert the row' race condition
problem.  A guy at my work came up with it.

We were discussing this earlier on -hackers, but no-one could find a
solution that didn't involve locking the entire table around the
update...insert commands.

The problem is that sometimes the row will be inserted by another process
between your update and insert, causing your insert to fail with a unique
constraint violation.

So, say this is the insert:

INSERT INTO table VALUES (1, 'foo'); // 1 is in the primary key column

Rewrite it like this:


See? So now that INSERT statement will insert the row if it doesn't exist,
or insert zero rows if it does. You are then guaranteed that your
transaction will not fail and rollback, so you can repeat your update, or do
the insert first and then the update, etc.

Hope that's handy for people,


---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to