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.
