Mailing List Archive: Trac: Tickets

[The Trac Project] #3446: Rate of `database is locked` errors is too high

Index | Next | Previous | View Threaded

noreply at edgewall

Jul 21, 2006, 12:22 AM

Post #1 of 11 (2434 views)
Permalink

[The Trac Project] #3446: Rate of `database is locked` errors is too high

#3446: Rate of `database is locked` errors is too high
---------------------+------------------------------------------------------
Reporter: cboos | Owner: jonas
Type: defect | Status: new
Priority: normal | Milestone:
Component: general | Version: devel
Severity: major | Keywords: database lock pysqlite
---------------------+------------------------------------------------------
I still get way too many `database is locked` errors. This morning, I
simply tried to log in and the server was not loaded (approx. half of the
threads were "_" Waiting for connection).
{{{
Traceback (most recent call last):
File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 314, in
dispatch_request
dispatcher.dispatch(req)
File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 186, in
dispatch
req.session = Session(self.env, req)
File "/usr/lib/python2.3/site-packages/trac/web/session.py", line 52, in
__init__
self.promote_session(sid)
File "/usr/lib/python2.3/site-packages/trac/web/session.py", line 125,
in promote_session
"AND authenticated=0", (sid,))
File "/usr/lib/python2.3/site-packages/trac/db/util.py", line 47, in
execute
return self.cursor.execute(sql_escape_percent(sql), args)
File "/usr/lib/python2.3/site-packages/trac/db/sqlite_backend.py", line
44, in execute
args or [])
File "/usr/lib/python2.3/site-packages/trac/db/sqlite_backend.py", line
36, in _rollback_on_error
return function(self, *args, **kwargs)
OperationalError: database is locked
}}}

Jonas/Christopher, can one of you attach a recent gzipped dump of the
t.e.o Trac log here?
I'd like to make some stats about the locks frequency. Ideally I'd like to
understand why this still happens so frequently and find ways to improve
the situation...

Beside locks, I'm also frequently seeing blank pages when going on the
Markup Trac.
But probably that's a different issue.

--
Ticket URL: <http://trac.edgewall.org/ticket/3446>
The Trac Project <http://trac.edgewall.com/>
_______________________________________________
Trac-Tickets mailing list
Trac-Tickets [at] lists
http://lists.edgewall.com/mailman/listinfo/trac-tickets

noreply at edgewall

Aug 9, 2006, 12:45 AM

Post #2 of 11 (2334 views)
Permalink

Re: [The Trac Project] #3446: Rate of `database is locked` errors is too high [In reply to]

#3446: Rate of `database is locked` errors is too high
------------------------------------+---------------------------------------
Reporter: cboos | Owner: jonas
Type: defect | Status: new
Priority: normal | Milestone:
Component: general | Version: devel
Severity: major | Resolution:
Keywords: database lock pysqlite |
------------------------------------+---------------------------------------
Comment (by cboos):

#3536 marked as duplicate.

Quite obviously, this is not only an issue with Trac's Trac, but a more
general one due to our database access patterns... which currently limit
our concurrency to at most one write access each few seconds (if nobody
starts a long read query in the meantime, of course ...).

> Beside locks, I'm also frequently seeing blank pages when going on the
Markup Trac. But probably that's a different issue.
This has been filed as #3455.

--
Ticket URL: <http://trac.edgewall.org/ticket/3446#comment:1>
The Trac Project <http://trac.edgewall.com/>
_______________________________________________
Trac-Tickets mailing list
Trac-Tickets [at] lists
http://lists.edgewall.com/mailman/listinfo/trac-tickets

noreply at edgewall

Aug 9, 2006, 2:32 PM

Post #3 of 11 (2332 views)
Permalink

Re: [The Trac Project] #3446: Rate of `database is locked` errors is too high [In reply to]

#3446: Rate of `database is locked` errors is too high
------------------------------------+---------------------------------------
Reporter: cboos | Owner: jonas
Type: defect | Status: new
Priority: normal | Milestone:
Component: general | Version: devel
Severity: major | Resolution:
Keywords: database lock pysqlite |
------------------------------------+---------------------------------------
Comment (by cboos):

Some random documents about SQLite and concurrent writes, for reference:
- [http://www.somacon.com/p369.php Proposal for Improving Concurrency in
SQLite]
- [http://www.mikesclutter.com/sqlite/index.shtml Synchronized Locking in
SQLite]
- [http://www.sqlite.org/cvstrac/wiki?p=MultiThreading SQLite
MultiThreading]
- [http://www.sqlite.org/concurrency.html Improving Concurrency In
SQLite], in particular section 4.0 ''Shorter transactions''
- [http://www.sqlite.org/cvstrac/wiki?p=BlueSky the Shadow Pager idea],
now that would be nice ;)

Also, don't forget about
[http://article.gmane.org/gmane.comp.db.sqlite.general/13002 locks
triggered by concurrent writes], which we should now handle correctly
(#2170 and #ps126).

--
Ticket URL: <http://trac.edgewall.org/ticket/3446#comment:2>
The Trac Project <http://trac.edgewall.com/>
_______________________________________________
Trac-Tickets mailing list
Trac-Tickets [at] lists
http://lists.edgewall.com/mailman/listinfo/trac-tickets

noreply at edgewall

Aug 15, 2006, 7:16 AM

Post #4 of 11 (2323 views)
Permalink

Re: [The Trac Project] #3446: Rate of `database is locked` errors is too high [In reply to]

noreply at edgewall

Aug 29, 2006, 8:11 AM

Post #5 of 11 (2306 views)
Permalink

Re: [The Trac Project] #3446: Rate of `database is locked` errors is too high [In reply to]

#3446: Rate of `database is locked` errors is too high
------------------------------------+---------------------------------------
Reporter: cboos | Owner: cboos
Type: defect | Status: new
Priority: normal | Milestone:
Component: general | Version: devel
Severity: major | Resolution:
Keywords: database lock pysqlite |
------------------------------------+---------------------------------------
Changes (by cboos):

* owner: jonas => cboos

Comment:

Well, despite r3616 we still have a high rate of "database is locked"
error.

See #3503 for a short-term solution (an nice !TracError explaining what
happens instead of a backtrace).

Long-term, I think we should do the following:
- '''Shorten our transactions'''. In particular, a main reason for the
problem seems to be the long periods of time during which SHARED locks are
held. The common programming idiom in Trac is to ''iterate'' over cursors,
performing some operation for each of the returned row. While this may
sounds a good approach which lowers memory consumption, it lengthens the
period during which a SHARED lock is held and during which ''not a single
write'' can be done. We should instead read everything in memory first,
''then'' process the data. Of course, if there are some very
straightforward tasks (like filtering) that can be done while reading the
data, it could still be done by iterating over the cursor.
- Also, if there are situations where not all the rows from a cursor are
fetched, the cursor should be closed, so that the SHARED lock can be
release as soon as possible.
- '''Repeatable transactions''': instead of doing writes into the
database as soon as we feel like doing so, we could pass a callback to the
database layer which will be used for writing all the information. The
advantage of this would be to be able to ''retry transparently'' the write
sequence if a `database is locked` exception is detected. This should
also play well with the journalling approach described in
TracDev/Proposals/Journaling.

--
Ticket URL: <http://trac.edgewall.org/ticket/3446#comment:4>
The Trac Project <http://trac.edgewall.com/>
_______________________________________________
Trac-Tickets mailing list
Trac-Tickets [at] lists
http://lists.edgewall.com/mailman/listinfo/trac-tickets

noreply at edgewall

Sep 8, 2006, 11:58 AM

Post #6 of 11 (2283 views)
Permalink

Re: [The Trac Project] #3446: Rate of `database is locked` errors is too high [In reply to]

noreply at edgewall

Sep 9, 2006, 9:05 AM

Post #7 of 11 (2269 views)
Permalink

Re: [The Trac Project] #3446: Rate of `database is locked` errors is too high [In reply to]

noreply at edgewall

Sep 27, 2006, 6:34 AM

Post #8 of 11 (2248 views)
Permalink

Re: [The Trac Project] #3446: Rate of `database is locked` errors is too high [In reply to]

noreply at edgewall

Oct 2, 2006, 10:09 AM

Post #9 of 11 (2224 views)
Permalink

Re: [The Trac Project] #3446: Rate of `database is locked` errors is too high [In reply to]

noreply at edgewall

Oct 3, 2006, 12:18 AM

Post #10 of 11 (2229 views)
Permalink

Re: [The Trac Project] #3446: Rate of `database is locked` errors is too high [In reply to]

#3446: Rate of `database is locked` errors is too high
------------------------------------+---------------------------------------
Reporter: cboos | Owner: cboos
Type: defect | Status: new
Priority: normal | Milestone: 2.0
Component: general | Version: devel
Severity: major | Resolution:
Keywords: database lock pysqlite |
------------------------------------+---------------------------------------
Changes (by cboos):

* milestone: => 2.0

Comment:

What should perhaps be made clear is that this issue is ''only'' seen with
the PySqlite DatabaseBackend. If the locks are becoming an issue, an
option is to convert the database to use another backend (e.g.
th:SqliteToPgScript). If you know beforehand that you will get some high
traffic and have an important number of developers that will use your
Trac, you can also directly create the Trac environment with one of the
alternative database backend.

SQLite has by design been created to allow multiple process to access
independantly to the same database file, even in theory by processes
running on different machines. This requires the use of OS-level file
locking facilities (flock) and basically you can't have more than one
thread or process writing to the database at the same time, and a write
can happen only when there's no longer any thread or process reading the
database.

We are nevertheless interested in getting the situation improved for
SQLite. Besides shortening the read and write periods as outlined above, I
also plan to check whether we can gain some performance by sharing
connections between threads, which is possible with SQLite >= 3.3.1 (see
SQLite:MultiThreading). OTOH, the [http://www.sqlite.org/sharedcache.html
shared cache] mode doesn't seem to be able to help here, as this is only
about sharing a cache between different connections in ''the same'' thread
and we don't have more than one connection per thread.

Finally, there's also a hint about making use of temporary table, in the
abovementioned wiki page:
''When you use temporary tables, the main database is not locked (...)''
''By creating a temporary table containing the results of a large query
for processing, rather than processing it directly out of the main
database, you greatly reduce lock contentions.''

Using [http://www.sqlite.org/pragma.html#pragma_temp_store in-memory
temporary tables] should be fast.

--
Ticket URL: <http://trac.edgewall.org/ticket/3446#comment:9>
The Trac Project <http://trac.edgewall.org/>

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Trac Tickets" group.
To post to this group, send email to trac-tickets [at] googlegroups
To unsubscribe from this group, send email to trac-tickets-unsubscribe [at] googlegroups
For more options, visit this group at http://groups.google.se/group/trac-tickets
-~----------~----~----~----~------~----~------~--~---

noreply at edgewall

Oct 3, 2006, 3:33 AM

Post #11 of 11 (2214 views)
Permalink

Re: [The Trac Project] #3446: Rate of `database is locked` errors is too high [In reply to]

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads