What is transaction controlled storage

A transaction is a series of operations performed as a logical unit of work that either all succeed or all fail. Transactions ensure that changes to multiple data are handled as a single unit.

  1. In MySQL, only databases or tables that use Innodb storage engine support transactions;
  2. Transactions are used to maintain database integrity and ensure that batches of SQL statements are either executed or not executed.
  3. Transactions manage INSERT, UPDATE, and DELETE statements.

Four characteristics of transactions

If a database claims to support transactions, it must have ACID, Atomicity, Consistency, Isolation, and Durability.

  1. Atomicity: A transaction must be an atomic unit of work in which all or none of the operations are performed.
  2. Consistency: At the end of a transaction, all data must be in a consistent state.
  3. Isolation: Transactions run independently, and multiple transactions are isolated from each other. 100% isolation of transactions sacrifices speed.
  4. Persistence: After a transaction completes, its impact on the system is permanent.

MySQL transaction control

By default, MySQL commits transactions automatically, that is, every INSERT, UPDATE, and DELETE SQL statement is committed immediately after the COMMIT operation. So to start a transaction, either start Transaction or BEGIN can be used, or autoCOMMIT can be set to 0. Common transaction syntax is as follows:

# start transactionbegin# commit transactioncommit; # rollback transactionrollback;
Copy the code