OperationalError: database is locked

[anonymous]

recent trunk, trac/web/session.py, line 210, in save, attempts to commit a transaction without performing properly error handling. If an OperationalError? is raised here, it will propagate all the way up to the top of the application without being handled.

A stacktrace recently logged on my server:

          File "/home/trac/Projects/Twisted/trunk/twisted/web2/wsgi.py", line 138, in run
            result = self.application(self.environment, self.startWSGIResponse)
          File "/home/trac/Run/trac/external.py", line 74, in __call__
            return self.application(environ, start_response)
          File "/home/trac/Run/trac/external.py", line 115, in tracApplication
            return trac.web.main.dispatch_request(environ, start_response)
          File "/home/trac/Projects/trac/trunk/trac/web/main.py", line 367, in dispatch_request
            req.send_error(sys.exc_info(), status=500)
          File "/home/trac/Projects/trac/trunk/trac/web/api.py", line 365, in send_error
            exc_info)
          File "/home/trac/Projects/trac/trunk/trac/web/main.py", line 335, in dispatch_request
            dispatcher.dispatch(req)
          File "/home/trac/Projects/trac/trunk/trac/web/main.py", line 236, in dispatch
            req.session.save()
          File "/home/trac/Projects/trac/trunk/trac/web/session.py", line 210, in save
            db.commit()
        pysqlite2.dbapi2.OperationalError: database is locked

[anonymous]

tsete

[anonymous]

Hiya Mark!

[anonymous]

hiya Andy!

[cboos]

We should probably transform this into a TracError ...

[anonymous]

Since two weeks (still with 0.9.6) we are experiencing "database locked" errors quite often, maybe 3 or 4 times per week. What can I do to help debugging this ? Until now I only have the error message which appears in Trac. Somebody does something, then something goes wrong, and maybe the first user after that event gets a "database locked". How can I find out more ?

Alex

[cboos]

Unfortunately, "database locked" errors are somewhat inherent the to default architecture used by Trac (see also #3446 for enhancement proposals).

This ticket is about the suggestion of transforming the "database locked" error into a more "friendly" error message, if that's possible.

The only situations where the "database locked" errors are indeed problematic are the ones in which the database remains locked for all subsequent requests. Fortunately, we haven't seen that since a while, now.

[anonymous]

Just as a note: we are running trac in fastcgi-mode. If the lock happens, it doesn't go away. We have to login to the server and kill the trac.fcgi processes with killall. At least one of them can't be killed friendly, but SIGKILL has to be used. Then we have to restart apache. We are running Trac 0.9.4 and sqlite etc. from march or april.

Alex

[anonymous]

Similarly, I'm using WSGI and if the lock happens, I also have to SIGKILL the server process to get it to exit.

SQLite, subversion, and Python bindings for both are the versions packaged in Ubuntu Breezy.

[mgood]

At this point we're not in need of more feedback from 0.9.x versions, since there have been improvement in the trunk since then. So, if you're interested in providing more testing please upgrade to 0.10b1 or the trunk.

cboos: do you have a plan for addressing this in 0.10, or can we push it to 0.10.1 since there doesn't seem to be sufficient information to debug it?

[anonymous]

I should have mentioned the version of trac I am using is a fairly recent trunk, r3585 (0.9.x had no WSGI support, did it?)

[cboos]

Replying to mgood:

cboos: do you have a plan for addressing this in 0.10, or can we push it to 0.10.1 since there doesn't seem to be sufficient information to debug it?

For 0.10, I only planned to implement the suggestion that we should convert the database is locked error to a more "friendly" TracError, as the lock error seems to be inevitable at this point, but this shouldn't block 0.10 and can well be post-poned to 0.10.1.

However, the fact that there are apparently still serious persitent locks around would probably justify that we reopen #1661, which was closed due to lack of feedback and because I believed that people stopped being affected by this, which is apparently wrong...

[kre]

Unfortunately I have been hitting "Database is locked" from switching to current trunk. Seems something that changed in last ~20 commits that caused database to be locked rather often.

[cboos]

#3756 marked as duplicate.

[anonymous]

Replying to kre:

Unfortunately I have been hitting "Database is locked" from switching to current trunk. Seems something that changed in last ~20 commits that caused database to be locked rather often.

Does this mean it happens more often with 0.10.0 than with previous versions ? If that's the case, we can't upgrade to 0.10.0, because this would block us even more. (I understand that reports about 0.9.x don't help you in any way anymore)

Alex

[cboos]

Replying to anonymous:

Does this mean it happens more often with 0.10.0 than with previous versions ?

I don't think so. In addition, with r3830, the locks seem to be much harder to reproduce. At least, I couldn't get one anymore, using tracd. I think I'll backport to 0.10.1, but more user feedback is needed first, so I'll be glad to hear from you if 0.10 + r3830 helps.

If that's the case, we can't upgrade to 0.10.0, because this would block us even more. (I understand that reports about 0.9.x don't help you in any way anymore)

No, 0.9.x in general will not get any more improvements, but only security bug fixes. Your best move would be to have a test drive of 0.10 like suggested above: this will help us to make a more robust 0.10.1 that you'll also benefit from.

[ThurnerRupert]

Replying to cboos:

Unfortunately, "database locked" errors are somewhat inherent the to default architecture used by Trac (see also #3446 for enhancement proposals). This ticket is about the suggestion of transforming the "database locked" error into a more "friendly" error message, if that's possible. The only situations where the "database locked" errors are indeed problematic are the ones in which the database remains locked for all subsequent requests. Fortunately, we haven't seen that since a while, now.

unfortunately, we had it today. v 0.10.

[ThurnerRupert]

Replying to ThurnerRupert:

Replying to cboos:

Unfortunately, "database locked" errors are somewhat inherent the to default architecture used by Trac (see also #3446 for enhancement proposals). This ticket is about the suggestion of transforming the "database locked" error into a more "friendly" error message, if that's possible. The only situations where the "database locked" errors are indeed problematic are the ones in which the database remains locked for all subsequent requests. Fortunately, we haven't seen that since a while, now.

unfortunately, we had it today. v 0.10.

read #1661 ... we did not wait a couple of minutes, but 3-4 times as long as with the normal (frequent) locking errors.

[ThurnerRupert]

seems to be a duplicate for #3446, there is a solution. we try it and report back.

[cboos]

No, it's not the same. The error reported in this ticket is specific to the req.session.save() done after the response was sent. The problem here is that we attempt to do a req.send_error() which will necessarily fail (as a response was already sent).

Also, it doesn't seem to be a good idea to persist the session changes if the request actually failed.

It would be possible to catch the session save failure and present it to the user if that is done before sending the response. However, this might increase the visible rate of database locks, which I think would be nevertheless OK given the recent progresses on this front.

[cboos]

Above changes implemented in r4048.

I should note that I still have a hard way to trigger database locks, even with that change, probably thanks to r3830 (see #3446). However, I was able to see the problem with the TracTimeline just after a commit (i.e. #2902).

Will backport this later.

[cboos]

Replying to cboos:

Above changes implemented in r4048.

Oops, not really... I actually misplaced the req.session.save, and of course there's no locks as nothing gets changed :\

[cboos]

...and with r4050, this should now be done correctly.

I still don't get database locks anymore... :)

The only database error that's still there (rarely) is: ERROR: columns sid, authenticated are not unique, which is a well known issue and not that critical (see #3563).

[cboos]

(and now I'll take a break, some sleep etc., as I realized that r4048 was OK; r4050 only moves the session.save after the post-processing, which is of course a bit better but wasn't that critical ;) )

[ThurnerRupert <rupert.thurner@…>]

we upgraded recently and we do not experience lock errors any more. before there was one lock error every 5-10 minutes caused by the company search engine spidering the trac instance.

[cboos]

Thanks very much for the feedback, but I've left the issue opened as a reminder for me to backport it to 0.10-stable, as currently this is only fixed in trunk.

[cboos]

r4048 was adapted for 0.10-stable in r4171.

Replying to ThurnerRupert <rupert.thurner@gmail.com>:

we upgraded recently and we do not experience lock errors any more. before there was one lock error every 5-10 minutes caused by the company search engine spidering the trac instance.

and this is probably due to r3830, ported in r3910.

[phil.taylor@…]

We just experienced the problem with version 0.11.1 - it went away after a few minutes.