MySQL database study notes (5) MySQL database transaction processing

MySQL database study notes (5) MySQL database transaction processing


Before learning MySQL transactions, let's start with an example in life: Zhang San is going to transfer 500 yuan to Li Si. It is known that the balance on Zhang San’s original account is 1,000 yuan, and the balance on Li Si’s original account is 0 yuan. This transfer operation is essentially performed in two steps:

  • (1) Zhang San account number minus 500 yuan
  • (2) Li Si account plus 500 yuan

When Zhang San’s account was successfully reduced by 500 yuan, 500 yuan was added to Li Si’s account. At this time, Li Si’s account was found to be abnormal, and then it was necessary to roll back and restore the operation of just deducting 500 yuan from Zhang San’s account. If both operations are successful, submit it.

This example can embody the concept of transaction processing in the MySQL database.


1. The concept of transaction and ACID characteristics

Transaction processing (transaction operation): Ensure that all transactions are executed as a work unit, even if there is a failure, this way of execution cannot be changed. When performing multiple operations in a transaction, either all things are committed (commit), these changes will be permanently saved; or the database management system will abandon the changes made, the entire transaction rollback (rollback) to the original status.

ACID properties of the transactionFeatures
AtomicityAtomicity means that the transaction is an indivisible unit of work, and the operations in the transaction either all happen or none happen.
ConsistencyThe transaction must transform the database from one consistent state to another consistent state.
IsolationTransaction isolation means that the execution of a transaction cannot be interfered by other transactions, that is, the internal operations and data used by a transaction are isolated from other concurrent transactions, and each transaction executed concurrently cannot interfere with each other.
DurabilityPersistence means that once a transaction is committed, its changes to the data in the database are permanent, and other subsequent operations and database failures should not have any impact on it

2. Usage of transaction

The specific steps of transaction usage:

  • (1) Start transaction (start transaction)
  • (2) Execute sql statement (ordinary sql operation)
  • (3) Commit/rollback (commit/rollback)

note:

When building a table, choose the Innodb engine to support transactions

By default, MySQL automatically commits transactions. Each time a SQL statement is executed, if the execution is successful, it will automatically commit to the database, and it cannot be rolled back. If a group of operations need to be in a transaction, then you need to use start transaction, once the rollback or commit ends the current transaction, the subsequent operations will be automatically submitted.

If you need to cancel the auto-commit transaction during the entire process of the current connection and manually commit the transaction, you need to set set autocommit=false; or set autocommit=0;, after setting manual commit, every SQL statement needs to be manually committed. Will really take effect. All operations before rollback or commit are regarded as one transaction, and subsequent operations are regarded as another transaction, which also requires manual commit or rollback.

Note: DDL statements cannot be rolled back.

//mysql开始事务和结束事务,mysql默认是自动提交,执行一句就提交一句。

//如果想要手动提交事务
方式(1)set autocoommit=0;
接下来所有语句都必须手动提交,否则就不会永远生效。

方式(2)start   transaction;
       ...
       commit;或者rollback;


3. The isolation level of the database

For multiple transactions running at the same time (equivalent to multi-threaded concurrency), when these transactions access the same data in the database, if the isolation mechanism is not adopted, thread safety issues will occur:

  • Possible problem 1: Dirty read : For two transactions T1, T2, T1 reads a field that has been updated by T2 but has not yet been committed. Later, if T2 rolls back, the content read by T1 is temporary and invalid.
  • Possible problem 2: Non-repeatability read : For two transactions T1, T2, T1 read a field, then T2 updates and submits the field, and then T1 reads the same field again, the value is different.
  • Possible problem 3: phantom read : For two transactions T1, T2, T1 reads a field from a table, then T2 inserts or deletes some rows in the table, and then T1 reads the same table again There will be more and fewer lines.

Isolation of database transactions : The database system must have the ability to isolate and run various transactions concurrently, so that they will not affect each other and avoid various concurrency problems.

Oracle supports two transaction isolation levels: READ COMMITED, SERIALIZABLE. Oracle's default transaction isolation level is: READ COMMITED.
Mysql supports 4 transaction isolation levels. The default transaction isolation level of Mysql is: REPEATABLE-READ.

Isolation leveldescription
READ-UNCOMMITTEDAllow transactions to read uncommitted data from other transactions. Dirty reads, non-repeatable reads, and phantom reads will all appear
READ-COMMITTEDOnly allow transactions to read data that has been committed by other transactions, which can avoid dirty reads, but the problems of non-repeatable reads and phantom reads will still occur
REPEATABLE-READTo ensure that the transaction can read the same value from a field multiple times, it is like taking a picture of the existing data when the transaction is opened, and other transactions modify the data, regardless of whether the transaction is submitted or not, what I read here is the picture taken Data, dirty reads and non-repeatable reads can be avoided, but the problem of phantom reads still exists.
SERIALIZABLEEnsure that a transaction can read the same row from a table. During the duration of this transaction, prohibit other transactions from performing insert, update, and delete operations on the table. All concurrency problems can be avoided, but the performance is very low.

Every time a mysql program is started, a separate database connection is obtained. Each database connection has a variable @@tx_isolation, which represents the current transaction isolation level.

  • View the current isolation level: SELECT @@tx_isolation;
  • View the global isolation level: select @@global.tx_isolation;
  • Set the isolation level of the current mySQL connection: set tx_isolation ='repeatable-read';
  • Set the global isolation level of the database system: set global tx_isolation ='read-committed';

to sum up

This section mainly learns the transaction processing of MySQL database. What you need to master is the usage of the transaction and the isolation level of the database. At the same time, it should be noted that there is no isolation mechanism, and dirty reads, non-repeatable reads, and phantom reads may occur.

Insert picture description here