Set pessimistic connection

[YouriMa]

Hi, Is it possible to set connection and querying to pessimistic instead of optimistic? I don't find where to set this option. regards, YM

[teckick]

Hi!

Weir use TiDB default transaction mode, and starting from v3.0.8, newly created TiDB clusters use the pessimistic transaction mode by default.

https://docs.pingcap.com/tidb/stable/pessimistic-transaction

However, Weir does not support modify session scope transaction mode by SET @@tidb_txn_mode='pessimistic' or SET @@tidb_txn_mode='optimistic'.

[YouriMa]

Hi,

Here is a log from our Weir proxy: Aug 17 13:25:33 proxy211-XXX.net weirproxy[9178]: [2022/08/17 13:25:33.211 +02:00] [WARN] [conn.go:184] ["command dispatched failed (sql error)"] [conn=5106] [connInfo="id:5106, addr:127.0.0.1:XXXX status:10, collation:utf8mb4_general_ci, user:XXXXX"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="SELECT XXXX FROM XXXX where XXX like '%XXX%' OR XX = 'XXX'"] [txn_mode=OPTIMISTIC] [err="ERROR 9004 (HY000): Resolve lock timeout"]

Here is the configuration of our tidb : SELECT @@tidb_txn_mode => pessimistic

We're using tidb 5.4.

Why is our Weir is using optimisitic when our cluster is set to pessimistic ?

[teckick]

What's the result of SELECT @@tidb_txn_mode from Weir?

[YouriMa]

Pessimistic ...

But again this morning : Aug 24 09:50:04 xx-185 weirproxy[3582]: [2022/08/24 09:50:04.334 +02:00] [ERROR] [conn.go:195] ["command dispatched failed (unknown error)"] [conn=16059] [connInfo="id:16059, addr:xx:xx status:10, collation:latin1_swedish_ci, user:XXX"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="SELECT XX ORDER BY RAND() LIMIT 2"] [txn_mode=OPTIMISTIC] [..]

[teckick]

This is a bug, txn_mode in log is retrieve from the following code:

func (cc *clientConn) Run(ctx context.Context) {
    ...
    var txnMode string
    if cc.ctx != nil {
        txnMode = cc.ctx.GetSessionVars().GetReadableTxnMode()
    }
    ...
}

However, this method use Optimistic as default.

func (s *SessionVars) GetReadableTxnMode() string {
    txnMode := s.TxnMode
    if txnMode == "" {
        txnMode = ast.Optimistic
    }
    return txnMode
}

Please DO NOT depend on txn mode in log. Execute SELECT @@tidb_txn_mode if you want to get this param.

[YouriMa]

Thank you for your reactivity and your reliability. I'll modify my queries to take in account the pessimistic mode.

Regards, YM