Re: Of Mysql, DBI and PK best practices

I parallel posted this to MySQL's forum and it was suggested that I was looking for the MySQL syntax "INSERT ... ON DUPLICATE KEY UPDATE".

I'd never heard of such a thing and a brief google search showed it is probably what I'm looking for (I'm a pretty big believer that with reasonable usage, the db is usually going to be more efficient that anything I write). I spent hours searching Google for options on what I was seeing: "MySQL primary key violation on insert" and got no where.

--Jimbus aka Jim Babcock
Wireless Data Engineer and Geek Wannabe
jim-dot-babcock-at-usa-dot-com

Comment on Re: Of Mysql, DBI and PK best practices

Replies are listed 'Best First'.
Re^2: Of Mysql, DBI and PK best practices by andye (Curate) on Apr 17, 2007 at 17:59 UTC
Hi jimbus, I've used `ON DUPLICATE KEY UPDATE` in MySQL and it pretty much does exactly what it says on the tin. Seems to work well. A more database-neutral alternative is to `select` the key from the table, and if it doesn't exist then `insert`, otherwide `update`. You'll need to wrap this in a transaction to avoid the race condition kyle mentions above (unless you are the only process working on the db). In MySQL this means you either need to get a table lock, or switch your table type to InnoDB and do a row-level transaction as described on this page: InnoDB locking reads. By the way, `ON DUPLICATE KEY UPDATE` will lock the auto-increment table if you have an auto-increment field in your table, so this situation is one that can cause locking problems whichever solution you choose, if you have a lot of concurrent inserts/updates. (update: will only lock the auto-increment table if an insert actually takes place, I think - still, a potential problem) As I see it, the problem with the original solution (use insert, check for error, use insert if update didn't work) is, what if the error wasn't a duplicate key error? To do this properly, I think you'd need to check the error type. That way you could try again with an insert if the error was a duplicate key error, or if it was a different error then report the error and exit as usual. You'd need to ensure that `RaiseError` was switched off on your DBI handle. HTH! Best wishes, andye	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^2: Of Mysql, DBI and PK best practices
by andye (Curate) on Apr 17, 2007 at 17:59 UTC

jimbus

I've used ON DUPLICATE KEY UPDATE in MySQL and it pretty much does exactly what it says on the tin. Seems to work well.

A more database-neutral alternative is to select the key from the table, and if it doesn't exist then insert, otherwide update.

You'll need to wrap this in a transaction to avoid the race condition kyle mentions above (unless you are the only process working on the db). In MySQL this means you either need to get a table lock, or switch your table type to InnoDB and do a row-level transaction as described on this page: InnoDB locking reads.

By the way, ON DUPLICATE KEY UPDATE will lock the auto-increment table if you have an auto-increment field in your table, so this situation is one that can cause locking problems whichever solution you choose, if you have a lot of concurrent inserts/updates.
(update: will only lock the auto-increment table if an insert actually takes place, I think - still, a potential problem)

As I see it, the problem with the original solution (use insert, check for error, use insert if update didn't work) is, what if the error wasn't a duplicate key error? To do this properly, I think you'd need to check the error type.

That way you could try again with an insert if the error was a duplicate key error, or if it was a different error then report the error and exit as usual. You'd need to ensure that RaiseError was switched off on your DBI handle.

HTH!

Best wishes, andye

[reply]
[d/l]
[select]