sqlite3: wrap sqlite3_txn_state and sqlite3_stmt_readonly C functions

Feature or enhancement

Proposal:

SQLite is well known for having locking problems, as it's serverless. WAL mode reduces these but does not eliminate them. Debugging SQLITE_BUSY errors is hard since they can be triggered by any Cursor/Connection execute method call, anywhere in the codebase. It can be difficult to identify the blocking process without instrumenting every one of these calls, since it's not obvious from higher up whether a particular query is read-only or read-write, or what locks are already held.

It would be helpful to be able to identify whether our own connection currently has a read lock (SHARED) or a write lock (EXCLUSIVE) and whether it's about to (try to) acquire one. This can be done using the C API functions:

• sqlite3_txn_state (current transaction state/held lock type)
• sqlite3_stmt_readonly (whether executing this statement will try to acquire a write lock)

These functions are not wrapped and not available in Python, not even using ctypes since we can't get access to the raw sqlite3 structure pointer/handle from Python.

I'm imagining being able to use a custom Cursor class to do something like this:

• In overridden execute() method
• If we are about to execute a non-readonly statement
• And we are not already in a write transaction
• Check for another PID in a shared state file (e.g. a lock-type file)
• If there is one, our own attempt to acquire the lock may be delayed or fail, so log that
• Attempt to acquire a write lock with BEGIN IMMEDIATE
• If it was delayed, log the delay
• If it fails, log the PID that was holding the lock before and reraise
• Write our own PID to the shared state file
• Execute the non-readonly statement (this should not now block)
• If we were not in a transaction before, then COMMIT (this may also block as a checkpoint could require exclusive access/no readers) and remove the shared state file

I might be able to submit a PR for this as it should not be too difficult to wrap these C functions. I've been able to use sqlite3_txn_state by creating and loading a custom SQLite3 extension (in C) that creates a custom function that calls this function.

Has this already been discussed elsewhere?

This is a minor feature, which does not need previous discussion elsewhere

Links to previous discussion of this feature:

sqlite3_stmt_readonly used to be used internally by Cursor for a minor feature (the row count indication), but not exposed to Python.

Locate CPython's sqlite3 bindings around Cursor and Connection execution, then review the prior internal use referenced in issue 73541. Confirm how sqlite3_txn_state and sqlite3_stmt_readonly can be exposed to Python and identify the relevant tests. Done means both SQLite C functions are accessible from Python with behavior covered by tests.