A transaction is the execution unit of a DBMS. It is made up of a limited number of database operation sequences. But not any database operation sequence can be a transaction. In general, transactions are required to meet 4 conditions (ACID)atomicity (autmic): The business is executing, "either do it or do it all!" ", which means that the transactional part is not allowed to execute. Even if the transaction can not be completed because of the failure, also eliminate the impact on the database when rollback!Consistency (consistency): After the transaction operation, the database is in the same state as the business rules, such as A/b account after mutual transfer, the total amount of the same!Isolation (Isolation): If more than one transaction executes concurrently, it should be done independently of each transaction!persistence (Durability): The transaction is persisted to the database after it is committed.
There are two main ways to handle MySQL transactions. 1. Start with Begin,rollback,commit: Start transaction or BEGIN statement can start a new transaction commit: Commit can commit the current transaction, is the change to a permanent change rollback: ROLLBACK can To rollback the current transaction, cancel its change 2, directly using set to change the MySQL auto-commit mode MySQL default is automatically submitted, that is, you submit a query, it is directly executed! We can use set Autocommit=0 to disable autocommit set autocommit=1 turn on auto commit to implement transaction processing. But note that when you use set autocommit=0, all of your later SQL will be transacted until you end with commit confirmation or rollback, andfor current connection only。only InnoDB and BDB types of data tables in ※mysql can support transactional processing! Other types are not supported!
own understanding (about dirty reading, non-repeatable reading, phantom reading) ※ dirty reads: One transaction reads data that is written by another uncommitted parallel transaction. (Transaction T1 updated the contents of a row of records, but did not commit the changes.) The transaction T2 reads the updated row, and then T1 performs the rollback operation, canceling the modification that was just made. Now the line read by T2 is invalid. ) exp: Xiaoming's score was 89, and transaction a changed his score to 98, but transaction A was not yet committed. at the same time, transaction B is reading Xiaoming's score, reading to Xiaoming's score is 98. then, transaction A to the exception and rolled back the transaction. Xiaoming's score rolled back to 89. finally, transaction B reads the data for Xiaoming with a score of 98 as dirty data, and transaction B makes a dirty read. (This is not the case with most database default object isolation levels) ※ non-REPEATABLE READ: One transaction reread the previously read data and discovers that the data has been modified by another committed transaction. (transaction T1 reads a row of records, and the transaction T2 modifies the line of records that T1 just read. The T1 then reads the line again and finds that it differs from the result just read. This is referred to as "non-repeatable" reading because the row record that T1 originally read has changed. )       EXP:  &NBsp; in transaction A, the read to Xiaoming's score is 89, the operation is not completed, the transaction has not yet been submitted. at the same time, transaction B changed Xiao Ming's score to 98 and submitted a transaction. then, in transaction A, reading Xiaoming's score again, when the salary becomes 98. The result of two reads in a transaction does not cause a non-repeatable read. ※: A transaction re-executes a query, returns a set of rows that match the query criteria, and discovers that the rows have changed because of other recently committed transactions. (transaction T1 reads a result set returned by a specified where clause.) The transaction T2 then inserts a new row of records that exactly satisfies the condition of the WHERE clause in the query condition used by T1. Then T1 again uses the same query to retrieve the table again, but at this point you see the new row that the transaction T2 just inserted. This new line is called "Phantom", because for T1 this line is like a sudden appearance. ) exp: currently has a score of more than 90 students with 15 people, Transaction a reads a total of 15 students with a score of 90 or more. at this point, transaction B inserts a student record with a score of 99. This is, transaction a again reads more than 90 students, recorded as 16 people. This creates a phantom read. (this happens with most database default things isolation levels, which will bring table-level locks)
transaction ISOLATION level description: read UNCOMMITTED: Phantom Reading, non-repeatable reading and dirty reading are allowed; read COMMITTED: Allow Phantom Read and non-repeatable read, but do not allow dirty reading; repeatable read: Allow Phantom reads, but do not allow non-repeatable reads and dirty reads; serializable: Phantom Reading, non-repeatable reading and dirty reading are not allowed; oracle default is read COMMITTED. mysql default is repeatable READ. If the isolation level of the database is reae_uncommitted, Other threads can see uncommitted data, so dirty reads; if the database isolation level is set to read_committed, that is, data that is not submitted is invisible to others and avoids dirty reads; , the data being read receives only the read lock and unlocks after reading, allowing other transactions to modify the data being read by the transaction, regardless of whether the current transaction has ended. Causes non-repeatable reads. repeatable read prevents non-repeatable reads because it locks the data that is being manipulated and only releases the lock until the end of the transaction; repeatable Read only guarantees that the data being manipulated by this transaction is not modified by other transactions, but there is no guarantee that other transactions will commit new data. It is possible that thread 1 will still be able to submit new data to table T1 when the T1 of the Operation table (especially for statistical transactions), which can result in inconsistent results for 12 statistics of the thread, as in the case of hallucinations. serializable because a range lock is obtained and the transaction is aThen a serial execution ensures that no phantom reads occur. Thus, the higher the isolation level, the less interference with other things, and the worse the concurrency performance. two or more transactions may occur when you operate on the same shared recordset: (A) dirty Read (B) non-repeatable READ (C) Phantom read isolation level: (1) Read-uncommit, (2) Read-commit, (3) read-repeatable, (4) read-serializable are used to stop the problem, where: (1) Can't stop anything. (2) block (a) (3) block (a) (B) (4) block (A) (B) (C) (1)--(4) The higher the isolation level, the greater the performance loss.
The default transaction isolation level in MySQL is repeatable read (repeatable READ). SQL statements can be queried: View the transaction isolation level at the InnoDB system level: mysql> SELECT @ @global. tx_isolation; Result: +-----------------------+ | @ @global. tx_isolation | +-----------------------+ | Repeatable-read | +--------------------- --+ View the transaction isolation level at the InnoDB session level: mysql> SELECT @ @tx_isolation; results: +------- ----------+ | @ @tx_isolation | +-----------------+ | Repeatable-read | +-----------------+ To modify the transaction isolation level: mysql> set GLOBAL transaction ISOLATION level read committed; mysql> Set Session transaction isolation level Read Committed;
Transaction processing and isolation levels for MySQL
