RE: Updating counts

I don't know if that would be speedy. Updates are far more expensive
than an insert. It does sound portable and safe though.

-----Original Message-----
From: Tim Bunce [mailto:Tim.Bunce@xxxxxxxxx]
Sent: Friday, March 30, 2007 9:47 AM
To: Ronald Kimball
Cc: George Bills; dbi-users@xxxxxxxx
Subject: Re: Updating counts

On Fri, Mar 30, 2007 at 09:55:23AM -0400, Ronald Kimball wrote:
Garrett, Philip (MAN-Corporate) wrote:

George Bills wrote:
I've also seen MySQL syntax like "ON DUPLICATE KEY UPDATE ..."
which looks good, but I'd like to avoid an extra dependency on a
database that has to be set up and maintained (hence SQLite).

My preference for a solution would be something that's simple and
maintainable primarily, followed by fast (for reads first, writes
second) - but I don't have the database experience to know what
the best solution is. Is there an easy way?

Yes, but it looks like you've already done it: try fetch, update if
successful, insert otherwise.

This will work as long as you don't have to worry about concurrent
processes inserting rows into the table. If you do have that, then
you've created a race condition. Two processes could try fetch at
roughly the same time, fail, and insert the same row, causing one of
the processes to get a duplicate key error.

Instead you could insert while trapping errors, and update if a
duplicate key error occurs. However, that probably requires checking
for a database-specific error code or message, at which point you
might as well just take advantage of MySQL's ON DUPLICATE KEY UPDATE.

Instead of "insert while trapping errors, and update if a duplicate key"
you can "update then insert if the update affected 0 rows". That avoids
the need to check for specific error codes. It also optimises for the
common case of the row already existing.

If the update affected 0 rows then the following insert may fail if some
other process inserts a matching record before you get to do your
insert. If your insert fails you can just retry the update again before
aborting.

Portable, safe, speedy.

Tim.
.

Relevant Pages

  • RE: Updating counts
    ... database that has to be set up and maintained. ... the processes to get a duplicate key error. ... for a database-specific error code or message, ... If the update affected 0 rows then the following insert may fail if some ...
    (perl.dbi.users)
  • Re: Updating counts
    ... database that has to be set up and maintained. ... the processes to get a duplicate key error. ... for a database-specific error code or message, ... If the update affected 0 rows then the following insert may fail if some ...
    (perl.dbi.users)