Issues with Transaction Failure Caused by Using Bulk Copy

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

Original topic: 关于使用Bulk Copy导致事务失效问题

| username: 健康的腰间盘

[Test Environment for TiDB]
[TiDB Version] 5.7.25-TiDB-v7.1.1
[Reproduction Path] Start a transaction, first insert data into table A using insert, then insert data into table B using Bulk Copy. Without committing, start another transaction to query tables A and B.
[Encountered Problem: Found that tables A and B have already been written to without committing, and using ROLLBACK cannot revert. This issue does not occur in MySQL, where rollback works normally.]

| username: 有猫万事足 | Original post link

The bulk copy of MySQL seems to use LOAD DATA behind the scenes.

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

Here is the documentation for TiDB LOAD DATA:

It is worth noting that the compatibility introduction includes these two sentences:

* Regardless of how many rows are committed in a transaction, `LOAD DATA` will not be rolled back by the [`ROLLBACK`](https://docs.pingcap.com/zh/tidb/stable/sql-statement-rollback) statement in an explicit transaction.
* The `LOAD DATA` statement always executes in optimistic transaction mode, unaffected by the TiDB transaction mode settings.

This completely matches your description.

It can only be said that currently, the compatibility of the LOAD DATA statement with MySQL has some issues.

Since it cannot be rolled back and is executed in an optimistic transaction model, as stated, the issue cannot be submitted as a bug. You probably need to submit a feature/enhancement request on GitHub and see when the official team resolves it.

| username: 健康的腰间盘 | Original post link

Thank you for the reply, it’s very valuable. I would like to ask why the Bulk Copy operation affects the data in Table A and Table B, making it accessible to another transaction even when it is not committed. My current isolation level is REPEATABLE-READ.

| username: 有猫万事足 | Original post link

I don’t quite understand this implementation either, but it looks like it reads and commits at the same time.
So no transaction isolation level will work well.

| username: system | Original post link

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