INSERT or UPDATE

This can be very difficult to get right. For example, Dominus's suggestion to use

    eval { UPDATE ... } or INSERT ...;

introduces a potential race condition. If two processes are simultaneously trying to update-or-insert a row using that technique then the following can happen:

(initially the row is not there)
process A: UPDATE fails
process B: UPDATE fails
process A: INSERT succeeds
process B: INSERT fails

causing an unexpected failure in one of the processes. It's safer to do it the other way around:

eval { INSERT ... } or UPDATE ...;

which ought to be safe, because a successful INSERT will lock the affected row. I think this difficulty nicely illustrates the advantage of having a REPLACE option in EZDBI, though it would have to be implemented slightly differently for each different type of database.

Comment on INSERT or UPDATE

Replies are listed 'Best First'.
Re: INSERT or UPDATE by Dominus (Parson) on Oct 10, 2001 at 20:15 UTC
Somone said: This can be very difficult to get right. The whole thing seems like a tremendous can of worms. I think what I'm going to do is allow the user to specify additional functions, such as `use EZDBI 'Replace';` [download] This will create a `Replace` function which can be used just like the existing `Insert` and `Delete` functions, if the database supports it. If not, the `Replace` function will generate a syntax error, as if you had done `$dbh->prepare("REPLACE ...");` Trying to emulate `REPLACE` on databases that don't support that natively seems to be too perilous. The user is still free to write `eval { Insert ... } or Update...` if they want to. -- Mark Dominus Perl Paraphernalia	[reply] [d/l]

Replies are listed 'Best First'.

Re: INSERT or UPDATE
by Dominus (Parson) on Oct 10, 2001 at 20:15 UTC

This can be very difficult to get right.

I think what I'm going to do is allow the user to specify additional functions, such as

    use EZDBI 'Replace';
[download]

Replace

Insert

Delete

Replace

$dbh->prepare("REPLACE ...");

Trying to emulate REPLACE on databases that don't support that natively seems to be too perilous. The user is still free to write eval { Insert ... } or Update... if they want to.

--
Mark Dominus
Perl Paraphernalia

[reply]
[d/l]