MySQL transaction and mysql Transaction Processing

MySQL transaction and mysql Transaction Processing

MySQL transactions: A group of atomic SQL queries, or an independent unit of work

A transaction-supporting storage engine or relational database must meet ACID testing requirements.

A: atomicity, atomicity; all operations in the entire transaction are either successfully executed or rolled back after all failures.

C: consistency, consistency; the database always changes from one consistent state to another consistent state

I: Isolation, Isolation; operations performed by a firm before submission cannot be seen by others; Isolation has multiple Isolation levels

D: durability: persistence. Once a transaction is committed, the modifications made will be permanently stored in the database.

 

TRANSACTION lifecycle: start transaction --> Execute SQL statement --> end TRANSACTION: (1) COMMIT: COMMIT or (2) ROLLBACK: ROLLBACK

Note: Only transactional storage engines can support such operations

 

 

Autocommit | ON, each SQL statement is automatically committed as a transaction (which affects system I/o performance)

Suggestion: explicitly request and commit transactions, instead of using the "auto commit" function set autocommit = 0

 

Transactions support savepoint

SAVEPOINT identifier example: savepoint sp1 savepoint sp2

ROLLBACK [WORK] TO [SAVEPOINT] identifier example: rollback to sp2

Release savepoint identifier for example: release savepoint sp1

　　

Transaction isolation level:

Read uncommitted (read uncommitted): The Other Transaction modifies the data but has not committed the data. The SELECT statement in this transaction reads the UNCOMMITTED data (dirty READ)

 

Read committed: This transaction reads the latest data after other transactions are COMMITTED. The problem is that in the same transaction, if other transactions modify the same SELECT statement twice before and after the current transaction, different results will be read (no repeat)

 

Repeatable read: In the same transaction, the SELECT result is the state at the transaction start time. Therefore, the results READ by the same SELECT operation will be consistent. However, phantom reading may occur.

 

SERIALIZABILE (serializable): when a transaction is not committed, another transaction will be blocked when performing operations on the same data.

 

Problem: Dirty reading (another transaction modifies the data but has not yet committed, and the SELECT statement in this transaction reads the uncommitted data)

It cannot be read repeatedly (when dirty reads are solved, new data is committed for another transaction during the execution of the same transaction, therefore, the data results read twice in this transaction will be inconsistent)

Phantom read (solves non-repeated reads and ensures that the query results in the same transaction are in the state (consistency) at the beginning of the transaction) however, if another transaction commits new data at the same time, for example, 30 is changed to 40, then the same data has 30 and 40 values at the underlying layer)

Locking read: High concurrency may cause a nightmare, but it maximizes data security.

 

View isolation level:

 

Deadlock: Two or more processes are waiting for each other due to resource competition during execution. Without external force, they will not be able to push forward, the most common InnoDB solution is to solve deadlocks.

 

View transaction logs:

Innodb_log_buffer_size size of the transaction log in the cache

Innodb_log_file_size disk Transaction Log File Size

Innodb_log_files_in_group transaction log files

Innodb_log_group_home_dir transaction log file storage location

Transaction Log File Name:

The preceding parameters cannot be modified during runtime. You need to modify the configuration file and restart the instance.