Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

Re^2: Of Mysql, DBI and PK best practices

by andye (Curate)
on Apr 17, 2007 at 17:59 UTC ( #610598=note: print w/replies, xml ) Need Help??

in reply to Re: Of Mysql, DBI and PK best practices
in thread Of Mysql, DBI and PK best practices

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.


Best wishes, andye

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://610598]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (6)
As of 2018-06-18 08:26 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (109 votes). Check out past polls.