What is a transaction for a database? Which storage engines in MySQL support transactions?

What is a transaction?

A transaction consists of one or more SQL statements, and if all statements execute successfully then the modification will take effect, such as a SQL statement will sell +1, the next +1, and if the second fails, then sales will undo the +1 operation of the first SQL statement. Modifications are added to the database only if all of the statements in the transaction are executed successfully.

Characteristics of a transaction

Transaction specific four characteristics, that is often said acid
1. atomicity (atomicity)
Atomicity means that all operations contained by a transaction are either all successful or fail back, so the operation of a transaction must be fully applied to the database if it succeeds, and cannot have any effect on the database if the operation fails.

2. Consistency (consistency)
Consistency means that a transaction must transform a database from one consistent state to another, meaning that a transaction must be in a consistent state before and after execution.

Take the transfer, assume that user A and User B both the sum of money together is 5000, then no matter how a and B transfer, transfer several times, after the end of the transaction two users of the sum should be 5000, this is the consistency of the transaction.

3. Isolation (Isolation)
Isolation is when multiple users concurrently access the database, such as when the same table operation, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

To achieve this effect: for any two concurrent transactions T1 and T2, in the case of transaction T1, the T2 either ends before the T1 starts, or starts after the T1 ends, so that every transaction does not feel that another transaction is executing concurrently.

4. Persistence (Durability)
Persistence refers to the fact that once a transaction is committed, changes to the data in the database are permanent, even if the database system encounters a failure, and the commit transaction is not lost. The corresponding operation log is saved in MySQL, and the last update can be resumed through the log even if it encounters a failure.

For example, when we use the JDBC operation database, after committing the transaction method, prompt the user transaction operation completes, when our program execution completes until sees the prompt, can determine the transaction and the correct commit, even if this time the database has the problem, also must have our transaction complete execution, Doing so will cause us to see that the transaction is complete, but the database failed to perform a transaction because of a failure.

A transaction-enabled engine in MySQL

The most storage engines used in MySQL are: Innodb,bdb,myisam, memory, and so on. where InnoDB and BDB support transactions and MyISAM and so on do not support transactions.

Reprinted from: 78545749

What is a transaction for a database? Which storage engines in MySQL support transactions?