Detailed explanation of Mysql transaction processing

1. MySQL transaction concept

MySQL transactions are mainly used to process data with large operation volumes and high complexity. A logical execution unit consists of a sequence of one or several database operations. This series of operations is either executed in full or abandoned. In MySQL, only databases or tables that use the Innodb database engine support transactions. Transactions are used to manage insert, update, and delete statements.

2. Transaction characteristics: Atomicity, Consistency, Isolation and Durability. These four characteristics are also referred to as ACID properties.

1. Atomicity: A transaction is the smallest execution unit in an application, just as an atom is the smallest particle in nature and has the characteristic of being indivisible. A transaction is the smallest indivisible logical execution unit in an application. A group of transactions can either succeed or be withdrawn.

2. Stability and consistency: The result of transaction execution must change the database from one consistent state to another consistent state. A database is in a consistent state when it contains only the results of successfully committed transactions. Consistency is ensured through atomicity. If there is illegal data (such as foreign key constraints), the transaction will be withdrawn.

3. Isolation: The execution of each transaction does not interfere with each other, and the internal operations of any transaction are isolated from other concurrent transactions. In other words: concurrently executed transactions cannot see each other's intermediate states, and concurrently executed transactions cannot affect each other. Transactions run independently. If the result of a transaction affects other transactions, then the other transactions will be withdrawn. 100% isolation of transactions requires sacrificing speed.

4. Persistence and reliability: Persistence is also called persistence, which means that once a transaction is committed, any changes made to the data must be recorded in permanent storage, usually saved in a physical database. After a software or hardware crash, the InnoDB data table driver will use the log file to reconstruct and modify it. Reliability and high speed cannot be achieved at the same time. The innodb_flush_log_at_trx_commit option determines when transactions are saved to the log.

Note: The storage engine MyISAM does not support transactions, but the storage engine InnoDB supports transactions. Transactions are valid only for statements that affect data. show engines View the data engines supported by MySQL lock.

3. Reading Data Concept

1. Dirty Reads: Dirty reads are the reading of dirty data, and dirty data refers to uncommitted data. A transaction is modifying a record. Before the transaction is completed and committed, the data is in a pending state (it may be committed or rolled back). At this time, a second transaction reads the uncommitted data and performs further processing based on it, which will generate an uncommitted data dependency. This phenomenon is called a dirty read.

2. Non-Repeatable Reads: A transaction reads the same record twice, but the data read twice is different. We call it a non-repeatable read. That is to say, the data is modified by other transactions between two reads of this transaction.

3. Phantom Reads: A transaction re-reads previously retrieved data according to the same query conditions, but finds that other transactions have inserted new data that meets its query conditions. This phenomenon is called phantom reads.

4. Transaction Isolation Level

Modify the transaction isolation level syntax:
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

1. Read Uncommitted (unauthorized read, read uncommitted): This is the lowest isolation level, allowing other transactions to see uncommitted data. This level can lead to dirty reads. If a transaction has started writing data, another transaction is not allowed to write at the same time, but other transactions are allowed to read this row of data. This isolation level can be achieved through an exclusive write lock. This avoids update loss, but may cause dirty reads. That is to say, transaction B reads the data that transaction A has not committed. The SELECT statement is executed in a non-locking manner, so it is possible to read dirty data. The isolation level is the lowest.

SET session transaction isolation level read uncommitted;
SET global transaction isolation level read uncommitted;/*Global recommendation not to use*/
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

Create a simple student table, set the id, name, and num fields, start transaction 1, add a stored procedure to the table, and do not commit the transaction. Check the current database transaction status and you can see a data transaction with a transaction level of READ UNCOMMITTED:

drop table if exists student;
create table student(
id int primary key auto_increment comment 'id',
name varchar(100) comment 'name',
num int
);
drop procedure if exists proc_on_sw;
delimiter;;
create procedure proc_on_sw()
begin
start transaction;
insert into student(name,num) value('aaa',1);
select * from information_schema.INNODB_TRX;
end
;;
delimiter;;
call proc_on_sw();

Create a new transaction 2 and query the student table. At the READ UNCOMMITTED level, we can see the uncommitted data of other transactions: Check the database transaction status again and we will see that the status is normal.

start transaction;
select * from student;
commit;
select * from information_schema.INNODB_TRX;

2. Read Committed (authorized read, read committed): The transaction that reads data allows other transactions to continue to access the row of data, but the uncommitted write transaction will prohibit other transactions from accessing the row. This isolation level avoids dirty reads, but non-repeatable reads may occur. Transaction A read the data in advance, transaction B immediately updated the data and committed the transaction, and when transaction A read the data again, the data had changed.

SET session transaction isolation level read committed;
SET global transaction isolation level read committed; /*Global recommendation not to use*/

drop procedure if exists proc_on_up;
delimiter;;
create procedure proc_on_up()
begin
set autocommit=0;
update student set name='cc' where id=1;
commit;
set autocommit=1;
end
;;
delimiter;;
call proc_on_up();
select * from student;

3. Repeatable read: When the data is read (transaction is started), modification operations are no longer allowed. When the transaction is started, UPDATE modification operations of other transactions are not allowed. Non-repeatable read corresponds to modification, that is, UPDATE operation. But there may still be phantom read problems. Because the phantom read problem corresponds to the INSERT operation, not the UPDATE operation. Non-repeatable reads and dirty reads are avoided, but phantom reads may sometimes occur. This can be achieved with "shared read locks" and "exclusive write locks".

set session transaction isolation level repeatable read;

4. Serialization and serialization: provide strict transaction isolation. It requires that transactions be executed serially. Transactions can only be executed one after another, but not concurrently. If transaction serialization cannot be achieved through "row-level locks" alone, other mechanisms must be used to ensure that the newly inserted data will not be accessed by the transaction that has just executed the query operation. Serialization is the highest transaction isolation level, but it also has the highest cost and very low performance, so it is rarely used. At this level, transactions are executed sequentially, which not only avoids dirty reads, non-repeatable reads, but also phantom reads.

set session transaction isolation level serializable;

Isolation Level Dirty Read Non-Repeatable Read Phantom Read Read Uncommitted YES YES YES
Read Committed NO YES YES
Repeatable read NO NO YES
Serialization NO NO NO

5. Complete example including commit and rollback complete example

drop procedure if exists pro_new;
delimiter;;
create procedure pro_new(out rtn int)
begin
declare err INT default 0;
-- If an exception occurs, it will be automatically processed and rollback
declare exit handler for sqlexception ROLLBACK; 
-- Start transaction set autocommit=0;
start transaction;
insert into student(name,num) values(NULL,2.3);
-- set err = @@IDENTITY; -- = Get the auto-increment ID of the last insertion;
set err =last_insert_id(); -- Get the auto-increment ID of the last insertion
insert into student(name,num) VALUEs('ccc',err);
-- If there is no abnormality in the operation, commit the transaction;
-- Set the return value to 1
set rtn=1;
set autocommit=1;
end
;;
delimiter;;
set @n=1;
call pro_new(@n);
select @n;