Posted by Markus on 07/13/07 14:28

Hello

I use a table to cache some informations which need lots of resources to
be composed. The first time the info is needed, it will be composed and
written to the cache table ($db in the example is a PEAR DB object; the
question is the same for other ways of accessing the database):

// Retrieve info if present
$details = $db->getOne("SELECT contents FROM cache WHERE id=".$id." AND
info='details'");
// If not present, compose info and write it to the database
if (!is_string($details) || trim($details) == '') {
$details = $this->compose_details();
$data = array('id' => $id, 'info' => 'details', 'contents' => $details);
$db->query("INSERT INTO cache (id, info, contents) VALUES (".$id.",
'details', '".$details."')");
}

Now I encountered that if several users call a page at the same time
after the cache was flushed, it is possible that between the first line
and the INSERT query the info was entered by another user. This results
in a duplicate key error.

Now I wonder which is the best way to handle this. I see various approaches:
- Use ON DUPLICATE KEY UPDATE (which might fail if MySQL 4.0 is used)
- Try to write some kind of locking mechanism
- Suppress the error message for this special case
- Remove the primary key from the cache table (as it is flushed whenever
items are administrated, duplicate entries might not be a big problem)

Which is the recommended way to handle this? I guess, as MySQL 4.0 and
MyISAM tables are a quite common configuration, there must be some
common practice about this, but I did not find anything by googling...

Thanks for comments!
Markus

[Back to original message]