Feature Request

Is your feature request related to a problem? Please describe:

Currently, in autocommit = 0 mode, stale read does not activiate a stale read transaction. Its behavior is the same as in autocommit = 1 mode, where each statement recalculates read_ts. To enable a stale read transaction, SET TRANSACTION READ ONLY AS OF TIMESTAMP must be used. For applications that wants to start stale read transactions via autocommit = 0, adaptation and modification of the application are needed, which increases the cost of use.

Reference issue: #62384

Describe the feature you'd like:

Extend the functionality of activating stale read transactions to the autocommit = 0 usage scenario.

The specific functionality is as follows: when autocommit = 0 and there is no ongoing transaction, if the session variable tidb_read_staleness is not set to 0, then:

1. When the first SELECT statement is executed, an attempt is made to initiate a stale read transaction. Subsequent in-transaction stale reads will use the same read_ts. Example:

SET tidb_read_staleness = '-5s';
SET autocommit = 0;

-- Loop the queries below:
SELECT COUNT(*) FROM orders WHERE status='PAID'; // Calculate the read_ts
SELECT * FROM users WHERE id=1;                                  // Re-use the read_ts
COMMIT;

2. The transaction’s snapshot TSO is determined according to tidb_read_staleness.
3. All subsequent reads in this transaction use the same historical snapshot.
4. The transaction is treated as read-only and can be committed or rolled back normally.

Compatibility Rules

1. If autocommit = 1, the behavior remains unchanged (single statement stale read).
2. If autocommit = 0 and tidb_read_staleness is 0, behavior remains unchanged (regular implicit transaction).
3. If both tidb_read_staleness and tidb_snapshot are set, tidb_snapshot takes precedence (same as current rule).

Limitations

1. Only applicable to implicit transactions under autocommit = 0.
2. Explicit transactions (BEGIN / START TRANSACTION) ignore tidb_read_staleness.
3. Write statements are not allowed in stale read transactions.

Tech Design

Stale read SELECT statement processing flow:

1. The processing of stale read SELECT statement information and timestamp calculation is first completed during the pre-process stage of the compilation phase at https://github.com/pingcap/tidb/blob/master/pkg/planner/core/preprocess.go#L1748. At this stage, the stale read timestamp and related information are determined.
2. Generate a StaleTxnContextProvider to handle stale read related processing, which is set in the txnManager at https://github.com/pingcap/tidb/blob/master/pkg/session/txnmanager.go#L167.

For stale read statement execution in a non-in-transaction state, the context provider is reinitialized at https://github.com/pingcap/tidb/blob/master/pkg/planner/core/preprocess.go#L1936 without activating a transaction. Therefore, the main modifications involve:

1. When autocommit = 0 and InTxn() == false, attempt to start a stale read transaction, making subsequent behavior similar to SET TRANSACTION READ ONLY AS OF TIMESTAMP.Example:

@@ -1935,13 +1935,20 @@ func (p *preprocessor) updateStateFromStaleReadProcessor() error {
                        p.sctx.GetSessionVars().StmtCtx.IsStaleness = true
                        if !p.sctx.GetSessionVars().InTxn() {
                                txnManager := sessiontxn.GetTxnManager(p.sctx)
+                               enterType := sessiontxn.EnterNewTxnWithReplaceProvider
+                               if !p.sctx.GetSessionVars().IsAutocommit() {
+                                       enterType = sessiontxn.EnterNewTxnWithBeginStmt
+                               }
                                newTxnRequest := &sessiontxn.EnterNewTxnRequest{
-                                       Type:     sessiontxn.EnterNewTxnWithReplaceProvider,
+                                       Type:     enterType,
                                        Provider: staleread.NewStalenessTxnContextProvider(p.sc
tx, p.LastSnapshotTS, p.InfoSchema),
                                }
                                if err := txnManager.EnterNewTxn(context.TODO(), newTxnRequest)
; err != nil {
                                        return err
                                }
+                               if !p.sctx.GetSessionVars().IsAutocommit() {
+                                       p.sctx.GetSessionVars().SetInTxn(true)
+                               }

2. After starting the stale read transaction, report errors for non-read-only operations such as DELETE, INSERT, UPDATE, and SELECT FOR UPDATE/SHARE.

For tests, it is needed to verifiy:

1. The stale read transaction initiated via autocommit = 0 will use the read_ts generated by the first statement.
2. If non-read-only operations (e.g., INSERT, UPDATE, DELETE) are executed within a stale read transaction, an error should be raised.
3. The current stale read transaction can be properly terminated via COMMIT or ROLLBACK. Other operations (e.g., DDL) will implicitly roll back the current transaction.
4. If tidb_read_staleness is non-zero but a transaction has already been activated (e.g., the first statement is an INSERT), tidb_read_staleness should have no effect on the ongoing transaction.

Describe alternatives you've considered:

SET TRANSACTION READ ONLY AS OF TIMESTAMP

Teachability, Documentation, Adoption, Migration Strategy: