From 059825f1698eaf2c282bf5a925feb157954d430f Mon Sep 17 00:00:00 2001
From: Přemysl Eric Janouch <p@janouch.name>
Date: Sat, 20 Jan 2024 18:15:12 +0100
Subject: gallery: add dhashes in one big DB transaction

iotop showed gigabytes of writes for a DB in the order of 100 MB.
---
 main.go | 23 +++++++++++++++++++++--
 1 file changed, 21 insertions(+), 2 deletions(-)

diff --git a/main.go b/main.go
index 0299a14..972e1b3 100644
--- a/main.go
+++ b/main.go
@@ -2390,14 +2390,29 @@ func cmdDhash(fs *flag.FlagSet, args []string) error {
 		}
 	}
 
-	stmt, err := db.Prepare(`UPDATE image SET dhash = ? WHERE sha1 = ?`)
+	// Commits are very IO-expensive in both WAL and non-WAL SQLite,
+	// so write this in one go. For a middle ground, we could batch the updates.
+	tx, err := db.Begin()
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+
+	// Mild hack: upgrade the transaction to a write one straight away,
+	// in order to rule out deadlocks (preventable failure).
+	if _, err := tx.Exec(`END TRANSACTION;
+		BEGIN IMMEDIATE TRANSACTION`); err != nil {
+		return err
+	}
+
+	stmt, err := tx.Prepare(`UPDATE image SET dhash = ? WHERE sha1 = ?`)
 	if err != nil {
 		return err
 	}
 	defer stmt.Close()
 
 	var mu sync.Mutex
-	return parallelize(hexSHA1, func(sha1 string) (message string, err error) {
+	err = parallelize(hexSHA1, func(sha1 string) (message string, err error) {
 		hash, err := makeDhash(sha1)
 		if errors.Is(err, errIsAnimation) {
 			// Ignoring this common condition.
@@ -2411,6 +2426,10 @@ func cmdDhash(fs *flag.FlagSet, args []string) error {
 		_, err = stmt.Exec(int64(hash), sha1)
 		return "", err
 	})
+	if err != nil {
+		return err
+	}
+	return tx.Commit()
 }
 
 // --- Main --------------------------------------------------------------------
-- 
cgit v1.2.3-70-g09d2