Right now, if I execute the following lines in the shell connected with a remote host (e.g. Turso):
BEGIN;
SELECT 42;
COMMIT;
, each of those statements are sent in a separate connection, so COMMIT does not match any transaction and errors out with SQLite error: cannot commit - no transaction is active.
That happens because every single statement is sent in a separate connection. What we should consider instead is to establish a long-living connection when the shell is launched, and send all statements within that single connection (possibly reconnecting if it got closed for any reason). That would allow people to send multi-line interactive transactions, without getting unexpected errors.
The example above is benign, because it just reads from the database, but we can also imagine the following:
BEGIN IMMEDIATE;
INSERT INTO t1 VALUES(0, 'important_info');
INSERT INTO t2 VALUES ('important_info', 'we need both of those inserts to happen atomically!');
COMMIT;
. When user sends those statements, he assumes BEGIN IMMEDIATE started a transaction, and both inserts happen within that transaction. In reality, BEGIN IMMEDIATE was sent in a separate connection that got disconnected later, and both inserts will be sent in separate connections as well, so they don't happen atomically, and if the first succeeds, and the second one fails, the user might get very surprised.
psarna
commented
7 months ago
Right now, if I execute the following lines in the shell connected with a remote host (e.g. Turso):
, each of those statements are sent in a separate connection, so
COMMITdoes not match any transaction and errors out withSQLite error: cannot commit - no transaction is active.That happens because every single statement is sent in a separate connection. What we should consider instead is to establish a long-living connection when the shell is launched, and send all statements within that single connection (possibly reconnecting if it got closed for any reason). That would allow people to send multi-line interactive transactions, without getting unexpected errors.
The example above is benign, because it just reads from the database, but we can also imagine the following:
. When user sends those statements, he assumes
BEGIN IMMEDIATEstarted a transaction, and both inserts happen within that transaction. In reality,BEGIN IMMEDIATEwas sent in a separate connection that got disconnected later, and both inserts will be sent in separate connections as well, so they don't happen atomically, and if the first succeeds, and the second one fails, the user might get very surprised.