Detailed introduction to transaction processing in Mysql

What is transaction processing? ? Transaction processing is used to maintain the integrity of the database. It ensures that batches of MySQL operations are either completely executed or not executed.

1. Mysql transaction concept

MySQL transactions are mainly used to process data with large operations and high complexity. A logical execution unit is composed of one or several database operation sequences. This series of operations is either fully executed or all execution is abandoned. In MySQL, only databases or tables using the Innodb database engine support transactions. Transactions are used to manage insert, update, and delete statements.

2. Transaction characteristics: Atomicity (atomicity), Consistency (stability, consistency), isolation (Isolation) and Durability (continuity, reliability). These four properties are also referred to as ACID properties.

　1. Atomicity: A transaction is the smallest execution unit in an application, just like an atom is the smallest particle in nature and has the characteristics of being indivisible. A transaction is the smallest logical execution body that cannot be divided 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 consistency state to another consistency state. A database is in a consistent state when it contains only the results of successfully committed transactions. Consistency is guaranteed through atomicity. There is illegal data (foreign key constraints and the like), and the transaction is withdrawn.

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

　4. Continuity, reliability: Continuity, also known as durability, means that once a transaction is submitted, any changes made to the data must be recorded in permanent storage, usually in a physical database. After the software or hardware crashes, the InnoDB data table driver will use the log file to reconstruct and modify it. Reliability and high speed are incompatible. The innodb_flush_log_at_trx_commit option determines when to save transactions to the log.

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

3. Concept of reading data

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

　2. Non-Repeatable Reads: A transaction reads the same record successively, but the data read twice is different. We call it non-repeatable read. In other words, the data was modified by other transactions between the 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 satisfies its query conditions. This phenomenon is called For phantom reading.

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 reading, reading 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 data 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". Loss of updates is avoided, but dirty reads may occur. In other words, transaction B has read the uncommitted data of transaction A. The SELECT statement is executed in a non-locking manner, so dirty data may be read, and the isolation level is the lowest.

SET session transaction isolation level read uncommitted ;
SET global transaction isolation level read uncommitted;/*全局建议不用*/
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 and add the table to the stored procedure. The transaction is not submitted. View the current database transaction. Status, you can see a data transaction, the transaction level is READ UNCOMMITTED:

drop table if exists student;
create table student(
id int primary key auto_increment comment 'id',
name varchar(100) comment '名称',
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, query the student table, we can see other data under the READ UNCOMMITTED level Transaction uncommitted data: If we check the database transaction status again, 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 commit): The transaction that reads the data allows other transactions to continue to access the row of data, but the uncommitted write transaction will Other transactions will be prohibited from accessing the row. This isolation level avoids dirty reads, but non-repeatable reads may occur. Transaction A reads the data in advance, transaction B updates the data and commits the transaction, and when transaction A reads the data again, the data has changed.

SET session transaction isolation level read committed ;
SET global transaction isolation level read committed; /*全局建议不用*/

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(可重复读取):就是在开始读取数据（事务开启）时，不再允许修改操作,事务开启，不允许其他事务的UPDATE修改操作，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。避免了不可重复读取和脏读，但是有时可能出现幻读。这可以通过“共享读锁”和“排他写锁”实现。

set session transaction isolation level repeatable read;

　　4.串行化、序列化：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。

set session transaction isolation level serializable;

　　　　　　隔离等级 　　脏读 　　不可重复读　　 幻读
　　　　　　读未提交 　　YES　　 YES 　　　　　YES
　　　　　　读已提交 　　NO 　　 YES 　　　　    YES
　　　　　　可重复读 　　NO 　　 NO　　　　　   YES
　　　　　　串行化 　　    NO 　　 NO 　　　　　  NO

五、完整例子包括提交和回滚完整例子

drop procedure if exists pro_new;
delimiter;;
create procedure pro_new(out rtn int)
begin
declare err INT default 0;
-- 如果出现异常，会自动处理并rollback
declare exit handler for sqlexception ROLLBACK ; 
-- 启动事务
set autocommit=0;
start transaction;
insert into student(name,num) values(NULL,2.3);
-- set err = @@IDENTITY; -- =  获取上一次插入的自增ID;
set err =last_insert_id(); -- 获取上一次插入的自增ID
insert into student(name,num) VALUEs('ccc',err);
-- 运行没有异常，提交事务
commit;
-- 设置返回值为1
set rtn=1;
set autocommit=1;
end
;;
delimiter ;;
set @n=1;
call pro_new(@n);
select @n;

The above is the detailed content of Detailed introduction to transaction processing in Mysql. For more information, please follow other related articles on the PHP Chinese website!