Learning website: www.runoob.com/mysql/mysql…

MySQL transactions are mainly used to: process data with large operations and high complexity. For example, to delete a user, the information associated with the user also needs to be deleted. These database operation statements constitute a transaction.

  • Transactions are only supported in MySQL for databases or tables that use the Innodb database engine.
  • Transaction processing can be used to maintain database integrity by ensuring that batches of SQL statements are either all executed or none executed.
  • Transactions manage INSERT, UPDATE, and DELETE statements.

Generally speaking, transactions must meet 4 ACID conditions: Atomicity, Consistency, Isolation, and Durability.

  • Atomicity: All operations in a transaction either complete or not complete, and do not end up somewhere in between. If a transaction fails during execution, it will be rolled back to the state before the transaction began, as if the transaction had never been executed.
  • Consistency: The integrity of the database is not compromised before and after a transaction. This means that the data written must conform to all the preset rules, including the accuracy of the data, the concatenation of the data, and the ability of the subsequent database to do its predetermined work spontaneously.
  • Isolation: The ability of a database to allow multiple concurrent transactions to read, write, and modify its data at the same time. Isolation prevents data inconsistencies due to cross-execution when multiple transactions are executed concurrently. Transaction isolation can be divided into different levels, including Read Uncommitted, Read Committed, Repeatable Read (InnoDB default) and Serializable.
  • Persistence: After a transaction, changes to the data are permanent and will not be lost even if the system fails.

For isolation, problems occur when transactions are concurrent:

  1. Dirty read: Transaction A reads the data updated by transaction B, and then TRANSACTION B rolls back the data
  2. Non-repeatable read: When transaction A reads the same data for many times, transaction B updates and commits the data during the process of reading the same data for many times. As A result, when transaction A reads the same data for many times, the results are inconsistent.
  3. Phantom reads: system administrators, A database of all the grades of the students from the specific scores to ABCDE level, but the system administrator B at this time by inserting A specific score record, when A system administrator A change after the found there is no change to come over, A record like the illusion, this is called magic to read.

Summary: It’s easy to confuse unrepeatable reading with magic reading. Unrepeatable reading focuses on modification, while magic reading focuses on addition or deletion. To solve the problem of unrepeatable reads, you only need to lock the rows that meet the condition. To solve phantom reads, you need to lock the table. The implementation of transaction isolation mechanism is based on locking mechanism and concurrent scheduling.

Transaction isolation level Dirty read Unrepeatable read Phantom read
Read uncommitted (read-uncommitted) is is is
Read committed (read-committed) no is is
Repeatable read no no is
Serializable no no no

Transaction control statement:

By default on the MySQL command line, transactions are committed automatically, that is, the COMMIT operation is performed immediately after the SQL statement is executed. Therefore, to explicitly START a TRANSACTION, use the commands BEGIN or START TRANSACTION, or execute the command SET AUTOCOMMIT=0, which disables automatic commit using the current session.

  • BEGIN or START TRANSACTION To explicitly START a TRANSACTION;
  • COMMIT can also use COMMIT WORK, but the two are equivalent. COMMIT commits the transaction and makes all changes to the database permanent;
  • ROLLBACK can also use ROLLBACK WORK, but the two are equivalent. A rollback ends the user’s transaction and undoes any uncommitted changes in progress;
  • SAVEPOINT Identifier, SAVEPOINT allows you to create a SAVEPOINT in a transaction. A transaction can have multiple Savepoints.
  • RELEASE SAVEPOINT Identifier Removes a transaction SAVEPOINT. Executing this statement will raise an exception if no SAVEPOINT is specified.
  • ROLLBACK TO Identifier ROLLBACK TO identifier
  • SET TRANSACTION is used to SET the isolation level of a TRANSACTION. InnoDB storage engine provides transaction isolation levels of READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE.