SoFunction
Updated on 2025-05-17

MySQL single transaction single-transaction option details

In MySQL,LOAD DATA INFILECommands are not executed as a single transaction by default. This means that by default, the data is imported into the table step by step, and if an error is encountered during the import process, the imported data will not be automatically rolled back. This may cause some of the data to be inserted, thus breaking the consistency of the database.

Default behavior

  • Non-transactional storage engines (such as MyISAM): For storage engines that do not support transactions,LOAD DATA INFILEThe operation will directly apply changes and there is no transaction protection.
  • Transactional storage engines (such as InnoDB): Although InnoDB supports transactions,LOAD DATA INFILEBy default, its operations are not wrapped in a transaction. Therefore, if the import fails in the middle, the data that was successfully imported before will be retained in the table and will not be automatically rolled back.
  • By default，LOAD DATA INFILENot executed in a transaction, even on a storage engine that supports transactions.

Example

Suppose you have a CSV file to be loaded withyour_tableInnoDB table, and want to ensure that the import operation is atomic:

LOAD DATA INFILE '/path/to/your/'
INTO TABLE your_table
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

To ensureLOAD DATA INFILEAtomicity of operations and data consistency, especially when processing large amounts of data.

LOAD DATA INFILECommands are used to efficiently import data from external files into database tables. When you need to ensure the atomicity of the import operation (i.e., if all succeed or all fail, there will be no partial data being imported), you can use it in combination--single-transactionOptions and transaction controls to achieve this.

--single-transaction option

--single-transactionis a very useful option, especially when dealing with large amounts of data. It ensures that during the import process, the entire operation is treated as a single transaction. This means:

  • Atomicity: If any error occurs during the import process, all imported data will be rolled back to ensure the consistency of the data.
  • Concurrent control: This option avoids long locking of tables and allows other sessions to continue querying and modifying data by setting the transaction isolation level to Repeatable Read (REPEATABLE READ) and creating a snapshot at the beginning.

Use --single-transaction and transaction control

althoughLOAD DATA INFILEOn InnoDB tables, it is transaction-safe by default, but explicitly uses transaction control statements (BEGIN, COMMIT, ROLLBACK) can provide clearer control. However, in use--single-transactionWhen you do not need to manually start the transaction, because this option itself implies the beginning of a transaction.

Example

Suppose you have a CSV file to be loaded withyour_tableInnoDB table, and you want to make sure that you can roll back in any error situation:

LOAD DATA INFILE '/path/to/your/'
INTO TABLE your_table
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
-- There is no explicit one here BEGIN, because --single-transaction The beginning of the transaction is already implicit
-- If an error occurs，The entire operation will roll back
-- You don't need to be manually COMMIT or ROLLBACK
--single-transaction;

In this example,--single-transactionEnsures that the entire import process is executed as a transaction. If any errors occur, such as violation of uniqueness constraints or type conversion errors, the entire import operation will be rolled back and no part of the imported data will be left.

  • Notice:although--single-transactionProvides transaction support, but it does not change the basic transaction mechanism of MySQL. If you start a transaction manually (for example, byBEGINorSTART TRANSACTION), then executeLOAD DATA INFILE, you need to call it explicitlyCOMMITorROLLBACKto end the transaction.

Things to note

  • Only available for transaction-enabled storage engines: Such as InnoDB. If you try to use MyISAM table--single-transaction, it will not have any effect because MyISAM does not support transactions.
  • Performance impact:Although--single-transactionProvides the benefits of data consistency, but it may slow down imports slightly because it involves more logging and transaction management overhead.
  • Compatible with other options: You can do with otherLOAD DATA INFILEUse options together--single-transaction,For exampleFIELDS TERMINATED BY、LINES TERMINATED BYetc.
  • Error handling: Even if used--single-transaction, you should also check the import results to make sure no errors occur. You can confirm by viewing MySQL error logs or observing the returned messages in the SQL client.

In short, use--single-transactionOptions can be effectively guaranteedLOAD DATA INFILEThe atomicity of operations and data consistency are particularly suitable for batch data import scenarios that require high reliability.

This is the end of this article about the single-transaction option of mysql transaction. For more related contents of mysql single-transaction option, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!