1, Mysql transaction concept

Mysql transactions are mainly used to process data with large amount of operations and high complexity. For example, in the personnel management system, to delete a person, you need to delete not only the basic data of the person, but also the information related to the person, such as mailbox, article, etc. In this way, these database operation statements constitute a transaction!

• Transaction is a mechanism and an operation sequence, which contains a set of database operation commands, and all commands are submitted or revoked to the system as a whole, that is, this set of database commands are either executed or not executed.
• Transaction is an inseparable work logic unit. When performing concurrent operations on the database system, transaction is the smallest control unit.
• Transactions are applicable to the scenario of database systems operated by multiple users at the same time, such as banks, insurance companies and securities trading systems.
• Transaction is to ensure data consistency through transaction integrity.

   in short, the so-called transaction is a sequence of operations. These operations are either executed or not executed. It is an inseparable work unit.

2, ACID characteristics of transactions

ACID refers to the four characteristics that a transaction should have in a reliable database management system (DBMS):

• Atomicity
• Consistency
• Isolation
• Durability

  these are several characteristics of a reliable database.

1. Atomicity

A transaction is an inseparable work unit. Operations in a transaction either occur or do not occur.

• A transaction is a complete operation, and the elements of a transaction are inseparable
• All elements in a transaction must be committed or rolled back as a whole
• If any element in the transaction fails, the entire transaction fails

Case:
   when A transfers 100 yuan to B, it only executes the deduction statement and submits it. At this time, if there is A sudden power failure, account A has been deducted, but account B has not received the increase, which will cause disputes in life. In this case, transaction atomicity is required to ensure that transactions are either executed or not executed.

2. Consistency

It means that the integrity constraint of the database is not destroyed before the transaction starts and after the transaction ends.

• When the transaction completes, the data must be in a consistent state
• Before the transaction starts, the data stored in the database is in a consistent state
• Data may be in an inconsistent state in an ongoing transaction
• When the transaction completes successfully, the data must return to the known consistent state again

case
  for bank transfer transactions, no matter whether the transaction is successful or failed, it should be ensured that the total deposits of A and B in the table after the transaction is completed are consistent with those before the transaction is executed.

3. Isolation

In a concurrent environment, when different transactions manipulate the same data at the same time, each transaction has its own complete data space.

• All concurrent transactions that modify data are isolated from each other, indicating that the transaction must be independent and should not depend on or affect other transactions in any way
• A transaction that modifies data can access it before another transaction that uses the same data starts, or after another transaction that uses the same data ends
• The execution of one transaction cannot be disturbed by other transactions

4. Persistence

After the transaction is completed, the changes made by the transaction to the database will be permanently saved in the database and will not be rolled back.

• It means that the result of transaction processing is permanent regardless of whether the system fails or not
• Once the transaction is committed, the effect of the transaction will be permanently retained in the database

3, How transactions interact

1. Dirty reading

Read uncommitted data
   dirty reading refers to reading uncommitted data of other transactions. Uncommitted means that these data may be rolled back, that is, they may not be saved in the database, that is, nonexistent data. Read the data that must eventually exist, which is dirty reading.

Case list
  for example, transaction B modifies data X during execution. Before committing, transaction A reads x, but transaction B rolls back, so transaction A forms A dirty read. In other words, the data read by the current transaction is the data that other transactions want to modify but failed to modify.

2. Non repeatable reading

The data content is inconsistent after repeated reading
  two identical queries in a transaction return different data. This is caused by the submission of other transaction modifications in the system during query.

Case list
   transaction A obtains A row of record row1 for the first time. After transaction B submits the modification, transaction A obtains row1 for the second time, but the column content has changed.

3. Unreal reading

The total amount of data is inconsistent after repeated reading
  a transaction modifies the data in a table, which involves all data rows in the table. At the same time, another transaction also modifies the data in the table. This modification is to insert a new row of data into the table. Then, the user who operates the previous transaction will find that there are still unmodified data rows in the table, as if there was an illusion.

Case list
   suppose transaction A changes the contents of some rows but has not yet committed, at this time, transaction B inserts the same record row as the record before transaction A changes, and commits before transaction A commits. At this time, query in transaction A and find that it seems that the changes just made have no effect on some data, but transaction B has just inserted them, Let the user feel very magical and feel that there is an illusion, which is called Unreal reading.

4. Missing updates

   two transactions read the same record at the same time. A modifies the record first, and B also modifies the record (B does not know that a has modified it). After B submits the data, the modification result of B overwrites the modification result of A.

Case list
A 30 - > 40 transactions are completed first
B 30 - > 50 transactions completed
The transaction result of B will overwrite the transaction result of A, and the final value is 50

4, Transaction isolation level

• Read uncommitted: read uncommitted data - dirty reads are not resolved

Dirty reading is allowed. As long as other transactions modify the data, even if it is not committed, this transaction can see the modified data value. That is, the number of uncommitted transaction modifications in other sessions can be read.

• Read committed: read committed data - dirty reads can be resolved

Only submitted data can be read. Most databases such as Oracle are at this level by default.

• Repeatable read: Reread read - can solve dirty read and non repeatable read (mysql default)

Repeatable. No matter whether other transactions modify and commit data, the data values seen in this transaction are always unaffected by other transactions.

• serializable: it can solve dirty read, non repeatable read and virtual read - equivalent to locking the table

For fully serialized reads, table level shared locks need to be obtained for each read, and reads and writes will block each other.

mysql The default transaction level is repeatable read，and Oracle and SQL Server yes read committed

The scope of transaction isolation level is divided into two types

• Global level: valid for all sessions
• Session level: valid only for the current session

Transaction isolation level query

Query global transaction isolation level

show global variables like '%isolation%';
SELECT @@global.tx_isolation;

Query session transaction isolation level

show session variables like '%isolation%';
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

Set global transaction isolation level

set global transaction isolation level read committed;

Set session transaction isolation level

set session transaction isolation level read committed;

5, Transaction control statement

> BEGIN or START TRANSACTION: Open a transaction explicitly.
> COMMIT or COMMIT WORK: Commit the transaction and make all changes made to the database permanent.
> ROLLBACK or ROLLBACK WORK: Rollback ends the user's transaction and undoes any uncommitted changes in progress.
> SAVEPOINT S1: use SAVEPOINT It is allowed to create a rollback point in a transaction. There can be multiple rollback points in a transaction SAVEPOINT. "S1" Represents the name of the rollback point.
> ROLLBACK TO [SAVEPOINT] S1: Roll back the transaction to the marker point.

1. Create a template

create database SCHOOL;
use SCHOOL;

create table info(  
id int(10) primary key not null,  
name varchar(40),  
money double  
);

insert into info values(1,'A',1000);  
insert into info values(2,'B',1000); 
select * from info;

2. Test commit transactions

begin;
update info set money= money - 100 where name='A';
select * from info;
commit;
quit
---------------
mysql -u root -p
use SCHOOL;
select * from info;

3. Test rollback transaction

begin;
update info set money= money + 100 where name='A';
select * from info;
rollback;
quit
---------------
mysql -u root -p
use SCHOOL;
select * from info;

4. Test multipoint rollback

begin;
update info set money= money + 100 where name='A';
select * from info;
SAVEPOINT S1;
update info set money= money + 100 where name='B';
select * from info;
SAVEPOINT S2;
insert into info values(3,'C',1000);
select * from info;
ROLLBACK TO S1;
select * from info;

5. Use set to set control transactions

SET AUTOCOMMIT=0;						#Disable automatic submission
SET AUTOCOMMIT=1;						#Enable automatic submission. Mysql defaults to 1
SHOW VARIABLES LIKE 'AUTOCOMMIT';		#View the AUTOCOMMIT value in Mysql

If Auto commit is not enabled, all mysql operations connected to the current session will be treated as a transaction until you enter rollback commit; The current transaction is over. Before the end of the current transaction, the operation results of any current session cannot be read when a new mysql connection is made.
If Auto commit is enabled, mysql will treat each sql statement as a transaction, and then automatically commit.
Of course, whether it is turned on or not, begin; commit|rollback; Are independent affairs.

Example:

use SCHOOL;
select * from info;
SET AUTOCOMMIT=0;
SHOW VARIABLES LIKE 'AUTOCOMMIT';
update info set money= money + 100 where name='B';
select * from info;
quit
----------------
mysql -u root -p
use SCHOOL;
select * from info;