MySQL Basics (4): Transaction control

View Storage Engine:show engines;

InnoDB support transactions,MyISAM,MEMORY does not support transactions

1, the nature of the transaction: (ACID)

Atomicity: A transaction can no longer be split, either executed or not executed.

Consistency: One transaction execution shifts data from one consistent state to another consistent state

Isolation: Execution of one transaction is not disturbed by other transactions (required by setting isolation level)

Persistence: Once a transaction is committed, the database data is changed forever

2, the creation of a transaction

Before the transaction is closed, it is equivalent to just keeping it in memory.

Step 1: Turn on    transactions set autocommit=0;     Transaction ; "Step 2: Statements in a transaction; Step 3: End transaction     Commit ;     rollback;

3, common concurrency problems

dirty reads: one transaction If the T1 reads a field that has been T2 updated but has not yet been submitted, if T2 is rolled back,the content thatT1 reads is temporary and invalid.

After you set up read commited , even if T2 is updated but not committed,TI reads the data before the update.

non-repeatability: one transaction After T1 reads a field,T2 updates the field, andtheT1 reads twice when the result is different.

After setting repeatable Read, T2 modifies the field even during T1 execution ,T1 T2 modified before and after reading the same two times, read the T2 the result before the modification, T1 the latest data will not be available until the query is submitted.

Phantom reads: a transaction T1 When you manipulate a table, another transaction T2 Insert or delete data at this point, causing the T1 to modify more or less data.

after setting serializable ,T1 is ready to update when there are only two data in the table, when T2 inserts 1 data and commit the transaction, T1 The impact is still two data, T2 will be blocked in the way, and so T1 can be inserted after execution.

4, Isolation Level

Isolation level: The higher the isolation level, the better the consistency of data, but the weaker the concurrency

Mysql Default level is repeatable READ

Oracle only supports read commited and SERIALIZABLE, which is read COMMITTED by default

To query the current isolation    level: Select @ @tx_isolation ; set current MySQL connection / The Global isolation level:      Session/transactionisolationlevelREAD  COMMITTED;

5,savepointcombinedrollbackUse

MySQL Basics (4): Transaction control