Persistent 'database is locked' error

[sambloomquist@…]

I am getting the infamous 'database is locked' error. I've looked at tickets #3446 and #3503, but my error is persistent so I decided to open a new ticket.

I'm using Trac 0.10.3 on SQLite 3.3.13 and PySQLite 2.3.3

The error started when I switched to using trac.fcgi instead of trac.cgi. Now I've switched back, but I'm still getting the error:

Oops...

Trac detected an internal error: database is locked

Traceback (most recent call last):
  File "/home/spooninator/packages/share/trac/cgi-bin/trac.cgi", line 20, in ?
    cgi_frontend.run()
  File "/home/spooninator/packages/lib/python2.3/site-packages/trac/web/cgi_frontend.py", line 68, in run
    gateway.run(dispatch_request)
  File "/home/spooninator/packages/lib/python2.3/site-packages/trac/web/wsgi.py", line 87, in run
    response = application(self.environ, self._start_response)
  File "/home/spooninator/packages/lib/python2.3/site-packages/trac/web/main.py", line 377, in dispatch_request
    env = _open_environment(env_path, run_once=run_once)
  File "/home/spooninator/packages/lib/python2.3/site-packages/trac/web/main.py", line 51, in _open_environment
    return open_environment(env_path)
  File "/home/spooninator/packages/lib/python2.3/site-packages/trac/env.py", line 435, in open_environment
    if env.needs_upgrade():
  File "/home/spooninator/packages/lib/python2.3/site-packages/trac/env.py", line 315, in needs_upgrade
    if participant.environment_needs_upgrade(db):
  File "/home/spooninator/packages/lib/python2.3/site-packages/trac/env.py", line 369, in environment_needs_upgrade
    dbver = self.env.get_version(db)
  File "/home/spooninator/packages/lib/python2.3/site-packages/trac/env.py", line 236, in get_version
    cursor.execute("SELECT value FROM system WHERE name='database_version'")
  File "/home/spooninator/packages/lib/python2.3/site-packages/trac/db/util.py", line 51, in execute
    return self.cursor.execute(sql)
  File "/home/spooninator/packages/lib/python2.3/site-packages/trac/db/sqlite_backend.py", line 56, in execute
    args or [])
  File "/home/spooninator/packages/lib/python2.3/site-packages/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: database is locked

I've tried killing the trac.cgi processes, but this never goes away. I'm attaching my recent log file.

[samboomquist@…]

log file

[Christian Boos]

I've tried killing the trac.cgi processes

Well, if you have a process holding a lock on the db that is stalled for some reason and you can't kill, no wonder all the other attempts to get to the db are failing.

What you could do:

• copy the db/trac.db to db/trac.db-tmp
• check the integrity of this copy
  • open it with sqlite3
  • see if your latest content is there and the queries are otherwise ok
  • just out of curiosity, do:
    1. select max(cast(rev as int)) from revision;
    2. select max(cast(rev as int)) from node_change;
    Actually, this is for checking if there isn't any connection with #4043…
• move or blow away the old db/trac.db
• rename the copy to db/trac.db
• restart your server

[Chris]

For what it's worth, I'm also getting frequent 'database is locked' errors with 0.10.3 and a pysqlite database. On the order of several times a week.

It only appears to happen so far when I attempt to run Trac as fcgi. Normal cgi seems to work okay (but is too slow, unfortunately).

I realize this isn't much help without a backtrace or other log, but I just wanted to let you know because I noticed that most of the tickets on the locked database bug indicate that this bug is considered fixed on 0.10.3.

[Christian Boos]

Replying to Chris:

For what it's worth, I'm also getting frequent 'database is locked' errors with 0.10.3 and a pysqlite database. On the order of several times a week.

Well, it's very important to specify whether you keep getting errors for all further requests once the error happen, until you take some recovery action. (This is what we call "persistent" 'database is locked' errors and what this ticket is about.

OTOH, have a few spurious 'database is locked' errors (i.e. only a request fail, retrying it succeeds) per week using the PySqlite backend can be considered normal, given the intrinsic limitations of SQLite at the level of concurrent (read/write) requests. See #3446 for more details.

[anonymous]

Replying to cboos:

Well, it's very important to specify whether you keep getting errors for all further requests once the error happen, until you take some recovery action. (This is what we call "persistent" 'database is locked' errors and what this ticket is about.

Sorry, didn't realize there was a different kind of lockup. Yes, these are persistent locks. I have to log in to the server and kill -9 the trac.fcgi instances to get things working again.

I'll be sure to record any logs next time I see it. I know it's frustrating to get an "it's broken!" complaint without evidence to help solve it.

[Christian Boos]

#4970 was closed as duplicate.

[Christian Boos]

Can you please upgrade to source:branches/0.10-stable@5115​ and see if this still happens at that revision?

[chris]

Yes, I will try to do that. I haven't had time yet, but I hope to find some time to do it within the next week or so.

Since I said I'd paste in a traceback when I had one, I'll go ahead and do so, but of course this is still only with version 0.10.3, so it may not be very useful. This traceback was produced when creating a new ticket.

Traceback (most recent call last):
  File "/home/cricket/packages/lib/python2.3/site-packages/trac/web/main.py", line 387, in dispatch_request
    dispatcher.dispatch(req)
  File "/home/cricket/packages/lib/python2.3/site-packages/trac/web/main.py", line 237, in dispatch
    resp = chosen_handler.process_request(req)
  File "/home/cricket/packages/lib/python2.3/site-packages/trac/ticket/web_ui.py", line 128, in process_request
    self._do_create(req, db)
  File "/home/cricket/packages/lib/python2.3/site-packages/trac/ticket/web_ui.py", line 192, in _do_create
    db.commit()
OperationalError: database is locked

Anyway, I'll update again when I've managed to upgrade to revision 5115. Thanks much.

[Christian Boos]

Replying to chris:

Anyway, I'll update again when I've managed to upgrade to revision 5115.

Well, rather try with the latest of branches/0.10-stable (currently r5144). It might even be that there will be a 0.10.4rc1 release candidate at that time :-)

On the downside, it doesn't look like the changes between 0.10.3 and 0.10.4 might help for the kind of traceback you've displayed… Make sure you have a recent SQLite and pysqlite2 combination, the former built in a thread-safe way (look at the PySqlite page for details).

[Chris]

For what it's worth, I finally got around to updating to 0.10.4 about a month ago. I switched back to fcgi to give it a whirl and haven't had any lockups yet.

[werdlerk@…]

I've had this exact same error a few minutes ago.

After trying several things (including renaming and killing all running python processes) I discovered that the database was having issues;

sqlite> PRAGMA integrity_check;
ok
sqlite> VACUUM;
SQL error: database is locked
sqlite> PRAGMA integrity_check;
ok
sqlite> rollback;
SQL error: cannot rollback - no transaction is active

The database was somehow locked up.

I have the database "recovered" now using the method mentioned at PySqlite:

sqlite3 corrupted.db .dump | sqlite3 recovered.db

Something must have changed during this recovery as the filesizes of both databases now differ in 37 kb.

I'm going to try restarting Trac using this new database. Lets see how it goes.

[werdlerk@…]

That did the trick. After a resync (which took some time) Trac is back up and running faster then before.

I hope this doesn't happen too often ;)

[ozone@…]

I just saw this error on my own Trac sqlite database; a sqlite3 VACUUM command reported "disk I/O error". Doing a dump/restore fixed it for me (see the two comments above this one).

[Christian Boos]

Reading through the ticket comments, it seems that since 0.10.4, the situation improved. In other cases, a database corruption happened and a recovery procedure fixed it.