开发者

SQLite database locking permanently (until deleted) for PHP

We are using the Recess framework for a web service. As part of this we're using Recess' caching mechanism, provided as an SQLite database.

We've been using this caching mechanism happily for about a year. However on 3 occasions now we've had issues with the SQLite database getting "locked" and causing issues. The message that we get is "exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 5 database is locked' in...".

I've had a search around and it seems to be a common issue, and there's a lot of discussion about ways to minimise its likelihood or prevent it (e.g. the drupal boards). However, my issue seems to be slightly different to this. The situations I've seen described seem to indicate that it's to do with concurrency - when two PHP processes try to access the SQLite database at exactly the same time, one of them gets a lock error. In this situation efforts to simply minimise the issue make sense. But for my application, when the issue starts happening (presumably because of concurrency), the SQLite database is from that point on permanently locked. Every single cache-accessing request from this point on gets a PDOException. Our solution has been to just delete the cache file, which is not the end of the world, but this requires manual intervention and plus means we lose the built-up cache data.

Why would this be happening? Is there other reasons why we might get the lock to start with? Why does the lock persist? Is there a way to programmatically clear it? Is there a way to prevent it in the first place?

The two "solutions" I'm considering so far are:

  1. Put a try-catch around the cache accessing functions. If we get exceptions, just ignore the cache and notify tech support to manually clear the cache.
  2. Use a mutex (using PHP flock) for the SQLite file to prevent the issue with concurrency (but again, I开发者_JAVA技巧'm not even certain this is the root cause).

Any information or suggestions would be greatly appreciated!


Well...

  1. Makes tech-support handle a non-existing problem.
  2. Ignores the fact that SQLite does it's own locking. (SQLite3 has even optimized it!)

Leaving the geek in me stand at the door, I would use "solution" number 3 (which you didn't list) and simply put a try-catch around the cache accessing functions. If you get exceptions, do a short sleep and then call the exception-causing function again.

thisFunction(...) 
{
    ....
    try
    {
      ....
    }
    catch(Exception $e)
    {
      sleep(rand(1,3))
      thisFunction(...);
    }
    ....
}

That way, you actually gain something from the error sqlite is trying to help you with. ;)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜