Error When Executing Multiple Statements in a Transaction

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 事务中一次执行多条语句时报错

| username: Kongdom

【TiDB Version】v6.5.8
【Impact of the Bug】Unable to use temporary tables within a transaction
【Possible Steps to Reproduce the Issue】
In a C# program, the pseudo-code is as follows:

1. Create a database connection and start a transaction
2. Define an SQL statement variable:
CREATE TEMPORARY TABLE tmp_customer_base(
    c_no varchar(20),
    PRIMARY KEY (c_no)
);
INSERT IGNORE INTO tmp_customer_base(c_no) values('11');
3. Execute the SQL statement variable in one call

The log information tracked after enabling the general log is as follows:

[2024/03/29 11:20:19.803 +08:00] [INFO] [session.go:3764] [GENERAL_LOG] [conn=2937701555370986845] [user=root@192.168.135.1] [schemaVersion=10423] [txnStartTS=0] [forUpdateTS=0] [isReadConsistency=false] [currentDB=] [isPessimistic=false] [sessionTxnMode=PESSIMISTIC] [sql="use `tidb_test`"]

[2024/03/29 11:20:19.803 +08:00] [INFO] [session.go:3764] [GENERAL_LOG] [conn=2937701555370986845] [user=root@192.168.135.1] [schemaVersion=10423] [txnStartTS=0] [forUpdateTS=0] [isReadConsistency=false] [currentDB=tidb_test] [isPessimistic=false] [sessionTxnMode=PESSIMISTIC] [sql="SET NAMES utf8mb4;"]

[2024/03/29 11:20:19.803 +08:00] [INFO] [session.go:3764] [GENERAL_LOG] [conn=2937701555370986845] [user=root@192.168.135.1] [schemaVersion=10423] [txnStartTS=0] [forUpdateTS=0] [isReadConsistency=false] [currentDB=tidb_test] [isPessimistic=false] [sessionTxnMode=PESSIMISTIC] [sql="set session transaction isolation level repeatable read;"]

[2024/03/29 11:20:19.804 +08:00] [INFO] [session.go:3764] [GENERAL_LOG] [conn=2937701555370986845] [user=root@192.168.135.1] [schemaVersion=10423] [txnStartTS=0] [forUpdateTS=0] [isReadConsistency=false] [currentDB=tidb_test] [isPessimistic=false] [sessionTxnMode=PESSIMISTIC] [sql="start transaction;"]

[2024/03/29 11:20:23.043 +08:00] [INFO] [conn.go:1181] ["command dispatched failed"] [conn=2937701555370986845] [connInfo="id:2937701555370986845, addr:192.168.135.1:57074 status:11, collation:utf8mb4_general_ci, user:root"] [command=Query] [status="inTxn:1, autocommit:1"] [sql="
    CREATE TEMPORARY TABLE tmp_customer_base(c_no varchar(20), PRIMARY KEY (c_no));
    INSERT IGNORE INTO tmp_customer_base(c_no) values('11');
"] [txn_mode=PESSIMISTIC] [timestamp=448707276245827608] [err="[schema:1146]Table 'tidb_test.tmp_customer_base' doesn't exist"]

[2024/03/29 11:20:23.138 +08:00] [INFO] [session.go:3764] [GENERAL_LOG] [conn=2937701555370986845] [user=root@192.168.135.1] [schemaVersion=10423] [txnStartTS=448707276245827608] [forUpdateTS=448707276245827608] [isReadConsistency=false] [currentDB=tidb_test] [isPessimistic=true] [sessionTxnMode=PESSIMISTIC] [sql=rollback]

Tried the following operations, none of which reported errors:

1. When the statements are executed in two separate calls, no error is reported.
2. When the SQL from the general log is manually executed as a combined statement, no error is reported.
3. When the connection is changed to MySQL 8.0, no error is reported.

【Observed Unexpected Behavior】
The program throws the following error:

Table 'tidb_test.tmp_customer_base' doesn't exist

【Expected Behavior】
The statements should execute normally without errors.
【Related Components and Specific Versions】
None

| username: DBAER | Original post link

Using TiDB version 7.6 with the MySQL client executes without errors, it should be an incompatibility between the driver and TiDB.

| username: Kongdom | Original post link

It should be as one of the classmates mentioned in the original post, the client sends and executes statements one by one, which is fine. The program executes according to the specified content. Since I specified two statements, the program sends and executes both statements together, which results in an error.

| username: zhanggame1 | Original post link

How about trying global temporary tables, so you don’t have to add table creation statements in the SQL?

| username: Kongdom | Original post link

There might be conflicts with global temporary tables in concurrent situations. :thinking:

| username: zhanggame1 | Original post link

No, the table definition of a global temporary table is visible to the entire TiDB cluster, but the data within the table is only visible to the current transaction. It is suitable for temporarily storing intermediate data within a transaction.

| username: Kongdom | Original post link

:thinking: Is that so? If that’s the case, then a layer of logic needs to be added to check whether the table exists before using it.

However, this bug with local temporary tables does need to be resolved. I suspect this bug is related to the “read committed” issue because after adding a transaction, the business logic within the same transaction cannot retrieve the data updated within the transaction and can only read the data from before the transaction was added.

| username: aytrack | Original post link

This is not an issue with temporary tables; it is caused by tidb_multi_statement_mode. You will encounter this error even when creating non-temporary tables. This issue exists in version 6.5.8, but it has already been fixed in the master version. The specific PR needs to be found.

| username: Kongdom | Original post link

:kissing_heart: Has it been fixed? Please look for the PR.

| username: aytrack | Original post link

Same as this issue: Renaming a table inside a transaction and using it doesn't work · Issue #39664 · pingcap/tidb · GitHub
Fix PR: server: ignore Preprocess error for the `prefetchPointPlanKeys` optimization by tiancaiamao · Pull Request #39945 · pingcap/tidb · GitHub

| username: Kongdom | Original post link

:thinking: In which version was this PR fixed? According to the label, it should have been fixed in 6.5, right?

| username: Kongdom | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.