On Fri, Mar 30, 2007 at 09:56:02AM -0600, Ian Harisay wrote: > I don't know if that would be speedy. Updates are far more expensive > than an insert.
That very debatable, but the point is that updates would generally be more frequent than inserts in a typical "increment counter" scenario. Tim. > It does sound portable and safe though. > -----Original Message----- > From: Tim Bunce [mailto:[EMAIL PROTECTED] > Sent: Friday, March 30, 2007 9:47 AM > To: Ronald Kimball > Cc: George Bills; [email protected] > 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.
