You can do this be retrieving the value of 'now' and providing it as a bound variable. This work-around will always work. If you do this on the first statement of a transaction, and provide the value so retrieved to every other statement in the transaction that requies it, then you will have achieved transaction stable 'now'.

Modification of the current code to permit 'now' to be statement stable rather than step stable is trivial. Making 'now' transaction stable would require storing 'now' as an attribute of the connection and resetting it every time a transaction was commenced.

The part of the code that retrieves 'now' from the VFS would have to check if the stability is transaction and if so get 'now' from the connection, and if it is not there, then get it from the VFS and store it in the connection. Stability settings other than transaction can bypass this and just get and return 'now' from the VFS as is currently done.

If the stablility was step, then the code path would be unchanged. If the stability were other than step, then the statement 'now' would only be reset on the first entry to the VDBE program.

Presumably there would need to be a pragma added to adjust the stability setting, allowing it to be set to step, statement; or, transaction, and perhaps even a compile-time default which would default to step so that there is no change to the current behaviour.

Presumably there would need to be a pragma added to adjust the stability setting, allowing it to be set to step, statement; or, transaction, and perhaps even a compile-time default which would default to step so that there is no change to the current behaviour.

I think this would be a lovely feature :-)

For example, 'txn' for transaction level (perhaps also 'stp' for step level?)

In honour of SpaceBalls, it should be called 'then'. You can have 'now' when you need 'now', and when you don't need now but then you can have 'then'.

Now. You're looking at now, sir. Everything that happens now, is happening now.

What happened to then?

We passed then.

When?

Just now. We're at now now.

Go back to then.

When?

Now.

Now?

Note that you should probably retrieve the value of julianday() (an IEEE-754 double precision floating point number). You should then be able to bind this value anywhere you would use 'now' and it should work over the entire supported datetime range with no modifier required and no loss of precision.

Anyway, my question is: could eventually SQLite get inspiration from PostgreSQL here?

Unlikely.

How many thousands of applications, do you suppose, do something like this:

BEGIN;
-- wait an hour or so
SELECT datetime('now');

The change you propose would break all such applications. Maybe the application didn't even do an explicit BEGIN, but just failed to step a query until SQLITE_DONE nor finalize or reset that query, thus holding a read transaction open. There might be millions of applications that do that kind of thing, and all of them would break under your proposal.

I think that it would be a huge mistake to change SQLite so that the date/time value was fixed to the start of the current transaction.

You are probably right. Perhaps the answer is to have the capability to cache an ijd in the connection (that is reset whenever a transaction first commences on the connection.

Then add a new modifier to date.c called 'txn' that when accessed looks to the connection ijd and either returns it, or if it is 0 then retrieves the ijd from the VFS, stores it in the connection, then returns the value retrieved.

This would add the ability to have a "transaction time" while preserving the current behaviour of 'now'. It should (I would think) also be rather trivial to implement.

I do not see a non-excessively-complicated and slow way to tie 'now' (or some other modifier) to a "transaction".

Using the autocommit flag would be reasonable though. If you use the new modifier (say 'txn') and autocommit is on, then simply act as if 'now' had been specified. If autocommit is off (a transaction is in process) then use the same process as is used for the ijd cache in the statement except against an ijd in the connection.

Whenever autocommit gets turned off, the connection ijd is reset.

This would work except in the case where there is a "dangling statement" at commit/rollback time. However, having "undefined behaviour" in this case should be OK.

The following patch (which I release into the public domain) implements the 'txn' modifier (the line numbers may not be exactly the same as the distributed version)

Index: src/date.c
==================================================================
--- src/date.c
+++ src/date.c
@@ -379,10 +379,30 @@
     return 0;
   }else{
     return 1;
   }
 }
+
+/*
+** Set the time to the current time reported by the connection.
+**
+** Return the number of errors.
+*/
+static int setDateTimeToTransaction(sqlite3_context *context, DateTime *p){
+  p->iJD = sqlite3ConnCurrentTime(context);
+  if( p->iJD>0 ){
+    p->validJD = 1;
+    p->validYMD = 0;
+    p->validHMS = 0;
+    p->validTZ = 1;
+    p->tz = 0;
+    p->zone = 0;
+    return 0;
+  }else{
+    return 1;
+  }
+}
 
 /*
 ** Input "r" is a numeric quantity which might be a julian day number,
 ** or the number of seconds since 1970.  If the value if r is within
 ** range of a julian day number, install it as such and set validJD.
@@ -439,10 +459,12 @@
     return 0;
   }else if( !context ){
     return 1;   /* bail if no context */
   }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){
     return setDateTimeToCurrent(context, p);
+  }else if( sqlite3StrICmp(zDate,"txn")==0 && sqlite3NotPureFunc(context) ){
+    return context->pVdbe->db->autoCommit ? setDateTimeToCurrent(context, p) : setDateTimeToTransaction(context, p);
 #ifndef SQLITE_OMIT_LOCALTIME
   }else if( sqlite3StrICmp(zDate, "local")==0 && sqlite3NotPureFunc(context) ){
     if(setDateTimeToCurrent(context, p)==0) return toLocaltime(p, context);
 #endif
   }else if( strchr(zDate, '/')>0 ) {

Index: src/sqliteInt.h
==================================================================
--- src/sqliteInt.h
+++ src/sqliteInt.h
@@ -1748,10 +1748,11 @@
   sqlite3 *pNextBlocked;        /* Next in list of all blocked connections */
 #endif
 #ifdef SQLITE_USER_AUTHENTICATION
   sqlite3_userauth auth;        /* User authentication information */
 #endif
+  i64 iCurrentTime;             /* Connection ijd cache */
 };
 
 #ifdef SQLITE_ENABLE_SHARED_SCHEMA
 # define IsSharedSchema(db) (((db)->openFlags & SQLITE_OPEN_SHARED_SCHEMA)!=0)
 #else
@@ -5400,10 +5401,11 @@
 #if (defined(SQLITE_ENABLE_DBPAGE_VTAB) || defined(SQLITE_TEST)) \
     && !defined(SQLITE_OMIT_VIRTUALTABLE)
   void sqlite3VtabUsesAllSchemas(sqlite3_index_info*);
 #endif
 sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context*);
+sqlite3_int64 sqlite3ConnCurrentTime(sqlite3_context*);
 int sqlite3VdbeParameterIndex(Vdbe*, const char*, int);
 int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *);
 void sqlite3ParseObjectInit(Parse*,sqlite3*);
 void sqlite3ParseObjectReset(Parse*);
 void *sqlite3ParserAddCleanup(Parse*,void(*)(sqlite3*,void*),void*);

Index: src/vdbe.c
==================================================================
--- src/vdbe.c
+++ src/vdbe.c
@@ -3827,10 +3827,12 @@
       rc = SQLITE_BUSY;
       goto abort_due_to_error;
     }else if( (rc = sqlite3VdbeCheckFk(p, 1))!=SQLITE_OK ){
       goto vdbe_return;
     }else{
+      if( !desiredAutoCommit && db->autoCommit )
+        db->iCurrentTime = 0;
       db->autoCommit = (u8)desiredAutoCommit;
     }
     if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
       p->pc = (int)(pOp - aOp);
       db->autoCommit = (u8)(1-desiredAutoCommit);

Index: src/vdbeapi.c
==================================================================
--- src/vdbeapi.c
+++ src/vdbeapi.c
@@ -978,10 +978,33 @@
   assert( p->pVdbe!=0 );
 #else
   sqlite3_int64 iTime = 0;
   sqlite3_int64 *piTime = p->pVdbe!=0 ? &p->pVdbe->iCurrentTime : &iTime;
 #endif
+  if( *piTime==0 ){
+    rc = sqlite3OsCurrentTimeInt64(p->pOut->db->pVfs, piTime);
+    if( rc ) *piTime = 0;
+  }
+  return *piTime;
+}
+
+/*
+** Return the current time for a connection.  If the current time
+** is requested more than once within the same run of a transaction,
+** the exact same time is returned for each invocation regardless
+** of the amount of time that elapses between invocations.  In other words,
+** the time returned is always the time of the first call.
+*/
+sqlite3_int64 sqlite3ConnCurrentTime(sqlite3_context *p){
+  int rc;
+#ifndef SQLITE_ENABLE_STAT4
+  sqlite3_int64 *piTime = &p->pVdbe->db->iCurrentTime;
+  assert( p->pVdbe!=0 );
+#else
+  sqlite3_int64 iTime = 0;
+  sqlite3_int64 *piTime = p->pVdbe!=0 ? &p->pVdbe->db->iCurrentTime : &iTime;
+#endif
   if( *piTime==0 ){
     rc = sqlite3OsCurrentTimeInt64(p->pOut->db->pVfs, piTime);
     if( rc ) *piTime = 0;
   }
   return *piTime;

with the following results: (note that my date.c is customized to be timezone aware so the output appears somewhat different that the standard date.c)

sqlite> select datetime('now'), datetime('txn');
┌─────────────────────────────┬─────────────────────────────┐
│       datetime('now')       │       datetime('txn')       │
├─────────────────────────────┼─────────────────────────────┤
│ '2023-02-05 23:54:10.317 Z' │ '2023-02-05 23:54:10.317 Z' │
└─────────────────────────────┴─────────────────────────────┘
sqlite> select datetime('now'), datetime('txn');
┌─────────────────────────────┬─────────────────────────────┐
│       datetime('now')       │       datetime('txn')       │
├─────────────────────────────┼─────────────────────────────┤
│ '2023-02-05 23:54:13.948 Z' │ '2023-02-05 23:54:13.948 Z' │
└─────────────────────────────┴─────────────────────────────┘
sqlite> select datetime('now'), datetime('txn');
┌─────────────────────────────┬─────────────────────────────┐
│       datetime('now')       │       datetime('txn')       │
├─────────────────────────────┼─────────────────────────────┤
│ '2023-02-05 23:54:20.261 Z' │ '2023-02-05 23:54:20.261 Z' │
└─────────────────────────────┴─────────────────────────────┘
sqlite> begin;
sqlite> select datetime('now'), datetime('txn');
┌─────────────────────────────┬─────────────────────────────┐
│       datetime('now')       │       datetime('txn')       │
├─────────────────────────────┼─────────────────────────────┤
│ '2023-02-05 23:54:27.037 Z' │ '2023-02-05 23:54:27.038 Z' │
└─────────────────────────────┴─────────────────────────────┘
sqlite> select datetime('now'), datetime('txn');
┌─────────────────────────────┬─────────────────────────────┐
│       datetime('now')       │       datetime('txn')       │
├─────────────────────────────┼─────────────────────────────┤
│ '2023-02-05 23:54:31.836 Z' │ '2023-02-05 23:54:27.038 Z' │
└─────────────────────────────┴─────────────────────────────┘
sqlite> select datetime('now'), datetime('txn');
┌─────────────────────────────┬─────────────────────────────┐
│       datetime('now')       │       datetime('txn')       │
├─────────────────────────────┼─────────────────────────────┤
│ '2023-02-05 23:54:39.374 Z' │ '2023-02-05 23:54:27.038 Z' │
└─────────────────────────────┴─────────────────────────────┘
sqlite> commit;
sqlite> select datetime('now'), datetime('txn');
┌─────────────────────────────┬─────────────────────────────┐
│       datetime('now')       │       datetime('txn')       │
├─────────────────────────────┼─────────────────────────────┤
│ '2023-02-05 23:54:48.125 Z' │ '2023-02-05 23:54:48.125 Z' │
└─────────────────────────────┴─────────────────────────────┘
sqlite> select datetime('now'), datetime('txn');
┌─────────────────────────────┬─────────────────────────────┐
│       datetime('now')       │       datetime('txn')       │
├─────────────────────────────┼─────────────────────────────┤
│ '2023-02-05 23:54:50.694 Z' │ '2023-02-05 23:54:50.694 Z' │
└─────────────────────────────┴─────────────────────────────┘
sqlite>

Note that in date.c setDateTimeToTransaction is merely a copy of setDateTimeToCurrent with the sqlite3StmtCurrentTime changed to sqlite3ConnCurrentTime, in case of difficulties figuring out what datetime struct elements are being set.

Also, the lines following the insertion where 'txn' is recognized may be different. I have added additional options that do not appear in the distributed date.c.

Similarly, sqlite3ConnCurrentTime is merely a copy of sqlite3StmtCurrentTime that uses an iCurrentTime that is attached to the connection rather than the statement. The header line is also duplicated and the name changed in sqliteInt.h and an iCurrentTime member is added to the connection structure.

This seems to be the most straightforward implementation and it seems to work. If autocommit is on then 'txn' is treated as if 'now' had been specified.

Note that it is alos possible for a savepoint (OP_Savepoint) to start a trnasaction and turn autocommit off, without calling OP_autocommit. You have to also reset the iCurrentTime in the connection there as well (hint: search vdbe.c for ->autocommit = 0;

I don't think a savepoint should change a per-transaction timestamp.

Because from the outside they are invisible. Savepoints are an implementation
detail to possibly partially rewind an ongoing transaction; anything that follows
a savepoint is still logically part of the same transaction. So savepoints should
have no bearing on a per-transaction timestamp.

Of course, the above is a logical view of this issue. Independent of any implementation.

A savepoint can be used to start a transaction, not merely inside a transaction.

The point is moot however, as it looks like the way Richard's patch will work is that 'txn' will also be stable for implicit transactions. That is if you do something line:

for row in db.execute('select x from x'):
   db.execute('update y set t=datetime('txn') where x=?, (row.x,))

that the 'txn' time will remain constant during the entire implicit transaction.

At least I think it does, but maybe I am wrong. Maybe it resets the txn time every time a statement ends when autocommit is on notwithstanding the transaction state.

A savepoint can be used to start a transaction, not merely inside a transaction.

OK, thanks. I didn't know or remember that. To me, that's mostly irrelevant, and a degenerate case of savepoints I guess.

Since I'm currently working on a similar feature for http://github.com/groue/GRDB.swift, I was also wondering when the "transaction date" should be set.

It could be at the exit of the auto commit mode, as you suggest, or when a SHARED+ lock is acquired, or when the "current transaction date" is first used:

BEGIN: -- There? Exit autocommit mode
SELECT * FROM t; -- There? SHARED lock acquisition
SELECT datetime('now') -- There? First use of the value

Since I don't think SQLite exposes any hook for knowing when a lock is acquired, I, as a SQLite user, can only choose between the autocommit mode exit, and the first use of the value.

I'm still struggling comparing the benefits and drawbacks of both options.

Currently the patch only resets (clears) the ijd time cached in the connection when the connection traverses an autocommit state change from on->off. State changes on->on, off->on, off->off are ignored. The first use determines the persisted value.

Also note that if whatever function is accessing the timesources ('now' or 'txn') may be subject to constant optimization since the datetime functions are all constant functions: so even though datetime('now') is only step stable, it is a constant so all steps in the same statement will return the same value. Unless, of course, you make it non-constant (such as select datetime('now', (value-value) || ' days') from generate_series(0,100000) which requires that the datetime function be executed for each step rather than be subject to constant optimization; select datetime('now') from generate_series(0,1000000) will return the same value all the time since datetime('now') is constant and optimized as a constant.

I do not see any reason why you could not "set the time" when autocommit transitions on->off rather than just reset it. You would just be retrieving the ijd from the VFS and storing it rather than storing 0.

I long ago made a modification so that 'now' was truly statement stable (reset only when the pc==0 on entry to the vdbe code by adding a simple if(p->pc==0) before the p->iCurrentTime = 0;) and added a new 'stp' where the value (iStepTime) is reset each time the vdbe code is entered (that is, on initial entry and when resumed from producing a row).

The Python test code:

import mpsw
import time

getx = "select x, datetime('stp', (x-x) || ' days') as StepStable, datetime('now', (x-x) || ' days') as StmtStable, datetime('txn', (x-x) || ' days') as TxnStable from x"

db = mpsw.Connection()
db.execute('create table x(x); insert into x values (1),(2),(3),(4),(5),(6)')

print('Default Autocommit Mode')
for row in db.execute(getx):
    print(row)
    time.sleep(1)
print('begin transaction')
db.begin()
for row in db.execute(getx):
    print(row)
    time.sleep(1)
print('another statement, same transaction')
for row in db.execute(getx):
    print(row)
    time.sleep(1)
print('commit transaction')
db.commit()
for row in db.execute(getx):
    print(row)
    time.sleep(1)
print('begin transaction')
db.begin()
for row in db.execute(getx):
    print(row)
    time.sleep(1)
print('hanging transaction')
cr = db.execute(getx)
print(next(cr))
time.sleep(1)
print(next(cr))
time.sleep(1)
print(next(cr))
time.sleep(1)
print('rollback transaction')
db.rollback()
print(next(cr))
time.sleep(1)
print(next(cr))
time.sleep(1)
print(next(cr))
time.sleep(1)
cr.close()
print('New Statement Execution')
for row in db.execute(getx):
    print(row)
    time.sleep(1)

which outputs

>test
Default Autocommit Mode
Row(x=1, stepstable='2023-02-06 19:17:31.254 Z', stmtstable='2023-02-06 19:17:31.254 Z', txnstable='2023-02-06 19:17:31.254 Z')
Row(x=2, stepstable='2023-02-06 19:17:32.255 Z', stmtstable='2023-02-06 19:17:31.254 Z', txnstable='2023-02-06 19:17:31.254 Z')
Row(x=3, stepstable='2023-02-06 19:17:33.255 Z', stmtstable='2023-02-06 19:17:31.254 Z', txnstable='2023-02-06 19:17:31.254 Z')
Row(x=4, stepstable='2023-02-06 19:17:34.256 Z', stmtstable='2023-02-06 19:17:31.254 Z', txnstable='2023-02-06 19:17:31.254 Z')
Row(x=5, stepstable='2023-02-06 19:17:35.257 Z', stmtstable='2023-02-06 19:17:31.254 Z', txnstable='2023-02-06 19:17:31.254 Z')
Row(x=6, stepstable='2023-02-06 19:17:36.258 Z', stmtstable='2023-02-06 19:17:31.254 Z', txnstable='2023-02-06 19:17:31.254 Z')
begin transaction
Row(x=1, stepstable='2023-02-06 19:17:37.260 Z', stmtstable='2023-02-06 19:17:37.260 Z', txnstable='2023-02-06 19:17:37.260 Z')
Row(x=2, stepstable='2023-02-06 19:17:38.261 Z', stmtstable='2023-02-06 19:17:37.260 Z', txnstable='2023-02-06 19:17:37.260 Z')
Row(x=3, stepstable='2023-02-06 19:17:39.262 Z', stmtstable='2023-02-06 19:17:37.260 Z', txnstable='2023-02-06 19:17:37.260 Z')
Row(x=4, stepstable='2023-02-06 19:17:40.263 Z', stmtstable='2023-02-06 19:17:37.260 Z', txnstable='2023-02-06 19:17:37.260 Z')
Row(x=5, stepstable='2023-02-06 19:17:41.264 Z', stmtstable='2023-02-06 19:17:37.260 Z', txnstable='2023-02-06 19:17:37.260 Z')
Row(x=6, stepstable='2023-02-06 19:17:42.265 Z', stmtstable='2023-02-06 19:17:37.260 Z', txnstable='2023-02-06 19:17:37.260 Z')
another statement, same transaction
Row(x=1, stepstable='2023-02-06 19:17:43.266 Z', stmtstable='2023-02-06 19:17:43.266 Z', txnstable='2023-02-06 19:17:37.260 Z')
Row(x=2, stepstable='2023-02-06 19:17:44.268 Z', stmtstable='2023-02-06 19:17:43.266 Z', txnstable='2023-02-06 19:17:37.260 Z')
Row(x=3, stepstable='2023-02-06 19:17:45.268 Z', stmtstable='2023-02-06 19:17:43.266 Z', txnstable='2023-02-06 19:17:37.260 Z')
Row(x=4, stepstable='2023-02-06 19:17:46.269 Z', stmtstable='2023-02-06 19:17:43.266 Z', txnstable='2023-02-06 19:17:37.260 Z')
Row(x=5, stepstable='2023-02-06 19:17:47.270 Z', stmtstable='2023-02-06 19:17:43.266 Z', txnstable='2023-02-06 19:17:37.260 Z')
Row(x=6, stepstable='2023-02-06 19:17:48.271 Z', stmtstable='2023-02-06 19:17:43.266 Z', txnstable='2023-02-06 19:17:37.260 Z')
commit transaction
Row(x=1, stepstable='2023-02-06 19:17:49.272 Z', stmtstable='2023-02-06 19:17:49.272 Z', txnstable='2023-02-06 19:17:49.272 Z')
Row(x=2, stepstable='2023-02-06 19:17:50.273 Z', stmtstable='2023-02-06 19:17:49.272 Z', txnstable='2023-02-06 19:17:49.272 Z')
Row(x=3, stepstable='2023-02-06 19:17:51.274 Z', stmtstable='2023-02-06 19:17:49.272 Z', txnstable='2023-02-06 19:17:49.272 Z')
Row(x=4, stepstable='2023-02-06 19:17:52.275 Z', stmtstable='2023-02-06 19:17:49.272 Z', txnstable='2023-02-06 19:17:49.272 Z')
Row(x=5, stepstable='2023-02-06 19:17:53.276 Z', stmtstable='2023-02-06 19:17:49.272 Z', txnstable='2023-02-06 19:17:49.272 Z')
Row(x=6, stepstable='2023-02-06 19:17:54.277 Z', stmtstable='2023-02-06 19:17:49.272 Z', txnstable='2023-02-06 19:17:49.272 Z')
begin transaction
Row(x=1, stepstable='2023-02-06 19:17:55.278 Z', stmtstable='2023-02-06 19:17:55.278 Z', txnstable='2023-02-06 19:17:55.278 Z')
Row(x=2, stepstable='2023-02-06 19:17:56.279 Z', stmtstable='2023-02-06 19:17:55.278 Z', txnstable='2023-02-06 19:17:55.278 Z')
Row(x=3, stepstable='2023-02-06 19:17:57.280 Z', stmtstable='2023-02-06 19:17:55.278 Z', txnstable='2023-02-06 19:17:55.278 Z')
Row(x=4, stepstable='2023-02-06 19:17:58.281 Z', stmtstable='2023-02-06 19:17:55.278 Z', txnstable='2023-02-06 19:17:55.278 Z')
Row(x=5, stepstable='2023-02-06 19:17:59.282 Z', stmtstable='2023-02-06 19:17:55.278 Z', txnstable='2023-02-06 19:17:55.278 Z')
Row(x=6, stepstable='2023-02-06 19:18:00.282 Z', stmtstable='2023-02-06 19:17:55.278 Z', txnstable='2023-02-06 19:17:55.278 Z')
hanging transaction
Row(x=1, stepstable='2023-02-06 19:18:01.284 Z', stmtstable='2023-02-06 19:18:01.284 Z', txnstable='2023-02-06 19:17:55.278 Z')
Row(x=2, stepstable='2023-02-06 19:18:02.285 Z', stmtstable='2023-02-06 19:18:01.284 Z', txnstable='2023-02-06 19:17:55.278 Z')
Row(x=3, stepstable='2023-02-06 19:18:03.286 Z', stmtstable='2023-02-06 19:18:01.284 Z', txnstable='2023-02-06 19:17:55.278 Z')
rollback transaction
Row(x=4, stepstable='2023-02-06 19:18:04.288 Z', stmtstable='2023-02-06 19:18:01.284 Z', txnstable='2023-02-06 19:18:01.284 Z')
Row(x=5, stepstable='2023-02-06 19:18:05.289 Z', stmtstable='2023-02-06 19:18:01.284 Z', txnstable='2023-02-06 19:18:01.284 Z')
Row(x=6, stepstable='2023-02-06 19:18:06.290 Z', stmtstable='2023-02-06 19:18:01.284 Z', txnstable='2023-02-06 19:18:01.284 Z')
New Statement Execution
Row(x=1, stepstable='2023-02-06 19:18:07.291 Z', stmtstable='2023-02-06 19:18:07.291 Z', txnstable='2023-02-06 19:18:07.291 Z')
Row(x=2, stepstable='2023-02-06 19:18:08.297 Z', stmtstable='2023-02-06 19:18:07.291 Z', txnstable='2023-02-06 19:18:07.291 Z')
Row(x=3, stepstable='2023-02-06 19:18:09.298 Z', stmtstable='2023-02-06 19:18:07.291 Z', txnstable='2023-02-06 19:18:07.291 Z')
Row(x=4, stepstable='2023-02-06 19:18:10.299 Z', stmtstable='2023-02-06 19:18:07.291 Z', txnstable='2023-02-06 19:18:07.291 Z')
Row(x=5, stepstable='2023-02-06 19:18:11.300 Z', stmtstable='2023-02-06 19:18:07.291 Z', txnstable='2023-02-06 19:18:07.291 Z')
Row(x=6, stepstable='2023-02-06 19:18:12.301 Z', stmtstable='2023-02-06 19:18:07.291 Z', txnstable='2023-02-06 19:18:07.291 Z')

Note that I have forced the datetime function to be executed each time it is called by making it non-constant. If it is constant (you remove the (x-x) || ' days' from the datetime calls, then the 'stp' is the same as the 'now' results.

All right. I don't think SQLite notifies the host application of autocommit state changes, but I can track BEGIN statement executions, thanks to the Compile-Time Authorization Callbacks. This is the best approximation of autocommit state changes I have. Note: I'm not writing SQL, I'm writing a Swift library for accessing SQLite. And I need to help my library users have access to a timestamp that is constant during a transaction.

Thank you for your feedback.

Frankly, I did did not propose anything. By focusing on a wrong interpretation of my message, and making me sound like I'm suggesting breaking changes (come on), you don't address the real topic, and I regret it.

This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp.

This is the real topic.

I was wary of the change for the alias CURRENT_TIMESTAMP because applications might, as you had suggested have relied on the timestamp to be 'now'.

The SQLite documentation makes no mention of how long CURRENT_TIMESTAMP is stable. So we are technically free to change it. Changing to the ANSI-SQL standard behavior seems like a bug fix.

If you have a bunch of things tied to a specific transaction, maybe what you should be recording is a transaction ID.

CURRENT_TIMESTAMP is an ANSI-SQL Standard variable you will find in many relational databases including PostgreSQL, SQL Server, Firebird, IBM DB2 and MySQL to name a few that records the start of the transaction. The important thing to keep in mind about it is there is only one entry per transaction so if you have a long running transaction, you won't be seeing it changing as you go along.

Most database system show a consistent behaviour, while SQLite prefers to differ.

clock_timestamp() is a PostgreSQL function that always returns the current clock's timestamp.

The change to implement 'txn' has been backout out of the SQLite trunk and is not likely to appear in 3.41.0.

The difficulty is determining what is meant by a "transaction" for the purposes of deciding when to allow the 'txn' value to change. Consider this TCL example:

file delete -force test.db
sqlite3 dbA test.db
dbA eval {
  CREATE TABLE t2(x);
  CREATE TABLE t3(y);
}
sqlite3 dbB test.db
dbA eval {VALUES(1),(2),(3)} {
  dbA eval {INSERT INTO t2(x) VALUES(julianday('txn'));}
  dbB eval {INSERT INTO t3(y) SELECT count(*) FROM t2;}
  after 10
}
puts [dbA eval {SELECT count(x), count(DISTINCT x) FROM t2}]
puts [dbA eval {SELECT * FROM t3;}]

Does the duration of the VALUES statement constitute a "transaction" for the purposes of 'txn'? That statement does not interact with any database file. The dbB connection is able to make multiple updates to the database while the VALUES statement is running.

Part of the problem is that SQLite does not have the concept of a transaction at the database connection level. Individual database files are either in a transaction or not. But a single SQLite database connection can be talking to multiple database files at once. So how do we define a connection-level transaction? Is it when one or more files have an active transaction? Do read-transactions count, or should we only count write-transactions? What do you do with the VALUES statement that does no database I/O?

These problems don't come up as severely with client/server database engines because client/server systems are not re-entrant in the way that SQLite is. The TCL script above is able to start and run new SQL statements in the middle of other SQL statements. By contrast, SQL statements in a single PostgreSQL database connection are strictly serialized - one must run to completion before the next one starts by virtual of the client/server protocol.

My idea was to define "transaction" for the purpose of the 'txn' date/time format as either:

1. The duration of the containing BEGIN ... COMMIT, if there is one.

2. The duration of a single SQL statement if there is not an explicit transaction started using BEGIN (or SAVEPOINT).

But not everybody agrees with that definition. So for now, I think the best approach is to simply abandon the whole idea.

When you "close" a VM and autocommit is on, could you not scan all the attached Btree and only reset txnTime if sum(inTrans) == 0?

ie, something line this:

if (db->autocommit) {
   int transaction = 0;
   for (int ii=0; ii<db->nDb; ii++)
      transaction += db->aDb[ii]->pBt->inTrans;
   if (!transaction)
     db->txnTime = 0;
}

This would add some cycles to the closing of every VM however it would seem to work for any transaction type on the connection (read or write). Presumably one would expect that transaction time is stable for an entire read transaction (ie, repeatable read works properly) as well as write transactions.

Yes, certainly seems like sound reasoning to me.

A delay in rolling ‘txn’ time for/until folks coalesce around a common definition seems wise. I think your definition is quite workable and once folks have time to work through it may come around.

My idea was to define "transaction" for the purpose of the 'txn' date/time format as [...]

Completely agree.

But not everybody agrees with that definition

Well, what are their definitions?
Are these discussions taking place on a non-public forum? Bummer...

Whether a statement touches a DB file or not, or several for that matter, is irrelevant IMHO.
What matters is the client-side session issuing the SQL, and whether there's a transaction started,
either explicit for multiple statements, or implicit for a single statement, as you wrote.

The connection does serialize the SQL issued. So there is a non-ambiguous way to delimit transactions.
The fact the data-sphere these SQL operate on/in being 0, 1, or several DB files does not matter IMHO.

For what it's worth. This is a disappointing development. I haven't read arguments to convince me otherwise yet.

As I see it, the two conflicting definitions of a transaction are:

1. A transaction is started by BEGIN or an initial SAVEPOINT (ie, autocommit is turned off). Such a transaction ends when a statement completes execution and autocommit is on.

2. A single statement with autocommit on comprises a transaction.

The summary version of this interpretation is that a transaction commences whenever the transaction time is first accessed after being reset, and the transaction time is reset if a statement ends execution and autocommit is on.

However, this excludes the following situtations:

1. After a transaction is explicitly commenced by BEGIN or SAVEPOINT and autocommit is turned off, a statement may be executed "across" the COMMIT/ROLLBACK. In this instance the transaction does not end when the COMMIT/ROLLBACK statement is completed, even though autocommit is turned back on. The transaction state persists until all statements (that access any database) that were commenced inside the transaction terminate. This includes statements accessing any database that commenced execution after the COMMIT/ROLLBACK but while the transaction was held open by a statement that started inside the explicit transaction.

2. A statement accessing a database may be executed implicitly and, interspersed with the stepping of that statement, other statements accessing a database may be executed, that are part of the same transaction (notwithstanding that autocommit is turned on the whole time). The transaction persists from the time of the first statement accessing any database until the last statement accessing any database completes.

That is to say specifically that in this model a transaction commences whenever the transaction time is first access after being reset, and the transaction time is reset when a statement completes execution and autocommit is on and the transaction state of all databases attached to the connection is TXNSTATE_NONE.

While the definition of a "transaction" for the purposes of 'txn' time may be arbitrarily said to be reset "when a statement ends and autocommit is on", this definition is not dependant on a transaction but rather autocommit and should properly be called 'act' (autocommit time).

Note Edited to make clear that transaction state is only commenced/extended by statements accessing a databse. Statements which do not access a database do not affect transaction state.

The connection does serialize the SQL issued.

This is incorrect. The SQL statments are not serialized. The stepping (production of results, entries into the library) are serialized.

You may execute multiple overlapping statements on a connection, you can simply only "retrieve a result row" from one of them at a time. It is the calling of the library that it serialized, not the SQL statements.

OK, you are technically correct.

BUT... stepping a BEGIN or COMMIT statement is a single step().

Thus all other step()s on that connection come before or after those.
Thus it is unambiguous whether a step() is within a transaction or not.
Thus the meaning of julianday('txn') is unambiguous, for any given step().

The fact you can interleave step() of different SQL is beside the point.
You are right that the SQL are not strictly ordered, given that, but I do
believe that is irrelevant for a transaction-stable timestamp.

Just because you issue a COMMIT does not mean that the transaction is ended. If their are statements "open" (ie, that have not completed) when the COMMIT is executed, then the transaction remains open.

You can confirm this yourself by doing exactly that and using the sqlite3_txn_state API to get the transaction state.

import mpsw

db = mpsw.Connection()

print(f"txn_state={db.txn_state()}, autocommit={db.autocommit}, before BEGIN")
db.execute('BEGIN')
print(f"txn_state={db.txn_state()}, autocommit={db.autocommit}, after BEGIN")
cr = db.execute('select value from generate_series(1,1000)')
print(f"step={next(cr)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
db.execute('COMMIT')
print(f"txn_state={db.txn_state()}, autocommit={db.autocommit}, after COMMIT")
print(f"step={next(cr)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
cr.close()
print(f"txn_state={db.txn_state()}, autocommit={db.autocommit}, after statement close")

txn_state=0, autocommit=True, before BEGIN
txn_state=0, autocommit=False, after BEGIN
step=Row(value=1), txn_state=1, autocommit=False
step=Row(value=2), txn_state=1, autocommit=False
step=Row(value=3), txn_state=1, autocommit=False
txn_state=1, autocommit=True, after COMMIT
step=Row(value=4), txn_state=1, autocommit=True
step=Row(value=5), txn_state=1, autocommit=True
txn_state=0, autocommit=True, after statement close

but I do believe that is irrelevant for a transaction-stable timestamp.

So you want a "transaction stable timestamp" that has nothing whatsoever to do with transactions? Why would you call it a "transaction stable timestamp" then?

Note also the case for overlapping statements with autocommit on:

import mpsw

db = mpsw.Connection()

print(f"txn_state={db.txn_state()}, autocommit={db.autocommit}")
cr = db.execute('select value from generate_series(1,1000)')
print(f"step={next(cr)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
cr2 = db.execute('select value from generate_series(1001,2000)')
print(f"step={next(cr)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr)}, step={next(cr2)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr)}, step={next(cr2)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr)}, step={next(cr2)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
cr.close()
print(f"txn_state={db.txn_state()}, autocommit={db.autocommit}, after statement1 close")
print(f"step={next(cr2)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr2)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr2)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
cr3 = db.execute('select value from generate_series(2001, 3000)')
print(f"step={next(cr2)}, step={next(cr3)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr2)}, step={next(cr3)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr2)}, step={next(cr3)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
cr2.close()
print(f"txn_state={db.txn_state()}, autocommit={db.autocommit}, after statement2 close")
print(f"step={next(cr3)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr3)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr3)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
cr3.close()
print(f"txn_state={db.txn_state()}, autocommit={db.autocommit}, after statement3 close")

txn_state=0, autocommit=True
step=Row(value=1), txn_state=1, autocommit=True
step=Row(value=2), txn_state=1, autocommit=True
step=Row(value=3), txn_state=1, autocommit=True
step=Row(value=4), txn_state=1, autocommit=True
step=Row(value=5), step=Row(value=1001), txn_state=1, autocommit=True
step=Row(value=6), step=Row(value=1002), txn_state=1, autocommit=True
step=Row(value=7), step=Row(value=1003), txn_state=1, autocommit=True
txn_state=1, autocommit=True, after statement1 close
step=Row(value=1004), txn_state=1, autocommit=True
step=Row(value=1005), txn_state=1, autocommit=True
step=Row(value=1006), txn_state=1, autocommit=True
step=Row(value=1007), step=Row(value=2001), txn_state=1, autocommit=True
step=Row(value=1008), step=Row(value=2002), txn_state=1, autocommit=True
step=Row(value=1009), step=Row(value=2003), txn_state=1, autocommit=True
txn_state=1, autocommit=True, after statement2 close
step=Row(value=2004), txn_state=1, autocommit=True
step=Row(value=2005), txn_state=1, autocommit=True
step=Row(value=2006), txn_state=1, autocommit=True
txn_state=0, autocommit=True, after statement3 close

You will note that in this case also the transaction state persists from the first step of the first statement until all the statements are completed.

Note also tht you can combine the two cases together and hang that dangling transaction as long as you have overlapping statement steps.

import mpsw

db = mpsw.Connection()

print(f"txn_state={db.txn_state()}, autocommit={db.autocommit}, before BEGIN")
db.execute('BEGIN')
print(f"txn_state={db.txn_state()}, autocommit={db.autocommit}, after BEGIN")
cr = db.execute('select value from generate_series(1,1000)')
print(f"step={next(cr)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
db.execute('COMMIT')
print(f"txn_state={db.txn_state()}, autocommit={db.autocommit}, after COMMIT")
print(f"step={next(cr)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
cr2 = db.execute('select value from generate_series(1001,2000)')
print(f"step={next(cr)}, step={next(cr2)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
cr.close()
print(f"txn_state={db.txn_state()}, autocommit={db.autocommit}, after statement1 close")
print(f"step={next(cr2)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
print(f"step={next(cr2)}, txn_state={db.txn_state()}, autocommit={db.autocommit}")
cr2.close()
print(f"txn_state={db.txn_state()}, autocommit={db.autocommit}, after statement2 close")

txn_state=0, autocommit=True, before BEGIN
txn_state=0, autocommit=False, after BEGIN
step=Row(value=1), txn_state=1, autocommit=False
step=Row(value=2), txn_state=1, autocommit=False
step=Row(value=3), txn_state=1, autocommit=False
txn_state=1, autocommit=True, after COMMIT
step=Row(value=4), txn_state=1, autocommit=True
step=Row(value=5), step=Row(value=1001), txn_state=1, autocommit=True
txn_state=1, autocommit=True, after statement1 close
step=Row(value=1002), txn_state=1, autocommit=True
step=Row(value=1003), txn_state=1, autocommit=True
txn_state=0, autocommit=True, after statement2 close

So here is a combined version that runs all the possibilites and also displays the 'txn' datetime. The calls to datetime include a (value-value)||' days' modifier so that the datetime is not a constant and must be re-retrieved on every access. The patch to provide the txntime is the patch that was backed out. Note the sleep(1) after each row is accessed to ensure that the actual step time is different.

You will note that the txntime changes even though we are still in a transaction.

import mpsw
import time

db = mpsw.Connection()

print('Simple Statement')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}")
cr = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1,1000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement1 close")

print()
print('Simple BEGIN / COMMIT')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, before BEGIN")
db.execute('BEGIN')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after BEGIN")
cr = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1,1000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement1 close")
db.execute('COMMIT')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after COMMIT")

print()
print('Simple transaction persists beyond commit')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, before BEGIN")
db.execute('BEGIN')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after BEGIN")
cr = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1,1000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
db.execute('COMMIT')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after COMMIT")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement close")

print()
print('Implicit transaction overlapping statements')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}")
cr = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1,1000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr2 = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1001,2000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement1 close")
print(f"step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr3 = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(2001, 3000)")
print(f"step={next(cr2)}, step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
cr2.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement2 close")
print(f"step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr3.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement3 close")

print()
print('Transaction persist beyond commit with overlapping statements')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, before BEGIN")
db.execute('BEGIN')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after BEGIN")
cr = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1,1000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
db.execute('COMMIT')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after COMMIT")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr2 = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1001,2000)")
print(f"step={next(cr)}, step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement1 close")
time.sleep(1)
print(f"step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr2.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement2 close")

Simple Statement
inTransaction=False, autocommit=True
step=Row(value=1, txn='2023-02-09 19:42:55.982 Z'), inTransaction=True, autocommit=True
step=Row(value=2, txn='2023-02-09 19:42:55.982 Z'), inTransaction=True, autocommit=True
step=Row(value=3, txn='2023-02-09 19:42:55.982 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement1 close

Simple BEGIN / COMMIT
inTransaction=False, autocommit=True, before BEGIN
inTransaction=True, autocommit=False, after BEGIN
step=Row(value=1, txn='2023-02-09 19:42:58.985 Z'), inTransaction=True, autocommit=False
step=Row(value=2, txn='2023-02-09 19:42:58.985 Z'), inTransaction=True, autocommit=False
step=Row(value=3, txn='2023-02-09 19:42:58.985 Z'), inTransaction=True, autocommit=False
inTransaction=True, autocommit=False, after statement1 close
inTransaction=False, autocommit=True, after COMMIT

Simple transaction persists beyond commit
inTransaction=False, autocommit=True, before BEGIN
inTransaction=True, autocommit=False, after BEGIN
step=Row(value=1, txn='2023-02-09 19:43:01.992 Z'), inTransaction=True, autocommit=False
step=Row(value=2, txn='2023-02-09 19:43:01.992 Z'), inTransaction=True, autocommit=False
step=Row(value=3, txn='2023-02-09 19:43:01.992 Z'), inTransaction=True, autocommit=False
inTransaction=True, autocommit=True, after COMMIT
step=Row(value=4, txn='2023-02-09 19:43:04.994 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 19:43:04.994 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement close

Implicit transaction overlapping statements
inTransaction=False, autocommit=True
step=Row(value=1, txn='2023-02-09 19:43:06.998 Z'), inTransaction=True, autocommit=True
step=Row(value=2, txn='2023-02-09 19:43:06.998 Z'), inTransaction=True, autocommit=True
step=Row(value=3, txn='2023-02-09 19:43:06.998 Z'), inTransaction=True, autocommit=True
step=Row(value=4, txn='2023-02-09 19:43:06.998 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 19:43:06.998 Z'), step=Row(value=1001, txn='2023-02-09 19:43:06.998 Z'), inTransaction=True, autocommit=True
step=Row(value=6, txn='2023-02-09 19:43:06.998 Z'), step=Row(value=1002, txn='2023-02-09 19:43:06.998 Z'), inTransaction=True, autocommit=True
step=Row(value=7, txn='2023-02-09 19:43:06.998 Z'), step=Row(value=1003, txn='2023-02-09 19:43:06.998 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement1 close
step=Row(value=1004, txn='2023-02-09 19:43:14.006 Z'), inTransaction=True, autocommit=True
step=Row(value=1005, txn='2023-02-09 19:43:14.006 Z'), inTransaction=True, autocommit=True
step=Row(value=1006, txn='2023-02-09 19:43:14.006 Z'), inTransaction=True, autocommit=True
step=Row(value=1007, txn='2023-02-09 19:43:14.006 Z'), step=Row(value=2001, txn='2023-02-09 19:43:14.006 Z'), inTransaction=True, autocommit=True
step=Row(value=1008, txn='2023-02-09 19:43:14.006 Z'), step=Row(value=2002, txn='2023-02-09 19:43:14.006 Z'), inTransaction=True, autocommit=True
step=Row(value=1009, txn='2023-02-09 19:43:14.006 Z'), step=Row(value=2003, txn='2023-02-09 19:43:14.006 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement2 close
step=Row(value=2004, txn='2023-02-09 19:43:19.013 Z'), inTransaction=True, autocommit=True
step=Row(value=2005, txn='2023-02-09 19:43:19.013 Z'), inTransaction=True, autocommit=True
step=Row(value=2006, txn='2023-02-09 19:43:19.013 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement3 close

Transaction persist beyond commit with overlapping statements
inTransaction=False, autocommit=True, before BEGIN
inTransaction=True, autocommit=False, after BEGIN
step=Row(value=1, txn='2023-02-09 19:43:22.018 Z'), inTransaction=True, autocommit=False
step=Row(value=2, txn='2023-02-09 19:43:22.018 Z'), inTransaction=True, autocommit=False
step=Row(value=3, txn='2023-02-09 19:43:22.018 Z'), inTransaction=True, autocommit=False
inTransaction=True, autocommit=True, after COMMIT
step=Row(value=4, txn='2023-02-09 19:43:26.021 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 19:43:26.021 Z'), step=Row(value=1001, txn='2023-02-09 19:43:26.021 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement1 close
step=Row(value=1002, txn='2023-02-09 19:43:29.024 Z'), inTransaction=True, autocommit=True
step=Row(value=1003, txn='2023-02-09 19:43:29.024 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement2 close

db.instransaction is defined as `((not db.autocommit) or (db.txn_state() > 0))

Applying the following patch, which implements reset the txntime when a statement ends if autocommit is on and this is the last outstanding statement on the connection and no database in the connection is in a transaction:

Index: src/vdbeaux.c
==================================================================
--- src/vdbeaux.c
+++ src/vdbeaux.c
@@ -3394,11 +3394,19 @@
   ** by connection db have now been released. Call sqlite3ConnectionUnlocked()
   ** to invoke any required unlock-notify callbacks.
   */
   if( db->autoCommit ){
     sqlite3ConnectionUnlocked(db);
-    db->txnTime = 0;
+    if( !db->nVdbeActive ){
+      int transaction = 0;
+      for( int ii=0; ii<db->nDb; ii++ ){
+        Btree* pBt = db->aDb[ii].pBt;
+        transaction += (pBt ? pBt->inTrans : 0);
+      }
+      if( !transaction )
+        db->txnTime = 0;
+    }
   }

   assert( db->nVdbeActive>0 || db->autoCommit==0 || db->nStatement==0 );
   return (p->rc==SQLITE_BUSY ? SQLITE_BUSY : SQLITE_OK);
 }

Gives the following results where the same code is executed.

Simple Statement
inTransaction=False, autocommit=True
step=Row(value=1, txn='2023-02-09 20:22:14.573 Z'), inTransaction=True, autocommit=True
step=Row(value=2, txn='2023-02-09 20:22:14.573 Z'), inTransaction=True, autocommit=True
step=Row(value=3, txn='2023-02-09 20:22:14.573 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement1 close

Simple BEGIN / COMMIT
inTransaction=False, autocommit=True, before BEGIN
inTransaction=True, autocommit=False, after BEGIN
step=Row(value=1, txn='2023-02-09 20:22:17.579 Z'), inTransaction=True, autocommit=False
step=Row(value=2, txn='2023-02-09 20:22:17.579 Z'), inTransaction=True, autocommit=False
step=Row(value=3, txn='2023-02-09 20:22:17.579 Z'), inTransaction=True, autocommit=False
inTransaction=True, autocommit=False, after statement1 close
inTransaction=False, autocommit=True, after COMMIT

Simple transaction persists beyond commit
inTransaction=False, autocommit=True, before BEGIN
inTransaction=True, autocommit=False, after BEGIN
step=Row(value=1, txn='2023-02-09 20:22:20.585 Z'), inTransaction=True, autocommit=False
step=Row(value=2, txn='2023-02-09 20:22:20.585 Z'), inTransaction=True, autocommit=False
step=Row(value=3, txn='2023-02-09 20:22:20.585 Z'), inTransaction=True, autocommit=False
inTransaction=True, autocommit=True, after COMMIT
step=Row(value=4, txn='2023-02-09 20:22:20.585 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 20:22:20.585 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement close

Implicit transaction overlapping statements
inTransaction=False, autocommit=True
step=Row(value=1, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
step=Row(value=2, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
step=Row(value=3, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
step=Row(value=4, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 20:22:25.591 Z'), step=Row(value=1001, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
step=Row(value=6, txn='2023-02-09 20:22:25.591 Z'), step=Row(value=1002, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
step=Row(value=7, txn='2023-02-09 20:22:25.591 Z'), step=Row(value=1003, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement1 close
step=Row(value=1004, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
step=Row(value=1005, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
step=Row(value=1006, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
step=Row(value=1007, txn='2023-02-09 20:22:25.591 Z'), step=Row(value=2001, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
step=Row(value=1008, txn='2023-02-09 20:22:25.591 Z'), step=Row(value=2002, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
step=Row(value=1009, txn='2023-02-09 20:22:25.591 Z'), step=Row(value=2003, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement2 close
step=Row(value=2004, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
step=Row(value=2005, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
step=Row(value=2006, txn='2023-02-09 20:22:25.591 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement3 close

Transaction persist beyond commit with overlapping statements
inTransaction=False, autocommit=True, before BEGIN
inTransaction=True, autocommit=False, after BEGIN
step=Row(value=1, txn='2023-02-09 20:22:40.610 Z'), inTransaction=True, autocommit=False
step=Row(value=2, txn='2023-02-09 20:22:40.610 Z'), inTransaction=True, autocommit=False
step=Row(value=3, txn='2023-02-09 20:22:40.610 Z'), inTransaction=True, autocommit=False
inTransaction=True, autocommit=True, after COMMIT
step=Row(value=4, txn='2023-02-09 20:22:40.610 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 20:22:40.610 Z'), step=Row(value=1001, txn='2023-02-09 20:22:40.610 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement1 close
step=Row(value=1002, txn='2023-02-09 20:22:40.610 Z'), inTransaction=True, autocommit=True
step=Row(value=1003, txn='2023-02-09 20:22:40.610 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement2 close

The transaction time is then really the transaction time, not merely the autocommit time. The drawback is that checking that there is in fact no transaction in progress is (obviously) more time consuming that not bothering to check.

There can be no next transaction if there is still a statement executing. Well, actually, there can be. But this would be a statement that does not "persist" a transaction state.

To fix that one would have to remove the condition of being the last statement, and reset txntime at the end of a statement if autocommit is on and no schema is in a transaction.

The test for that would be something like:

import mpsw
import time

db = mpsw.Connection()

print('Simple Statement')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}")
cr = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1,1000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement1 close")

print()
print('Simple BEGIN / COMMIT')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, before BEGIN")
db.execute('BEGIN')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after BEGIN")
cr = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1,1000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement1 close")
db.execute('COMMIT')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after COMMIT")

print()
print('Simple transaction persists beyond commit')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, before BEGIN")
db.execute('BEGIN')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after BEGIN")
cr = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1,1000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
db.execute('COMMIT')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after COMMIT")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement close")

print()
print('Implicit transaction overlapping statements')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}")
cr = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1,1000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr2 = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1001,2000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement1 close")
print(f"step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr3 = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(2001, 3000)")
print(f"step={next(cr2)}, step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
cr2.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement2 close")
print(f"step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr3.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement3 close")

print()
print('Transaction persist beyond commit with overlapping statements')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, before BEGIN")
db.execute('BEGIN')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after BEGIN")
cr = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1,1000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
db.execute('COMMIT')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after COMMIT")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr2 = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1001,2000)")
print(f"step={next(cr)}, step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement1 close")
time.sleep(1)
print(f"step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr2.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement2 close")

print()
print('Implicit transaction overlapping statements -- test with non transactional statement')
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}")
cr = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1,1000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr2 = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(1001,2000)")
print(f"step={next(cr)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr)}, step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement1 close")
print(f"step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print("statement 3 should not hold the transaction open (values 2001...2006)")
cr3 = db.execute("values (2001), (2002), (2003), (2004), (2005), (2006), (2007), (2008), (2009), (2010), (2011)")
print(f"step={next(cr2)}, step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr2)}, step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
cr2.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement2 close")
print(f"step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr3)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr4 = db.execute("select value, datetime('txn',(value-value)||' days') as txn from generate_series(3001,4000)")
print(f"step={next(cr3)}, step={next(cr4)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr3)}, step={next(cr4)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr3)}, step={next(cr4)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr3.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement3 close")
print(f"step={next(cr4)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
print(f"step={next(cr4)}, inTransaction={db.intransaction}, autocommit={db.autocommit}")
time.sleep(1)
cr4.close()
print(f"inTransaction={db.intransaction}, autocommit={db.autocommit}, after statement4 close")

Simple Statement
inTransaction=False, autocommit=True
step=Row(value=1, txn='2023-02-09 21:08:54.637 Z'), inTransaction=True, autocommit=True
step=Row(value=2, txn='2023-02-09 21:08:54.637 Z'), inTransaction=True, autocommit=True
step=Row(value=3, txn='2023-02-09 21:08:54.637 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement1 close

Simple BEGIN / COMMIT
inTransaction=False, autocommit=True, before BEGIN
inTransaction=True, autocommit=False, after BEGIN
step=Row(value=1, txn='2023-02-09 21:08:57.642 Z'), inTransaction=True, autocommit=False
step=Row(value=2, txn='2023-02-09 21:08:57.642 Z'), inTransaction=True, autocommit=False
step=Row(value=3, txn='2023-02-09 21:08:57.642 Z'), inTransaction=True, autocommit=False
inTransaction=True, autocommit=False, after statement1 close
inTransaction=False, autocommit=True, after COMMIT

Simple transaction persists beyond commit
inTransaction=False, autocommit=True, before BEGIN
inTransaction=True, autocommit=False, after BEGIN
step=Row(value=1, txn='2023-02-09 21:09:00.649 Z'), inTransaction=True, autocommit=False
step=Row(value=2, txn='2023-02-09 21:09:00.649 Z'), inTransaction=True, autocommit=False
step=Row(value=3, txn='2023-02-09 21:09:00.649 Z'), inTransaction=True, autocommit=False
inTransaction=True, autocommit=True, after COMMIT
step=Row(value=4, txn='2023-02-09 21:09:00.649 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 21:09:00.649 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement close

Implicit transaction overlapping statements
inTransaction=False, autocommit=True
step=Row(value=1, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
step=Row(value=2, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
step=Row(value=3, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
step=Row(value=4, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 21:09:05.656 Z'), step=Row(value=1001, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
step=Row(value=6, txn='2023-02-09 21:09:05.656 Z'), step=Row(value=1002, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
step=Row(value=7, txn='2023-02-09 21:09:05.656 Z'), step=Row(value=1003, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement1 close
step=Row(value=1004, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
step=Row(value=1005, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
step=Row(value=1006, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
step=Row(value=1007, txn='2023-02-09 21:09:05.656 Z'), step=Row(value=2001, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
step=Row(value=1008, txn='2023-02-09 21:09:05.656 Z'), step=Row(value=2002, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
step=Row(value=1009, txn='2023-02-09 21:09:05.656 Z'), step=Row(value=2003, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement2 close
step=Row(value=2004, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
step=Row(value=2005, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
step=Row(value=2006, txn='2023-02-09 21:09:05.656 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement3 close

Transaction persist beyond commit with overlapping statements
inTransaction=False, autocommit=True, before BEGIN
inTransaction=True, autocommit=False, after BEGIN
step=Row(value=1, txn='2023-02-09 21:09:20.674 Z'), inTransaction=True, autocommit=False
step=Row(value=2, txn='2023-02-09 21:09:20.674 Z'), inTransaction=True, autocommit=False
step=Row(value=3, txn='2023-02-09 21:09:20.674 Z'), inTransaction=True, autocommit=False
inTransaction=True, autocommit=True, after COMMIT
step=Row(value=4, txn='2023-02-09 21:09:20.674 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 21:09:20.674 Z'), step=Row(value=1001, txn='2023-02-09 21:09:20.674 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement1 close
step=Row(value=1002, txn='2023-02-09 21:09:20.674 Z'), inTransaction=True, autocommit=True
step=Row(value=1003, txn='2023-02-09 21:09:20.674 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement2 close

Implicit transaction overlapping statements -- test with non transactional statement
inTransaction=False, autocommit=True
step=Row(value=1, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
step=Row(value=2, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
step=Row(value=3, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
step=Row(value=4, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 21:09:29.686 Z'), step=Row(value=1001, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
step=Row(value=6, txn='2023-02-09 21:09:29.686 Z'), step=Row(value=1002, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
step=Row(value=7, txn='2023-02-09 21:09:29.686 Z'), step=Row(value=1003, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement1 close
step=Row(value=1004, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
step=Row(value=1005, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
step=Row(value=1006, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
statement 3 should not hold the transaction open (values 2001...2006)
step=Row(value=1007, txn='2023-02-09 21:09:29.686 Z'), step=Row(column1=2001), inTransaction=True, autocommit=True
step=Row(value=1008, txn='2023-02-09 21:09:29.686 Z'), step=Row(column1=2002), inTransaction=True, autocommit=True
step=Row(value=1009, txn='2023-02-09 21:09:29.686 Z'), step=Row(column1=2003), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement2 close
step=Row(column1=2004), inTransaction=False, autocommit=True
step=Row(column1=2005), inTransaction=False, autocommit=True
step=Row(column1=2006), inTransaction=False, autocommit=True
step=Row(column1=2007), step=Row(value=3001, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
step=Row(column1=2008), step=Row(value=3002, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
step=Row(column1=2009), step=Row(value=3003, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement3 close
step=Row(value=3004, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
step=Row(value=3005, txn='2023-02-09 21:09:29.686 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement4 close

Note that the statement which should not be holding the transaction open is. The solution to this is the following patch:

Index: src/vdbeaux.c
==================================================================
--- src/vdbeaux.c
+++ src/vdbeaux.c
@@ -3394,11 +3394,11 @@
   ** by connection db have now been released. Call sqlite3ConnectionUnlocked()
   ** to invoke any required unlock-notify callbacks.
   */
   if( db->autoCommit ){
     sqlite3ConnectionUnlocked(db);
-    if( !db->nVdbeActive ){
+    {
       int transaction = 0;
       for( int ii=0; ii<db->nDb; ii++ ){
         Btree* pBt = db->aDb[ii].pBt;
         transaction += (pBt ? pBt->inTrans : 0);
       }

that is, the autocommit block becomes

  if( db->autoCommit ){
    sqlite3ConnectionUnlocked(db);
    {
      int transaction = 0;
      for( int ii=0; ii<db->nDb; ii++ ){
        Btree* pBt = db->aDb[ii].pBt;
        transaction += (pBt ? pBt->inTrans : 0);
      }
      if( !transaction )
        db->txnTime = 0;
    }
  }

With this change the output becomes the following, statements that do no affect the transaction status will not hold a transaction or stop the resetting of txntime.

Simple Statement
inTransaction=False, autocommit=True
step=Row(value=1, txn='2023-02-09 21:30:44.358 Z'), inTransaction=True, autocommit=True
step=Row(value=2, txn='2023-02-09 21:30:44.358 Z'), inTransaction=True, autocommit=True
step=Row(value=3, txn='2023-02-09 21:30:44.358 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement1 close

Simple BEGIN / COMMIT
inTransaction=False, autocommit=True, before BEGIN
inTransaction=True, autocommit=False, after BEGIN
step=Row(value=1, txn='2023-02-09 21:30:47.363 Z'), inTransaction=True, autocommit=False
step=Row(value=2, txn='2023-02-09 21:30:47.363 Z'), inTransaction=True, autocommit=False
step=Row(value=3, txn='2023-02-09 21:30:47.363 Z'), inTransaction=True, autocommit=False
inTransaction=True, autocommit=False, after statement1 close
inTransaction=False, autocommit=True, after COMMIT

Simple transaction persists beyond commit
inTransaction=False, autocommit=True, before BEGIN
inTransaction=True, autocommit=False, after BEGIN
step=Row(value=1, txn='2023-02-09 21:30:50.370 Z'), inTransaction=True, autocommit=False
step=Row(value=2, txn='2023-02-09 21:30:50.370 Z'), inTransaction=True, autocommit=False
step=Row(value=3, txn='2023-02-09 21:30:50.370 Z'), inTransaction=True, autocommit=False
inTransaction=True, autocommit=True, after COMMIT
step=Row(value=4, txn='2023-02-09 21:30:50.370 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 21:30:50.370 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement close

Implicit transaction overlapping statements
inTransaction=False, autocommit=True
step=Row(value=1, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
step=Row(value=2, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
step=Row(value=3, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
step=Row(value=4, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 21:30:55.377 Z'), step=Row(value=1001, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
step=Row(value=6, txn='2023-02-09 21:30:55.377 Z'), step=Row(value=1002, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
step=Row(value=7, txn='2023-02-09 21:30:55.377 Z'), step=Row(value=1003, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement1 close
step=Row(value=1004, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
step=Row(value=1005, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
step=Row(value=1006, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
step=Row(value=1007, txn='2023-02-09 21:30:55.377 Z'), step=Row(value=2001, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
step=Row(value=1008, txn='2023-02-09 21:30:55.377 Z'), step=Row(value=2002, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
step=Row(value=1009, txn='2023-02-09 21:30:55.377 Z'), step=Row(value=2003, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement2 close
step=Row(value=2004, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
step=Row(value=2005, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
step=Row(value=2006, txn='2023-02-09 21:30:55.377 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement3 close

Transaction persist beyond commit with overlapping statements
inTransaction=False, autocommit=True, before BEGIN
inTransaction=True, autocommit=False, after BEGIN
step=Row(value=1, txn='2023-02-09 21:31:10.396 Z'), inTransaction=True, autocommit=False
step=Row(value=2, txn='2023-02-09 21:31:10.396 Z'), inTransaction=True, autocommit=False
step=Row(value=3, txn='2023-02-09 21:31:10.396 Z'), inTransaction=True, autocommit=False
inTransaction=True, autocommit=True, after COMMIT
step=Row(value=4, txn='2023-02-09 21:31:10.396 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 21:31:10.396 Z'), step=Row(value=1001, txn='2023-02-09 21:31:10.396 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement1 close
step=Row(value=1002, txn='2023-02-09 21:31:10.396 Z'), inTransaction=True, autocommit=True
step=Row(value=1003, txn='2023-02-09 21:31:10.396 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement2 close

Implicit transaction overlapping statements -- test with non transactional statement
inTransaction=False, autocommit=True
step=Row(value=1, txn='2023-02-09 21:31:19.405 Z'), inTransaction=True, autocommit=True
step=Row(value=2, txn='2023-02-09 21:31:19.405 Z'), inTransaction=True, autocommit=True
step=Row(value=3, txn='2023-02-09 21:31:19.405 Z'), inTransaction=True, autocommit=True
step=Row(value=4, txn='2023-02-09 21:31:19.405 Z'), inTransaction=True, autocommit=True
step=Row(value=5, txn='2023-02-09 21:31:19.405 Z'), step=Row(value=1001, txn='2023-02-09 21:31:19.405 Z'), inTransaction=True, autocommit=True
step=Row(value=6, txn='2023-02-09 21:31:19.405 Z'), step=Row(value=1002, txn='2023-02-09 21:31:19.405 Z'), inTransaction=True, autocommit=True
step=Row(value=7, txn='2023-02-09 21:31:19.405 Z'), step=Row(value=1003, txn='2023-02-09 21:31:19.405 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement1 close
step=Row(value=1004, txn='2023-02-09 21:31:19.405 Z'), inTransaction=True, autocommit=True
step=Row(value=1005, txn='2023-02-09 21:31:19.405 Z'), inTransaction=True, autocommit=True
step=Row(value=1006, txn='2023-02-09 21:31:19.405 Z'), inTransaction=True, autocommit=True
statement 3 should not hold the transaction open (values 2001...2006)
step=Row(value=1007, txn='2023-02-09 21:31:19.405 Z'), step=Row(column1=2001), inTransaction=True, autocommit=True
step=Row(value=1008, txn='2023-02-09 21:31:19.405 Z'), step=Row(column1=2002), inTransaction=True, autocommit=True
step=Row(value=1009, txn='2023-02-09 21:31:19.405 Z'), step=Row(column1=2003), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement2 close
step=Row(column1=2004), inTransaction=False, autocommit=True
step=Row(column1=2005), inTransaction=False, autocommit=True
step=Row(column1=2006), inTransaction=False, autocommit=True
step=Row(column1=2007), step=Row(value=3001, txn='2023-02-09 21:31:34.423 Z'), inTransaction=True, autocommit=True
step=Row(column1=2008), step=Row(value=3002, txn='2023-02-09 21:31:34.423 Z'), inTransaction=True, autocommit=True
step=Row(column1=2009), step=Row(value=3003, txn='2023-02-09 21:31:34.423 Z'), inTransaction=True, autocommit=True
inTransaction=True, autocommit=True, after statement3 close
step=Row(value=3004, txn='2023-02-09 21:31:34.423 Z'), inTransaction=True, autocommit=True
step=Row(value=3005, txn='2023-02-09 21:31:34.423 Z'), inTransaction=True, autocommit=True
inTransaction=False, autocommit=True, after statement4 close

That is, remove the requirement for the reset to apply only if no other VMs are active. Instead, reset the txntime if autocommit is true and no schema is in a transaction state. This will be more accurate as to actually resetting txntime only when there are no databases in a transaction, but also more expensive for each statement closure.

This would probably make it worthwhile to only check for transactions and reset txntime if it is non-zero (meaning that you actually used it in the transaction).

  if( db->autoCommit ){
    sqlite3ConnectionUnlocked(db);
    if( db->txnTime ){
      int transaction = 0;
      for( int ii=0; ii<db->nDb; ii++ ){
        Btree* pBt = db->aDb[ii].pBt;
        transaction += (pBt ? pBt->inTrans : 0);
      }
      if( !transaction )
        db->txnTime = 0;
    }
  }

The only change from the patch set used by Richard is the detection of when to reset the txnTime in vdbeaux.c. That means that the patches (exclusive of the test changes) as provided by fossil are:

Index: src/date.c
==================================================================
--- src/date.c
+++ src/date.c
@@ -329,16 +329,20 @@
   }
   return 0;
 }

 /*
-** Set the time to the current time reported by the VFS.
+** Set the time to the current time reported for the prepared statement
+** that is currently executing.  The same time is reported for all
+** invocations of this routine from within the same call to sqlite3_step().
+**
+** Or if bTxn is true, use the transaction time.
 **
 ** Return the number of errors.
 */
-static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
-  p->iJD = sqlite3StmtCurrentTime(context);
+static int setCurrentStmtTime(sqlite3_context *context, DateTime *p, int bTxn){
+  p->iJD = sqlite3StmtCurrentTime(context, bTxn);
   if( p->iJD>0 ){
     p->validJD = 1;
     return 0;
   }else{
     return 1;
@@ -385,11 +389,13 @@
   if( parseYyyyMmDd(zDate,p)==0 ){
     return 0;
   }else if( parseHhMmSs(zDate, p)==0 ){
     return 0;
   }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){
-    return setDateTimeToCurrent(context, p);
+    return setCurrentStmtTime(context, p, 0);
+  }else if( sqlite3StrICmp(zDate,"txn")==0 && sqlite3NotPureFunc(context) ){
+    return setCurrentStmtTime(context, p, 1);
   }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8)>0 ){
     setRawDateNumber(p, r);
     return 0;
   }
   return 1;
@@ -929,12 +935,15 @@
 ** Process time function arguments.  argv[0] is a date-time stamp.
 ** argv[1] and following are modifiers.  Parse them all and write
 ** the resulting time into the DateTime structure p.  Return 0
 ** on success and 1 if there are any errors.
 **
-** If there are zero parameters (if even argv[0] is undefined)
-** then assume a default value of "now" for argv[0].
+** If there are zero parameters (if argc<=0) then assume a default
+** value of "now" for argv[0] if argc==0 and "txn" if argc<0.  SQL
+** functions will always have argc>=0, but the special implementations
+** of CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP set argc to -1
+** in order to force the use of 'txn' semantics.
 */
 static int isDate(
   sqlite3_context *context,
   int argc,
   sqlite3_value **argv,
@@ -942,13 +951,13 @@
 ){
   int i, n;
   const unsigned char *z;
   int eType;
   memset(p, 0, sizeof(*p));
-  if( argc==0 ){
+  if( argc<=0 ){
     if( !sqlite3NotPureFunc(context) ) return 1;
-    return setDateTimeToCurrent(context, p);
+    return setCurrentStmtTime(context, p, argc<0);
   }
   if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
                    || eType==SQLITE_INTEGER ){
     setRawDateNumber(p, sqlite3_value_double(argv[0]));
   }else{
@@ -1251,11 +1260,11 @@
   sqlite3_context *context,
   int NotUsed,
   sqlite3_value **NotUsed2
 ){
   UNUSED_PARAMETER2(NotUsed, NotUsed2);
-  timeFunc(context, 0, 0);
+  timeFunc(context, -1, 0);
 }

 /*
 ** current_date()
 **
@@ -1265,11 +1274,11 @@
   sqlite3_context *context,
   int NotUsed,
   sqlite3_value **NotUsed2
 ){
   UNUSED_PARAMETER2(NotUsed, NotUsed2);
-  dateFunc(context, 0, 0);
+  dateFunc(context, -1, 0);
 }

 /*
 ** current_timestamp()
 **
@@ -1279,11 +1288,11 @@
   sqlite3_context *context,
   int NotUsed,
   sqlite3_value **NotUsed2
 ){
   UNUSED_PARAMETER2(NotUsed, NotUsed2);
-  datetimeFunc(context, 0, 0);
+  datetimeFunc(context, -1, 0);
 }
 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */

 #ifdef SQLITE_OMIT_DATETIME_FUNCS
 /*
@@ -1310,11 +1319,11 @@
   char zBuf[20];

   UNUSED_PARAMETER(argc);
   UNUSED_PARAMETER(argv);

-  iT = sqlite3StmtCurrentTime(context);
+  iT = sqlite3StmtCurrentTime(context, 1);
   if( iT<=0 ) return;
   t = iT/1000 - 10000*(sqlite3_int64)21086676;
 #if HAVE_GMTIME_R
   pTm = gmtime_r(&t, &sNow);
 #else

Index: src/sqliteInt.h
==================================================================
--- src/sqliteInt.h
+++ src/sqliteInt.h
@@ -1619,10 +1619,11 @@
   u8 mTrace;                    /* zero or more SQLITE_TRACE flags */
   u8 noSharedCache;             /* True if no shared-cache backends */
   u8 nSqlExec;                  /* Number of pending OP_SqlExec opcodes */
   u8 eOpenState;                /* Current condition of the connection */
   int nextPagesize;             /* Pagesize after VACUUM if >0 */
+  i64 txnTime;                  /* Timestamp for current transaction */
   i64 nChange;                  /* Value returned by sqlite3_changes() */
   i64 nTotalChange;             /* Value returned by sqlite3_total_changes() */
   int aLimit[SQLITE_N_LIMIT];   /* Limits */
   int nMaxSorterMmap;           /* Maximum size of regions mapped by sorter */
   struct sqlite3InitInfo {      /* Information used during initialization */
@@ -5338,11 +5339,11 @@
 FuncDef *sqlite3VtabOverloadFunction(sqlite3 *,FuncDef*, int nArg, Expr*);
 #if (defined(SQLITE_ENABLE_DBPAGE_VTAB) || defined(SQLITE_TEST)) \
     && !defined(SQLITE_OMIT_VIRTUALTABLE)
   void sqlite3VtabUsesAllSchemas(sqlite3_index_info*);
 #endif
-sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context*);
+sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context*, int);
 int sqlite3VdbeParameterIndex(Vdbe*, const char*, int);
 int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *);
 void sqlite3ParseObjectInit(Parse*,sqlite3*);
 void sqlite3ParseObjectReset(Parse*);
 void *sqlite3ParserAddCleanup(Parse*,void(*)(sqlite3*,void*),void*);

Index: src/vdbeapi.c
==================================================================
--- src/vdbeapi.c
+++ src/vdbeapi.c
@@ -968,22 +968,31 @@
 ** Return the current time for a statement.  If the current time
 ** is requested more than once within the same run of a single prepared
 ** statement, the exact same time is returned for each invocation regardless
 ** of the amount of time that elapses between invocations.  In other words,
 ** the time returned is always the time of the first call.
+**
+** Or, if bTxn, return the transaction time.  The transaction time is the
+** same for all calls within the same transaction.
+**
+** bTxn is 0 for SQL like datetime('now') and is 1 for datetime('txn').
 */
-sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context *p){
+sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context *p, int bTxn){
   int rc;
 #ifndef SQLITE_ENABLE_STAT4
-  sqlite3_int64 *piTime = &p->pVdbe->iCurrentTime;
+  sqlite3_int64 *piTime;
+  sqlite3 *db = p->pOut->db;
   assert( p->pVdbe!=0 );
+  piTime = bTxn ? &db->txnTime : &p->pVdbe->iCurrentTime;
 #else
   sqlite3_int64 iTime = 0;
-  sqlite3_int64 *piTime = p->pVdbe!=0 ? &p->pVdbe->iCurrentTime : &iTime;
+  sqlite3_int64 *piTime;
+  sqlite3 *db = p->pOut->db;
+  piTime = bTxn ? &db->txnTime : p->pVdbe!=0 ? &p->pVdbe->iCurrentTime : &iTime;
 #endif
   if( *piTime==0 ){
-    rc = sqlite3OsCurrentTimeInt64(p->pOut->db->pVfs, piTime);
+    rc = sqlite3OsCurrentTimeInt64(db->pVfs, piTime);
     if( rc ) *piTime = 0;
   }
   return *piTime;
 }

Index: src/vdbeaux.c
==================================================================
--- src/vdbeaux.c
+++ src/vdbeaux.c
@@ -3394,10 +3394,19 @@
   ** by connection db have now been released. Call sqlite3ConnectionUnlocked()
   ** to invoke any required unlock-notify callbacks.
   */
   if( db->autoCommit ){
     sqlite3ConnectionUnlocked(db);
+    if( db->txnTime ){
+      int transaction = 0;
+      for( int ii=0; ii<db->nDb; ii++ ){
+        Btree* pBt = db->aDb[ii].pBt;
+        transaction += (pBt ? pBt->inTrans : 0);
+      }
+      if( !transaction )
+        db->txnTime = 0;
+    }
   }

   assert( db->nVdbeActive>0 || db->autoCommit==0 || db->nStatement==0 );
   return (p->rc==SQLITE_BUSY ? SQLITE_BUSY : SQLITE_OK);
 }

Note that benchmarking is left as an exercize for the reader. Unless the txnTime is used then the additional overhead is the few nanoseconds requires to process the JZ instruction to bypass the entire resetting procedure.

If you do use the txnTime, then you will pay a price when each vm is torn down and autocommit is on proportional to the number of database schema attached to the connection (usually two, but you may have attached more).

My benchmark testing indicates that the difference is negligible and that the timing differences due to running on a multitasking operating system exceed the overhead associated with the added txnTime transaction tracking -- meaning that the tps rate is pretty much the same (48K/sec) notwithstanding whether the transaction access 'now', 'txn' or nothing at all.

We are calling the transaction scope for 'txn' time from the first call for 'txn' time to last row retrieval (last step).

No. txn time is "set" when it is first accessed and is "unset"; and, "unset" when a statement is reset (explicitly or implicitly) provided that (a) autocommit is on (not in an explicit transacton) and (b) sqlite3_txn_state(db) for the connection is 0 (no schema attached to the connection is currently in a transaction).

B - A statement has a time value of similar scope (that is 'now' value is statement stable), from first call (if a deterministic expression) to last row retrieval (of that same expression), or retrieval time for something like datetime('now')||'' .

No. The time value for 'now' is an attribute of a statement. It is "unset" on each step (sqlite3_step) of the statement. It is set the first time it is accessed by a statement step. If its parameters are constant, then it may be subjected to constant optimization, making it "look like" it is statement stable, but it is not. It is step stable only. Appending something to datetime('now') does not make it non-constant. Having non-constant parameters makes it non-constant.

Your comment about CURRENT_TIMESTAMP / CURRENT_DATE / CURRENT_TIME is correct. They were step stable constants (that were optimized to look like they were statement stable). They will become transaction stable constants.