Mailing List Archive

#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@lists.edgewall.com
http://lists.edgewall.com/mailman/listinfo/trac-tickets

#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@lists.edgewall.com
http://lists.edgewall.com/mailman/listinfo/trac-tickets

#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):

... and we also shouldn't write into the db when it's not necessary: see
r3616.

It's probably worth to upgrade t.e.o, in order to see how much this
improves the situation.

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

#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@lists.edgewall.com
http://lists.edgewall.com/mailman/listinfo/trac-tickets

#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 |
------------------------------------+---------------------------------------
Comment (by wreese@gmail.com):

Did you guys try setting the busy_timeout on the SQLite connection? It
will cause a process to wait for a lock (up to the timeout) before
throwing the database is locked exception. The wait is usually only a few
milliseconds. It is a super easy way to get your "Repeatable
transactions".

I just submitted a patch to Rails
(http://dev.rubyonrails.org/ticket/6126/) for the same functionality. :)

-- Will Reese

--
Ticket URL: <http://trac.edgewall.org/ticket/3446#comment:5>
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@googlegroups.com
To unsubscribe from this group, send email to trac-tickets-unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.se/group/trac-tickets
-~----------~----~----~----~------~----~------~--~---

#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 |
------------------------------------+---------------------------------------
Comment (by anonymous):

Replying to [comment:5 wreese@gmail.com]:
> Did you guys try setting the busy_timeout on the SQLite connection?
The timeout is set, that helps a bit, but it's by no mean a miracle
solution to the issue... it can even introduce its own problems, like the
dead-locks on concurrent writes. If you wish, you can have a look at the
various documents I've given pointers to in comment:2.

--
Ticket URL: <http://trac.edgewall.org/ticket/3446#comment:6>
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@googlegroups.com
To unsubscribe from this group, send email to trac-tickets-unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.se/group/trac-tickets
-~----------~----~----~----~------~----~------~--~---

#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 anonymous):

* cc: trac@powertrip.co.za (added)

Comment:

I've also seeing this quite a bit.

--
Ticket URL: <http://trac.edgewall.org/ticket/3446#comment:7>
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@googlegroups.com
To unsubscribe from this group, send email to trac-tickets-unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.se/group/trac-tickets
-~----------~----~----~----~------~----~------~--~---

#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 |
------------------------------------+---------------------------------------
Comment (by anonymous):

I have noticed that even this site having the same problem. Seems to be
happening more any more.

--
Ticket URL: <http://trac.edgewall.org/ticket/3446#comment:8>
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@googlegroups.com
To unsubscribe from this group, send email to trac-tickets-unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.se/group/trac-tickets
-~----------~----~----~----~------~----~------~--~---

#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@googlegroups.com
To unsubscribe from this group, send email to trac-tickets-unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.se/group/trac-tickets
-~----------~----~----~----~------~----~------~--~---

#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 |
------------------------------------+---------------------------------------
Comment (by cboos):

Replying to [comment:9 myself]:
> 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).

Implemented in r3830. Not only does it seem to be faster and still
reliable, but '''I've not been able to trigger a single lock''' so far!
More testing welcomed.

--
Ticket URL: <http://trac.edgewall.org/ticket/3446#comment:10>
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@googlegroups.com
To unsubscribe from this group, send email to trac-tickets-unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.se/group/trac-tickets
-~----------~----~----~----~------~----~------~--~---