Queue write transactions instead of erroring out?

[penberg]

@glommer had the idea of queueing write transactions instead of erroring out with SQLITE_BUSY. We could use the sqlite3_stmt_readonly() interface to figure out if a statement is a write or read and queue writes to execute them one at a time. However, the tricky bit is interactive transactions where you could first have some reads before a write, which means you'd need to queue transactions in the middle.

[glommer]

I wrote a PoC of this, in a way that I think it would work: https://github.com/libsql/sqld/compare/main...glommer:sqld:transactions

The idea is that we don't try to be overly smart: BEGIN DEFERRED still could return SQLITE_BUSY, but BEGIN IMMEDIATE could detect immediately that there is already a transaction going on, and wait.

Couple of things missing, that I don't quite know how to implement yet:

• mark transaction as ended if connection is idle
• mark transaction as active if it begins as deferred, but later there is a write.
• other places where I may need to mark the transaction as ended that I may have forgotten.

@psarna wdyt ?

[psarna]

We already set sqlite3_busy_timeout for semi-active polling, so that a transaction can retry for 5 seconds before it gives up, but I suppose we're talking unbounded queuing? If we want this, I suppose a combination of a mutex + a Tokio timeout should suffice, wouldn't it? As in, if you're writing, you first need to obtain the lock. You're writing, if:

• you started a BEGIN IMMEDIATE transaction
• you started a regular deferred transaction some time ago, and now you sent a statement that isn't read-only, i.e. returns false on sqlite3_stmt_readonly
• you just sent a simple write statement (which is an implicit 1-statement transaction anyway, so it's conceptually identical to the previous point)

If we have this kind of mechanism, we should also drop the sqlite3_busy_timeout bit, because it's semi-active wait and it's prone to wasting resources. Are there any substantial corner-cases I missed? @MarinPostma anything comes to mind?

Re comment above:

Couple of things missing, that I don't quite know how to implement yet:

• mark transaction as ended if connection is idle

I think if we write on top of a mutex, the mutex guard would solve it, it will just be dropped in a connection destructor

• mark transaction as active if it begins as deferred, but later there is a write.

If we keep a Option<MutexGuard<...>> in a connection to track when it has a write lock, we just need to obtain the write lock as soon as a write comes, and hold it until the end of the transaction

• other places where I may need to mark the transaction as ended that I may have forgotten.

@psarna wdyt ?

[MarinPostma]

is there not a way for us to make the lock actually wait on libsql side, rather that returning a busy error an then retrying?