At 01:20 AM 15/04/2008, Jochem Maas wrote:

>ok, so the php firebird extension probably does it wrong - that sucks but there
>is little that can be done about it (unless you want and can hack the relevant source) ...
>there is no active maintainer for the firebird/interbase extension.

There wasn't any INSERT OR UPDATE syntax when most of our interfaces were written. It's not a gotcha that is exclusive to PHP. But you never could pass a NULL argument in a parameter for a non-nullable column, not for an insert or an update. INSERT OR UPDATE doesn't magically make it possible.

>also note that the 'INSERT OR UPDATE' SQL has an issue that you might not have though of (and that therefore it might be worth considering split INSERT and UPDATE logic into seperate 'bits' with seperate SQL):
>
>Person A begins to edit Item with id of 1
>Person B deletes Item with id of 1
>Person A submits to changes to Item with id of 1
>
>... when the submit happens one assumes that an update will occur, but instead
>an insert occurs and the item that was deleted no exists again ... is this desired?

Although it is something the application designer needs to be aware of, under most conditions, it would not happen.

Person A begins to edit Item with id of 1
-- at this point, unless there is a pessimistic lock, the server does not know what Person A (transaction 1) is doing with this record

Person B deletes Item with id of 1
-- the server locks the record for Person B (transaction 2)

Person A submits to changes to Item with id of 1
-- and transaction 1 gets a lock conflict exception in NO WAIT resolution or, in WAIT, just waits until Person B commits or rolls back transaction 2

After the WAIT, what happens next depends on the isolation level of Person A's transaction.

-- In concurrency (snapshot) it will except with a lock conflict if transaction 2 committed, because transaction 2 is younger than transaction 1

-- In read committed, if tr. 2 rolled back, tr1's UPDATE proceeds. If tr. 2 committed, transaction 1 will except with a (different) lock conflict with RECORD_VERSION, while with NO_RECORD_VERSION the INSERT should (theoretically) proceed.

The "gotcha" that INSERT OR UPDATE introduces really just echoes what could always happen with an insert under the "most dirty" transaction conditions (read committed isolation, WAIT resolution with NO_RECORD_VERSION). The trick is to configure the transaction to ensure you get the behaviour you want.

However.................

If your explicit purpose is to allow one transaction to "re-insert" a record that another has recently deleted, then you should know that there is a high potential that the insert will fail from a primary key violation. That's because the *index maintenance* is not under the same transaction control as the record. The index node for a deleted record stays in place for at least as long as it takes for the deleted *record* to be garbage-collected.

Helen