(address . guix-patches@gnu.org)
87ftbernat.fsf@cune.org
After some pondering about why the database might be locked so
frequently, this is what I've managed to come up with. The first patch
is the most likely to actually help with that, and the others mostly
involve improving robustness.
Ideally we'd come up with a test to quantify how much these kinds of
changes affect contention over the database. For now, though, all that I
can think of is seeing how this affects the systems that have had issues
with that.
- reepca
From cce653c590be1506e15044e445aa9805370ac759 Mon Sep 17 00:00:00 2001
From: Caleb Ristvedt <caleb.ristvedt@cune.org>
Date: Mon, 1 Jun 2020 18:50:07 -0500
Subject: [PATCH 1/4] database: work around guile-sqlite3 bug preventing
statement reset
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
guile-sqlite3 provides statement caching, making it unnecessary for sqlite to
keep re-preparing statements that are frequently used. Unfortunately it
doesn't quite emulate the semantics of sqlite_finalize properly, because it
doesn't cause a commit if the statement being finalized is the last "active"
statement. We work around this by wrapping sqlite-finalize with our own
version that ensures sqlite-reset is called, which does The Right Thing™.
* guix/store/database.scm (sqlite-finalize): new procedure that shadows the
sqlite-finalize from (sqlite3).
---
guix/store/database.scm | 30 ++++++++++++++++++++++++++++++
1 file changed, 30 insertions(+)
Toggle diff (43 lines)
diff --git a/guix/store/database.scm b/guix/store/database.scm
index ef52036ede..d4251e580e 100644
--- a/guix/store/database.scm
+++ b/guix/store/database.scm
@@ -130,6 +130,36 @@ transaction after it finishes."
If FILE doesn't exist, create it and initialize it as a new database."
(call-with-database file (lambda (db) exp ...)))
+(define (sqlite-finalize stmt)
+ ;; Cached statements aren't reset when sqlite-finalize is invoked on
+ ;; them. This can cause problems with automatically-started transactions:
+ ;;
+ ;; "An implicit transaction (a transaction that is started automatically,
+ ;; not a transaction started by BEGIN) is committed automatically when the
+ ;; last active statement finishes. A statement finishes when its last cursor
+ ;; closes, which is guaranteed to happen when the prepared statement is
+ ;; reset or finalized. Some statements might "finish" for the purpose of
+ ;; transaction control prior to being reset or finalized, but there is no
+ ;; guarantee of this."
+ ;;
+ ;; Thus, it's possible for an implicitly-started transaction to hang around
+ ;; until sqlite-reset is called when the cached statement is next
+ ;; used. Because the transaction is committed automatically only when the
+ ;; *last active statement* finishes, the implicitly-started transaction may
+ ;; later be upgraded to a write transaction (!) and this non-reset statement
+ ;; will still be keeping the transaction from committing until it is next
+ ;; used or the database connection is closed. This has the potential to make
+ ;; (exclusive) write access to the database necessary for much longer than
+ ;; it should be.
+ ;;
+ ;; (see https://www.sqlite.org/lang_transaction.html)
+ ;; To work around this, we wrap sqlite-finalize so that sqlite-reset is
+ ;; always called. This will continue working even when the behavior is fixed
+ ;; in guile-sqlite3, since resetting twice doesn't cause any problems. We
+ ;; can remove this once the fixed guile-sqlite3 is widespread.
+ (sqlite-reset stmt)
+ ((@ (sqlite3) sqlite-finalize) stmt))
+
(define (last-insert-row-id db)
;; XXX: (sqlite3) currently lacks bindings for 'sqlite3_last_insert_rowid'.
;; Work around that.
--
2.26.2
-----BEGIN PGP SIGNATURE-----
iQEzBAEBCAAdFiEEdNapMPRLm4SepVYGwWaqSV9/GJwFAl7V8koACgkQwWaqSV9/
GJwHXgf/R/mpXxFexv9B/V22BwF8+bNmDr6L0KVdFmaKz15nmPbMnnLtqmJTkrik
vbEtwQ9MT4we6yNRD76VaVEe+iE1Yk9WvCyBNUcoPL07dlzY/hN+0frSjQI4MqSc
GIktVbdt6Bf3bloCl5SHg2KrGEYY+ptWliEuY3AfQnBL/7YoSKfd4me5TLa1PB/4
RhyVqVFJHq/jeSQvirDYQoMCiRaDCxu8g+xV5/7ITE3Ue+gkM2aYKSrWz8cFhiJ4
/S3zFZYuI31d2J8jdkbcz5tuVG/ALpTTCIJa8cwIpRB2Yon6yZN4zoDX8ue3P8we
qEnsPdkxSPu7lckVk7OqQNQTvxpknQ==
=2ARa
-----END PGP SIGNATURE-----