Adam wrote:

> --- In Firebird-Architect@yahoogroups.com, Jim Starkey <jas@...> wrote:
>
>> Lester Caine wrote:
>>
>>> Adam wrote:
>>>
>>>
>>>> Running an update which affects 0 rows would not fire any update
>>>>
> trigger.
>
>>>> So if it tried insert then update:
>>>>
>>>> Before insert trigger -> attempt insert -> fails -> before update
>>>> trigger -> update -> after update trigger.
>>>>
>>>> Or if it tried update and insert:
>>>>
>>>> The update wouldn't affect any row, so no update triggers are fired,
>>>> therefore the insert is attempted.
>>>>
>>>> So depending on the methodology chosen, the before insert
>>>>
> triggers may
>
>>>> be fired.
>>>>
>>>>
>>> Good pickup Adam - I knew there was a reason I try the update first ;)
>>>
>>>
>>>
>> Rather than trying either statement, the internal Falcon engine
>> (inaccessible from MySQL) first fetches the record. If it finds a
>> record, it updates it, otherwise it does an insert.
>>
>
> Hi Jim,
>
> That sounds like a more sensible solution to me.
>
> Can I ask how the 'insert or update'/'update or
> insert'/'replace'/'substitute'/'whatever is decided' will handle MGA
> isolation though (from my understanding, not an issue under MySQL due
> to design).
>
> Consider snapshot transactions T1, T2.
>
> T1: Starts
> T2: Starts
> T1: insert or update into ..... values (1) ....
> T2: insert or update into ..... values (1) .... (same DML)
>
> Is T2 allowed to see the record (attempting and failing the update due
> to lock conflict), or does T2 attempt to insert a second record,
> because the exists check if performed under transaction isolation
> would fail?
>

It try an UPDATE, and if no record are updated, it try a INSERT.


Adriano