"Database is locked" error

[manu.blot@…]

The infamous "database is locked" error which used to easily plague Trac in the old days seems to be triggered quite often when Bitten is in used. With a single bitten-slave and a couple of regular Trac/SVN users, I've already experience 4 of such errors in the last two days. It seems that when Bitten UI is used and a SVN revision is committed, the "database is locked" error is likely to be triggered.

Trac:	0.11dev-r6749
Python:	2.5 (release25-maint, Dec 9 2006, 14:12:17) [GCC 4.1.2 20061115 (prerelease) (Debian 4.1.1-20)]
setuptools:	0.6c3
SQLite:	3.3.8
pysqlite:	2.3.2
Genshi:	0.4.4
mod_python:	3.2.10
Pygments:	0.9
Subversion:	1.4.2 (r22196)
jQuery:	1.2.3

[manu.blot@…]

I have to reload Apache several times a day so that users can access the Trac server ;-(

Any hints about how to reduce Bitten long lasting 'lock' operations?

[Emmanuel Blot <manu.blot@…>]

This situation has been improved a lot since I moved away from mod_python and replaced the Apache gateway with mod_fastcgi.

[gkruse@…]

I am seeing this on a regular basis while builds are running. I reported this to the trac trac and got the following response:

Please report this in the Bitten Trac.

Connections shouldn't be kept alive for long periods of time, so hopefully there's something that can be improved on the Bitten side, on that respect.

​http://trac.edgewall.org/ticket/7967

[anonymous]

The fix/workaround is to switch from sqlite to postgresql. The former locks the entire file for updates, so you'll get the locked problem (accompanied by an "Internal server error" for the bitten slave) when, for example, a user edits a ticket just as a build step completes. The problem gets worse the larger your sqlite database gets. But PostgreSQL doesn't suffer that problem.

I had the same problem (many times a day), switched database, and don't have the problem any more.

[eblot]

Replying to anonymous:

The fix/workaround is to switch from sqlite to postgresql.

I did the same about 1 month ago, but IMHO this is more a workaround than a solution: PostgreSQL is an overkill setup for a small installation; Bitten should be able to run without having to rely on such a complex setup, at least for small team / projects.

[osimons]

Trac syncs repos so no need for Bitten to do so. Helps remove "Database is locked" errors.

[osimons]

I've had some 20-25 Bitten-related tabs loaded in Firefox, and just doing "Reload All Tabs" has consistently left me with more than half returning 'Database is locked' errors in various forms and at various stages of the request.

As discussed with pacopablo and rblank the other night, the fact that Bitten syncs the repos is not needed as Trac already handles this on a per-request basis. Removing repos syncing helps as I now sometimes manage to reload all tabs without errors - and when the error appears it hits less tabs than without the patch.

Still, it still does not seem a full solution to the problem. However, the remaining errors in the logs are usually req.session.save() (first message finalizing a request), and _get_name_for_cookie() (remaining messages, trying to load authentication information on new incoming request). I don't see anythin Bitten-specific in the logs, so unsure what more useful research can be done.

[osimons]

BTW: Please try attachment:t269-no_resync-r708.diff​ against latest trunk :-)

[osimons]

Interesting. The tab that consistently results in the error is /timeline. Removing that tab from my tab-set, and the error does not appear at all.

Looking at the Bitten timeline code, it still uses the 0.10 API where the full events are rendered and yield'ed. In the 0.11 API this is done by first returning all events with just data, and then Bitten will be called again for each event through the new render_timeline_event(self, context, field, event) method.

I'll try redoing this and see if new-style API makes a difference.

[osimons]

Using Trac 0.11 Timeline API

[eblot]

Replying to osimons:

BTW: Please try attachment:t269-no_resync-r708.diff​ against latest trunk :-)

Sorry I won't be able to test it:

• I had to move to PostgreSQL several months ago, as Trac + Bitten led to a nearly unusable system due to the amount of "Database is locked" errors.
• I think that since the build/test log messages are not stored in the DB anymore, the occurrence of this major error have been decreased.

I haven't close this ticket as worksforme because PostgreSQL is more a workaround - in our case - than a real fix.

[osimons]

I've made a straight conversion to new Timeline API in attachment:t269-011_timeline-r708.diff​

I'm not sure it makes a noticeable difference, but if anything it is better and not worse as I manage at times to load a number of Timeline tabs. Still, my further timeline testing shows that Timeline is a problem - removing Timeline tabs, and all full reloads always works without issues. And timeline tabs without 'Builds' checked also fails (ie. no Bitten code executed). Looking into Trac Timeline code is a task for the Trac project, and outside the scope of Bitten. I'll check and see what can be discovered.

BTW, no worries Manu - I know you had converted to the 'dark side'. Hopefully we'll manage to track this down anyway...

[anonymous]

We still see this all the time on our production trac. We may be able to test if needed.

[osimons]

Replying to anonymous:

We still see this all the time on our production trac. We may be able to test if needed.

Thanks - could you apply the 2 patches, and see if/how that improves things for you?

[cboos]

To me, attachment:t269-no_resync-r708.diff​ is a must have. In Trac, there's the assumption that the notion of what is the "latest revision" doesn't change during the course of a single request, something that Bitten violates without the patch.

The switch to the new timeline API is also a good thing to do, but I think this can still be improved by first fetching all the results from the DB and then yielding the events. Alternatively, this could be done transparently at the level of the Cursor wrapper class, see latest comments in ​trac:ticket:3446 (hm, ​#T3446 doesn't work, please get someone to update [intertrac]).

Also, a bit [OT] regarding attachment:t269-011_timeline-r708.diff​, it's a bit sad you had to do:

if context.req.args.get('format') == 'rss':

I'll add a rendering hint in 0.12, so that this can be written:

if context.get_hint('format') == 'rss':` 

in the future 0.12 compatible version.

[osimons]

Patches committed as [717] (no repos sync) and [718] (0.11 timeline api).

Remaining issues from my testing is well-described in ​trac:ticket:8468 and is due to an error with timeline updating session with new types causing the session to DELETE old session and INSERT a new one as u'30' != 30 when comparing old and new. See also ​trac:ticket:3446.

I don't see much more that can be done on the Bitten side, so I'm closing this ticket.

[cboos]

Replying to osimons:

I don't see much more that can be done on the Bitten side

Maybe double check the SQL queries made by Bitten, to see if they're not looking for more results and more data than what will actually be handled.

For example, if there's a select that will return lots of results, and the iteration on the cursor will stop after e.g. 10 results, then be sure that you use a LIMIT 10 in the query itself.

If Bitten makes queries that take a long time, then there will be locks, no matter what is done on the Trac side. If there are such expensive queries, maybe look into ways of making more but faster queries?

Only that way will have a commit performed simultaneously in another thread will have some chances of success.