What is the business ？

Transactions transform a database from one consistency state to another , There is a very simple SQL Sentence composition , It can also be a complex set of SQL Sentence composition ; When the database commits a transaction , You can ensure that either all changes have been saved , Or all changes will not be saved ; Transaction is a program execution unit that accesses and updates various data items in the database .
stay MySQL innodb Next , Every statement is a transaction ; Can pass set autocommit = 0; Set the current session
Move to submit ;

The transaction ACID characteristic

Atomicity （A)

The transaction is either committed , Or roll back

Uniformity （C)

Consistency is defined by the business , Refers to the transition of a transaction from one consistency state to the next , Its business constraints have not been broken , For example, the only key is still the only key .

Isolation, （I)

Transaction isolation refers to the isolation between the execution of transactions and other transactions , adopt MVCC（ Multi version concurrency control ） And locks .

persistence （D)

After the transaction is committed , The operation of the transaction will be persisted without loss .

Common transaction statements

--  Show open transactions 
START TRANSACTION | BEGIN
--  Commit transaction , And make all changes made to the database persistent 
COMMIT
--  Roll back the transaction , End the user's transaction , And undo all pending changes 
ROLLBACK
--  Create a savepoint , A transaction can have multiple savepoints 
SAVEPOINT identifier
--  Delete a savepoint 
RELEASE SAVEPOINT identifier
--  Transaction rolled back to savepoint 
ROLLBACK TO [SAVEPOINT] identifier

Possible concurrency exceptions of transactions

1、 Dirty reading （ Read uncommitted ）

    Business （A） You can read another transaction （B） Data not submitted in ; That's business A Read dirty data ;

2、 It can't be read repeatedly （ Read submitted ）

  Business （A) You can read another transaction （B） Data submitted in ;

3、 Fantasy reading （REPEATABLE READ）

One read operation in a transaction cannot support the following business logic ; It often occurs in a transaction that a read operation is judged and then a write operation is lost
The situation of failure ;

Four levels of isolation

READ UNCOMMITTED

Read uncommitted ; Reads at this level are unlocked , Write and lock , Write locks release locks after a transaction is committed or rolled back

READ COMMITTED

Read submitted ; Support... From this level MVCC ( Multi version concurrency control ), That is, to provide consistent, unlocked reads ; At this time, the read operation reads the historical snapshot data ; At this isolation level, the latest data of the historical version is read , So read the submitted data

READ COMMITTED

Repeatable ; This level also supports MVCC, At this time, the read operation reads the version data at the beginning of the transaction ;

SERIALIZABLE

Serializable ; At this level, a shared lock is added to the read ; So transactions are executed serially ; At this time, the isolation level is the most severe ;

Concurrent exceptions at different isolation levels