MySQL InnoDB Transaction

Four features of transactions

1. atomicity refers to the fact that the entire database transaction is an inseparable unit of work.

2. Consistency: A transaction changes a database from one State to the following consistent state. The integrity constraints of the database are not damaged before and after the transaction starts.

3. isolation the impact of a transaction is invisible to other transactions before the transaction is committed ------ this is achieved through locks

4. Once a persistent transaction is committed, the result is permanent.

Transaction implementation

Isolation can be achieved through the MySQL InnoDB Lock,

Atomicity, consistency, and durability are achieved through database redo and undo.

SQL statements submitted implicitly

The following SQL statements generate an implicit commit operation. After these statements are executed, an implicit commit operation is performed.

1. DDL statement: Alter database... upgrade data directory name ,....

2. Operations used to implicitly modify the MySQL architecture: create user, drop user, Grant, rename user, revoke, and set password.

3. Management statements: Analyze table, cache index, check table, load index into cache, optimize table, and repair table.

 

Transaction operation Statistics

QPS: Question per second, number of requests per second

TPS: transaction per second, the ability to process transactions per second

The TPS calculation method is (com_commit + com_rollback)/time. The premise of this method is that all transactions must be committed explicitly.

Transaction isolation level

The SQL standard defines four isolation levels:

1. Read uncommited

2. Read commited

3. Repeatable read

4. serializable

View the transaction isolation level of the current session

 

View global transaction isolation level

 

At the transaction isolation level of serializble, the InnoDB Storage engine automatically adds lock in share mode after each select statement, that is, a shared lock is applied to each read operation. Therefore, at this transaction isolation level, the read occupies the lock, and the consistent non-locked read is no longer supported. Generally, the serializble isolation level is no longer used in local transactions. The serializable transaction isolation level is mainly used in distributed transactions of the InnoDB Storage engine.

At the transaction isolation level of read commited, in addition to the uniqueness constraints and foreign key constraints, the InnoDB Storage engine does not use the gap lock algorithm.

Distributed transactions

XA transactions can be used to support distributed transactions. When using distributed transactions, the InnoDB Storage engine must use the serializable isolation level to check whether XA transaction support is enabled (enabled by default)

 

By default, MySQL databases are automatically submitted.