mysql transaction

1. Business overview

When multiple users request to modify a certain data at the same time, the transaction can ensure that the data changes from one consistent state to another consistent state. For example, if user A transfers 10,000 yuan to user B, it needs to reduce 10,000 yuan in user A's account. , Add 10,000 yuan to user B's account. These two operations should be an atomic operation. Either both are modified successfully, or neither of these two operations occurs.

Insert picture description here


In MySQl, a transaction is composed of one or more MySQL statements in a single unit. In this unit, each MySQL statement is interdependent, and the entire unit is an indivisible whole. If the execution of the statement fails or an error occurs, the entire unit will rollback, and all affected data will return to the state before the transaction started; if all the SQL statements in the unit successfully execute the commit, the transaction is executed smoothly.

Transactions can guarantee the atomicity of multiple operations, either all succeed or all fail. For the database, the transaction guarantees that the batch of DML will either all succeed or all fail.

Transactions have four ACID characteristics:
1)原子性(Atomicity)
   把事务中所有的操作看作是一个整体,事务对数据的修改要么全部完全提交要么全部回滚
2)一致性(Consistency)
   在事务完成时,必须使所有的数据从一个一致性状态变更为另外一个一致性状态
3)隔离性(Isolation)
   一个事务中的操作语句对数据所做的修改必须与其他事务所做的修改相隔离.
4)持久性(Durability)
   在事务完成后,所做的修改是永久的

Note that the InnoDB storage engine in the MySQL database supports transactions, but MyISAM does not support transactions.

Two: automatic submission

There are two variables that can control behavior in mysql, they are autocommit and
transaction isolation level variables.

If you do not change the automatic submission variable in mysql, the system will automatically submit the results to the database. If the user wants to control the mysql automatic submission parameters, the mode of submission can be changed, and it must be achieved through the autocommit variable.

Turn off the syntax format of automatic submission:

set autocommit=0;
Insert picture description here

View the current automatic submission status:

select @@autocommit;
Insert picture description here

• Auto-commit mode is used to determine how and when new transactions are started.

• Enable auto-commit mode (default state mysql opens auto-commit mode):

-If the auto-commit mode is enabled, a single DML statement will automatically start a new transaction.
– If the statement is executed successfully, the transaction will be automatically committed and the execution result of the statement will be permanently saved. – If the execution of the statement fails, the transaction will be automatically rolled back and the result of the statement will be cancelled.
-In auto-commit mode, you can still use the start transanction statement to explicitly start the transaction. At this time, a transaction can still contain multiple statements until these statements are committed or rolled back.

• Disable automatic submission mode:

– If automatic commit is disabled, the transaction can span multiple statements.
-In this case, the transaction can use COMMIT (commit transaction) and ROLLBACK (rollback transaction) statements to explicitly commit or roll back multiple statements.

• The autocommit mode can be controlled by the server variable autocommit.

• E.g:

By default, executing DML statements is actually opening a transaction

mysql automatically mentions DML statements by default, and submit it once as long as a DML statement is executed;
Turn off automatic submission: start transaction; (equivalent to the meaning of opening a transaction)

Note about transaction rollback: only insert, delete and update statements can be rolled back, select cannot be rolled back (rolling select has no meaning), and cannot be rolled back for create, drop, and alter. Transactions are only effective for DML statements .

Note: Commit the transaction: commit; rollback the transaction rollback;

Three: Transaction execution principle and execution steps

Insert picture description here

The general process of creating things:

Initialize the transaction, create the transaction, use the SELECT statement to query whether the data is entered and commit the transaction (if the user does not perform the transaction commit after the database is completed, the system will perform a rollback operation by default. If the user chooses to cancel the transaction before committing the transaction, the user cancels All previous transactions will be cancelled, and the database system will return to the initial state)

Transaction execution steps:

1.关闭自动提交set autocommit=0;

To turn off auto-commit, first declare all SQL statements after initializing the MySQL transaction as a whole (atomic), and then use the start transaction; command in MySQL to mark the beginning of a transaction.

2.语法格式start transaction;   //开启事务

If after the user enters the above code, the MySQL database does not give a warning, prompt or error message, it means that the user has successfully initialized the transaction.

3.执行相关的DML语句块:insert update delete

insert into temp values("1","fa");
update temp set value='2' where id=1;
delete from temo where id=1;

4.回滚事务或者提交事务

Before the user submits the thing, the sentence of the thing belongs to the state that has not been written into the file, and belongs to the buffer state. At this time, other users cannot query the relevant data.

Commit transaction syntax commit;
Syntax rollback for transaction rollback;
Insert picture description here

Three: transaction isolation level

1. Some problems with transaction concurrency, because the more secure the database, the lower the flexibility of the database. Sometimes for business needs, it is necessary to balance the security and flexibility of the database. At this time, transaction isolation is needed. level.

Some problems with transaction concurrency

• The isolation level of transactions determines the level of visibility between transactions.
• When multiple clients access the same table concurrently, the following consistency problems may occur:

(1) Dirty Read

A transaction starts to read a row of data, but another transaction has updated the data but failed to commit in time, which results in dirty reads.
After two transactions T1, T2, and T1 read the fields that have been updated by T2 but have not yet been committed, if T2 rolls back, the content read by T1 is temporary and invalid.

(2) Non-repeatable Read

In the same transaction, the same read operation produces different results for the same data before and after the two reads, which is non-repeatable read.
For two transactions T1 and T2, T1 reads a field, and then after T2 updates the field, T1 reads the same field again and the value is different.

(3) Phantom Read

Phantom read refers to rows that did not exist in the same transaction before, and new rows appear due to the commit of other transactions.
For two transactions T1 and T2, T1 reads a field from a table, and then T2 inserts some new rows in the table. If T1 reads the same table again, there will be a few more rows, causing before and after The data read is inconsistent.

Isolation of database transactions:

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

The degree of isolation between a transaction and other transactions is called the isolation level:

The database specifies a variety of transaction isolation levels, and different isolation levels correspond to different levels of interference. The higher the isolation level, the better the data can be executed, but the weaker the concurrency.
2. Isolation level

Different databases have different isolation levels. The mysql database knows four isolation levels, and the Oracle database supports two isolation levels.

Insert picture description here


InnoDB implements four isolation levels to control the changes made by the transaction and notify the changes to other concurrent transactions:

-Read uncommitted (READ UMCOMMITTED) allows one transaction to see the uncommitted changes of other transactions.
-READ COMMITTED allows a transaction to only see the modifications that have been committed by other transactions, and the uncommitted modifications are invisible.
-Repeatable read (REPEATABLE READ) to
ensure that if you execute the same SELECT statement twice in a transaction, you can get the same results, regardless of whether other transactions commit these changes. (Bank ledger)
The isolation level is the default setting of InnoDB.
– Serialization (SERIALIZABLE) [Serialization] Completely isolate one transaction from other transactions.
Insert picture description here

Four. View the isolation level

The server variable tx_isolation (including session-level and global-level variables) stores the current session isolation level. In order to view the current isolation level, the tx_isolation variable can be accessed.

– View the current isolation level of the session level:

mysql> SELECT @@tx_isolation;
or
mysql> SELEC @@session.tx_isolation;

– View the isolation level at the global level:

mysql> SELECT @@global.tx_isolation;
Insert picture description here


Insert picture description here