| Markus Ernst 2007-07-12, 7:02 pm |
| 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:
$details = $db->getOne("SELECT contents FROM cache WHERE id=".$id." AND
info='details'");
if (!is_string($details) || trim($details) == '') {
$details = $this->compose_details();
$data = array('id' => $id, 'info' => 'details', 'contents' => $details);
$db->autoExecute('cache', $data, DB_AUTOQUERY_INSERT);
}
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 autoExecute() statement the info was entered by another user.
This results in a duplicate key error. (The application uses MySQL 4.0
or higher, with MyISAM tables.)
Now I wonder which is the best way to handle this. I see various approaches:
- Drop autoExecute() and use a query with ON DUPLICATE KEY UPDATE (which
might fail if MySQL 4.0 is used)
- Try to write some kind of locking mechanism
- Find a way to 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? Or is there a pre-built
solution in PEAR DB? (Would be a nice feature anyway, as the locking
stuff seems to be quite different in various RDBMS.)
Thanks for comments!
Markus
|