Bug Report: Setting Transaction Isolation is not supported with VTGate Parameter transaction_mode=SINGLE

ankitthakwani commented 1 year ago

Overview of the Issue

When --transaction_mode=SINGLE then following SQL pattern is not supported even though the SELECT is using Primary Vindex in WHERE call

Reproduction Steps

mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)

mysql> mysql> set @@session.transaction_isolation = 'repeatable-read'; Query OK, 0 rows affected (0.01 sec)

mysql> mysql> select txninfo0_.id as id178, txninfo0_.amount as amount278 ..... from txninfo as txninfo0 where txninfo0_.txn_id = 'PTMSSQXXCJ4D3WYZI4NP722ZIH5Q3ZWZ231'\G

ERROR 1317 (70100): multi-db transaction attempted: [target:{keyspace:"switch_sharded" shard:"-40" tablet_type:PRIMARY} transaction_id:1685087096507032302 tablet_alias:{cell:"dc1" uid:200} target:{keyspace:"switch_sharded" shard:"80-c0" tablet_type:PRIMARY} transaction_id:1685087096184469003 tablet_alias:{cell:"dc1" uid:400}] mysql> mysql>

mysql> SHOW VSCHEMA VINDEXES ON txn_info; +--------------------+-----------------------+--------------------------+-------------------------------------------------------------------------------------------------------+----------+ | Columns | Name | Type | Params | Owner | +--------------------+-----------------------+--------------------------+-------------------------------------------------------------------------------------------------------+----------+ | txn_id | unicode_loose_md5_vdx | unicode_loose_md5 | | | | id | txnInfo_id_vdx | lookup_unique | autocommit=true; from=id; table=txnInfo_id_vdx; to=keyspace_id | txn_info | +--------------------+-----------------------+--------------------------+-------------------------------------------------------------------------------------------------------+----------+ 2 rows in set (0.00 sec)

Binary Version

[vitess@workernode-upi-v1-10011441 ~]$ vttablet --version
Version: 16.0.1 (Git revision d1ba6258ea2462d5d28d67661aace7b79bb7e27b branch 'HEAD') built on Thu Mar 30 17:03:35 UTC 2023 by runner@fv-az280-176 using go1.20.2 linux/amd64

Operating System and Environment details

NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

Log Fragments

N.A.

ankitthakwani commented 1 year ago

The mentioned pattern is supported in native MySQL. I got this error in my application using Hibernate ORM which is very commonly used.

ankitthakwani commented 1 year ago

Reproduction Steps

In a 2 shard keyspace, deploy VSchema

{
"sharded": true,
"vindexes": {
"unicode_loose_md5_vdx": {
  "type": "unicode_loose_md5"
},
"hash_vdx": {
  "type": "hash"
},
"t1_id_vdx": {
  "type": "consistent_lookup_unique",
  "params": {
    "autocommit": "true",
    "from": "id",
    "table": "t1_id_vdx",
    "to": "keyspace_id"
  },
  "owner": "t1"
},
"t2_id_vdx": {
  "type": "consistent_lookup_unique",
  "params": {
    "autocommit": "true",
    "from": "id",
    "table": "t2_id_vdx",
    "to": "keyspace_id"
  },
  "owner": "t2"
}
},
"tables": {
"t1": {
  "columnVindexes": [
    {
      "column": "txn_id",
      "name": "unicode_loose_md5_vdx"
    },
    {
      "column": "id",
      "name": "t1_id_vdx"
    }
  ]
},
"t2": {
  "columnVindexes": [
    {
      "column": "txn_id",
      "name": "unicode_loose_md5_vdx"
    },
    {
      "column": "id",
      "name": "t2_id_vdx"
    }
  ]
},
"t1_id_vdx": {
  "columnVindexes": [
    {
      "column": "id",
      "name": "hash_vdx"
    }
  ]
},
"t2_id_vdx": {
  "columnVindexes": [
    {
      "column": "id",
      "name": "hash_vdx"
    }
  ]
}
}
}

Apply Schema
```
DROP TABLE IF EXISTS t1;
```

CREATE TABLE t1 ( id bigint(20) NOT NULL, txn_id varchar(50) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS t1_id_vdx;

CREATE TABLE t1_id_vdx ( id bigint(20) NOT NULL, keyspace_id varbinary(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS t2;

CREATE TABLE t2 ( id bigint(20) NOT NULL, txn_id varchar(50) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS t2_id_vdx;

CREATE TABLE t2_id_vdx ( id bigint(20) NOT NULL, keyspace_id varbinary(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


3. Execute following SQL

INSERT INTO t1(id, txn_id) VALUES (4, "t4");

INSERT INTO t1(id, txn_id) VALUES (5, "t5");

SELECT * FROM t1;

BEGIN;

set @@session.transaction_isolation = 'repeatable-read';

SELECT * FROM t1 WHERE txn_id = "t4";

ROLLBACK;

BEGIN;

set @@session.transaction_isolation = 'repeatable-read';

SELECT * FROM t1 WHERE txn_id = "t5";

ROLLBACK;


Execution Log from my system

mysql> INSERT INTO t1(id, txn_id) VALUES (4, "t4"); Query OK, 1 row affected (0.08 sec)

mysql> mysql> INSERT INTO t1(id, txn_id) VALUES (5, "t5");

Query OK, 1 row affected (0.08 sec)

mysql> mysql> SELECT * FROM t1; +----+--------+ | id | txn_id | +----+--------+ | 4 | t4 | | 5 | t5 | +----+--------+ 2 rows in set (0.00 sec)

mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)

mysql> mysql> set @@session.transaction_isolation = 'repeatable-read'; Query OK, 0 rows affected (0.01 sec)

mysql> mysql> SELECT * FROM t1 WHERE txn_id = "t4"; +----+--------+ | id | txn_id | +----+--------+ | 4 | t4 | +----+--------+ 1 row in set (0.00 sec)

mysql> mysql> ROLLBACK; Query OK, 0 rows affected (0.00 sec)

mysql> mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)

mysql> mysql> set @@session.transaction_isolation = 'repeatable-read'; Query OK, 0 rows affected (0.01 sec)

mysql> mysql> SELECT * FROM t1 WHERE txn_id = "t5"; ERROR 1317 (70100): multi-db transaction attempted: [target:{keyspace:"debug_sharded" shard:"80-" tablet_type:PRIMARY} transaction_id:1685629898601231230 tablet_alias:{cell:"dc1" uid:801} target:{keyspace:"debug_sharded" shard:"-80" tablet_type:PRIMARY} transaction_id:1685629742014607733 tablet_alias:{cell:"dc1" uid:701}] mysql> mysql> ROLLBACK; Query OK, 0 rows affected (0.00 sec)

harshit-gangal commented 1 year ago

I added a test for this but it did not fail on main

func TestSameShardQueries(t *testing.T) {
    conn, cleanup := setup(t)
    defer cleanup()

    utils.Exec(t, conn, `INSERT INTO t1(id, txn_id) VALUES (4, "t4");`)
    utils.Exec(t, conn, `INSERT INTO t1(id, txn_id) VALUES (5, "t5");`)
    utils.AssertMatches(t, conn, `SELECT * FROM t1;`, `[[INT64(4) VARCHAR("t4")] [INT64(5) VARCHAR("t5")]]`)

    utils.AssertMatches(t, conn, `select @@transaction_mode`, `[[VARCHAR("SINGLE")]]`)
    utils.Exec(t, conn, `begin`)
    utils.Exec(t, conn, `set @@session.transaction_isolation = 'repeatable-read';`)
    utils.Exec(t, conn, `SELECT * FROM t1 WHERE txn_id = "t4";`)
    utils.Exec(t, conn, `ROLLBACK;`)

    utils.Exec(t, conn, `begin`)
    utils.Exec(t, conn, `set @@session.transaction_isolation = 'repeatable-read';`)
    utils.Exec(t, conn, `SELECT * FROM t1 WHERE txn_id = "t5";`)
    utils.Exec(t, conn, `ROLLBACK;`)
}

go test ./go/test/endtoend/vtgate/transaction/single/ -count=100 -failfast -alsologtostderr  -run TestSameShardQueries

vitessio / vitess