Hi John, > How about leveraging the database’s locking? Wrap all of the queries with > dbi_conn_transaction_begin and dbi_conn_transaction_commit. That will make > multi-table writes atomic with an exclusive lock, blocking a reader from > reading them until the whole write is complete, while a read query while > loading will block the exclusive write lock until the read is complete.
That's a helpful reframe. I agree that leaning on the database's own transaction handling is a better foundation than making a read-only session write a row to the gnclock table and complicate the session mode semantics. That's the direction I'm going to research and design a PR around. I don't have the full design nailed down yet, but the shape I'm taking from your suggestion: - Bracket the whole book load in a single transaction, so a read gets a consistent snapshot — this looks like a small, self-contained first step. - Wrap a logical edit (a transaction and its splits) in one transaction, so the multi-object write commits atomically instead of the current behavior of per-instance commits. I want to work through the details before proposing anything concrete. I see friction points around rollback across a multi-instance edit, and how the retry/reconnect path interacts with a longer-lived transaction. I think I'll enjoy working on this; it seems like the right direction. I'll follow up on the list once I have a design worth reviewing. > Please always copy one of the mailing lists when you email one of the GnuCash > developers unless it’s to send something like a GnuCash book; in that case > the email should just have the file, all of the discussion around it should > be copied to the list. I thought I got it right that time; I see my message landed on the list archives. Perhaps the icloud email address obfuscation will be less confusing if I reply only to the list. Cheers, Noah On Sat, Jul 11, 2026 at 4:18 PM John Ralls <[email protected]> wrote: > > Noah, > > Please always copy one of the mailing lists when you email one of the GnuCash > developers unless it’s to send something like a GnuCash book; in that case > the email should just have the file, all of the discussion around it should > be copied to the list. > > I’ve passed on to Derek via IRC the Doxygen-docs problem just in case he > misses it here. > > Yup, readonly-mode is largely cosmetic and applied only to the GUI. GnuCash > is not designed for use by more than one user at a time and read-only mode is > very simplistic. > > I don’t think it would be too hard to extend its force to the backend so that > a read-only session is blocked from writing. But if you want a read-only > session to lock the database then it’s not really read-only anymore. > > How about leveraging the database’s locking? Wrap all of the queries with > dbi_conn_transaction_begin and dbi_conn_transaction_commit. That will make > multi-table writes atomic with an exclusive lock, blocking a reader from > reading them until the whole write is complete, while a read query while > loading will block the exclusive write lock until the read is complete. > > Regards, > John Ralls > > > > > > On Jul 11, 2026, at 12:24 PM, [email protected] wrote: > > > > Thanks for merging #2264 and #2266 John. > > > > Arrg. As I've started to poke more into this read-only and lock > > topic, I've found some surprises and ultimately a correctness matter. > > > > What SESSION_READ_ONLY actually means > > ------------------------------------------------------------ > > > > 1. At the engine level, SESSION_READ_ONLY doesn't actually enforce > > read-only. The mode only affects lock handling -- it does not mark the > > book read-only. Book write-protection is applied separately by the GUI > > only, which calls qof_book_mark_readonly() after opening. I confirmed > > with an API bindings test that a SESSION_READ_ONLY session can still > > edit an account and have the change persisted to the SQL store. So an > > API/bindings consumer that opens SESSION_READ_ONLY expecting the book > > to be unwritable does not get that guarantee unless it also calls > > qof_book_mark_readonly() itself. [a bug if one interprets > > SESSION_READ_ONLY reading the enum name while not carefully parsing > > the header comments ] > > > > 2. SESSION_READ_ONLY ignores the lock. A read-only consumer opens a > > book even while another process holds it open, perhaps mid-write, > > which is exactly when the state can be inconsistent; and because the > > SQL backend commits incrementally, a reader can catch a > > partially-applied edit. [ a read-only client correctness bug ] > > > > So today the mode neither protects read consistency (it ignores the > > lock) nor guarantees read-only-ness (that's a GUI add-on), even though > > the name implies both. > > > > Proposed path forward > > ----------------------------------- > > > > For building read-only tools on the API, what I'd want is a read-only > > session mode that (a) takes the lock, so a writer cannot be active > > while the tool reads, and (b) actually enforces no writes. > > > > A possible direction would be to replace SESSION_READ_ONLY with two > > modes whose names state what they do, and have both mark the book > > read-only so enforcement is intrinsic rather than GUI-only: > > - SESSION_READ_ONLY_EXCLUSIVE -- takes the lock (fails if the book is > > already locked); for tools that need a consistent, uninterrupted read. > > Calls qof_book_mark_readonly(). Obviously a SQL backend user would > > need sufficient permissions to take the lock. I'd want to add some > > user guide documentation about this where none exists today. > > - SESSION_READ_ONLY_IGNORE_LOCK -- opens regardless of the lock state; > > the path the GUI's "open read-only anyway" override would use. (i.e. > > today's SESSION_READ_ONLY behavior, but with qof_book_mark_readonly() > > ) > > > > These could be added now, with SESSION_READ_ONLY marked deprecated, > > and SESSION_READ_ONLY removed in a later release (v6? whatever that > > timeframe turns out to be). I believe the deprecation warning would > > only apply to users of the API `SESSION_READ_ONLY` mode today. (The > > GUI internal use would be swapped to SESSION_READ_ONLY_IGNORE_LOCK.)" > > > > Does folding read-only enforcement into the mode (so bindings > > consumers get it too), plus splitting the lock behavior, sound like an > > OK path? > > > > Aside, is it a known issue Doxygen isn't updating / is stale? (title > > says "GnuCash 5.6-150-g038405b370+" ) > > https://code.gnucash.org/docs/STABLE/qofsession_8h.html > > I was initially looking for the enum descriptions but didn't find them > > outside of the code. Double btw: There is a documentation bug in the > > source here in that the five /** … */ comments need to be trailing > > /**< … */ comments (Doxygen trailing-member syntax). Right now, when > > the comments are rendered, they are offset by one. > > > > Regards, > > > > Noah > > > > > > On Sat, Jul 11, 2026 at 8:47 AM Noah <> wrote: > >> > >> > >> Thanks, John. > >> > >>> Your premise that the database was necessarily written by a GnuCash > >>> session is false. > >> > >> You're right, I was biasing by my own use principles. > >> I never want to modify my production book through anything but the GnuCash > >> GUI or the official GnuCash API / bindings. > >> > >> My household moved our finances to GnuCash 14 years ago motivated by two > >> challenges. > >> - need to support two users on different computers (in serial, not > >> simultaneously). We were bit too many times with filesystem-based > >> commercial options at the time. > >> - a Q* commercial software was dropping support for Mac, or non-cloud, or > >> both > >> > >> In more recent years, our financial institutions have been making it > >> harder to get our transaction data into GnuCash (eg. removing OFX/QFX > >> download) > >> In order to hold the line at home, I've been doing more and more work > >> adopting use of the API & Python bindings to help a lot in this regard. > >> I've also been creating some reporting and exploration tools that don't > >> need write access (and tinkering with other's work like piecash). Since > >> they don’t need write access, I want them to access MySQL with the minimum > >> privileges necessary. > >> > >> > >>> The ffastmath problem underlying bug 611936 was on the write side of > >>> libdii so a read-only test wouldn’t find the problem. > >> > >> That settles it for me: since the -ffast-math defect in #611936 is on the > >> write side of libdbi, a read-only session can neither trigger nor detect > >> it, so running the probe on a read-only open guards nothing. I've opened a > >> PR that skips dbi_library_test() for SESSION_READ_ONLY and leaves all > >> write-capable modes untouched: > >> https://github.com/Gnucash/gnucash/pull/2266 > >> > >> I'm not trying to bolt access control onto GnuCash, and I agree that > >> wouldn't end well. The narrower goal is just to let a read-only consumer > >> honor a least-privilege credential a DBA may already have in place -- and, > >> independently, to stop running a write-only probe on a read-only open. The > >> PR stands on that second part alone. > >> > >> Thanks again, > >> Noah > >> > >> > >> > >> On Thu, Jul 9, 2026 at 8:31 PM John Ralls > >> <[email protected]> wrote: > >>> > >>> > >>>> On Jul 9, 2026, at 14:34, [email protected] wrote: > >>>> Hello again, > >>>> Obviously backend stuff is on my mind lately and there's been a > >>>> quirk impacting read-only access to SQL back-ed books that has > >>>> bothered me for a while. > >>>> Opening a book with SESSION_READ_ONLY through the SQL/DBI backend > >>>> still requires the database user to have permission to create a > >>>> temporary table. On every session_begin (regardless of mode), > >>>> dbi_library_test() runs the 64-bit round-trip guard from Bug #611936 > >>>> -- CREATE TEMPORARY TABLE / INSERT / SELECT / DROP -- to detect a > >>>> 15-year-old -ffast-math libdbi miscompilation. A least-privileged, > >>>> SELECT-only database user therefore cannot open the book at all: the > >>>> create fails and begin returns ERR_SQL_DBI_UNTESTABLE. > >>>> (SESSION_READ_ONLY already skips the gnclock lock, so this probe is > >>>> the only remaining reason a read-only open needs write-ish rights.) > >>>> I'd like to propose making a read-only open work without those > >>>> privileges. Two possible directions: > >>>> 1. Skip dbi_library_test() when the session is opened SESSION_READ_ONLY. > >>>> 2. Or, for read-only, replace it with a check that needs no write > >>>> privileges -- for example, verify the driver retrieves large > >>>> int64/uint64 and a high-precision FLOAT8 via a SELECT of > >>>> literals/casts (dbi_result_get_longlong / get_ulonglong / get_double), > >>>> so the reader's conversion path is still validated. I'd welcome > >>>> suggestions on the best minimal form. > >>>> The justification for either approach: the data already in the > >>>> database was written by a GnuCash session, and dbi_library_test() ran > >>>> for that session, so any book that was successfully saved was > >>>> necessarily saved by a driver that passed the round-trip test -- the > >>>> stored 64-bit and FLOAT8 values are therefore correct at rest. A > >>>> read-only session never writes back, so the only thing left to guard > >>>> against is a reader-side retrieval defect. For a modern 64-bit libdbi > >>>> that is the 2011, 32-bit / -ffast-math case from #611936, which is > >>>> essentially historical (or maybe never existed on the read) > >>>> If there is a preferred direction, I'm happy to prepare a PR. > >>> > >>> Your premise that the database was necessarily written by a GnuCash > >>> session is false. Anybody with db write access (and for a SQLite3 > >>> database that means anyone with write access to the filesystem) can write > >>> (or delete) whatever they want into the database. There is at least one > >>> public Python library that reads and writes GnuCash SQLite3 files > >>> (https://github.com/sdementen/piecash). The databases have no integrity > >>> checks enabled: Any integrity checking (and it’s pretty minimal) is in > >>> GnuCash’s engine. > >>> > >>> The ffastmath problem underlying bug 611936 was on the write side of > >>> libdii so a read-only test wouldn’t find the problem. I think it unlikely > >>> that anyone is using that version of gcc anymore so it’s probably safe to > >>> remove the test. > >>> > >>> That said, I have my doubts about the value of having users without > >>> database write access. Access control hasn’t ever been a consideration in > >>> GnuCash’s design and trying to bolt it on 30 years later isn’t likely to > >>> produce a satisfactory result. > >>> > >>> Regards, > >>> John Ralls > _______________________________________________ gnucash-devel mailing list [email protected] https://lists.gnucash.org/mailman/listinfo/gnucash-devel
