Mysql transaction processing details, mysql Transaction Processing

Mysql transaction processing details, mysql Transaction Processing

I. Mysql transaction concepts

MySQL transactions are mainly used to process data with large operations and high complexity. A logical execution unit consists of database operation sequences of one or more steps. These operations are either executed in full or abandoned. In MySQL, transactions are supported only for databases or tables that use the Innodb database engine. Transactions are used to manage insert, update, and delete statements.

Ii. Transaction Features:Atomicity (Atomicity), Consistency (stability, Consistency), Isolation (Isolation) and Durability (continuity, reliability ). These four features are also referred to as ACID.

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 same characteristics that cannot be further divided. A transaction is the smallest logical execution body in an application that cannot be further divided. A group of transactions are either successful or recalled.

2. stability and consistency: the transaction execution result must change the database from one consistent state to another consistent state. When the database only contains the results of successful transaction commit, the database is in a consistent state. Consistency is ensured by atomicity. If there is illegal data (foreign key constraints and so on), the transaction is recalled.

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, the intermediate state of the other party cannot be seen between the transactions that are concurrently executed, and the transactions that are concurrently executed cannot affect each other. Transactions run independently. If the results of one transaction affect other transactions, other transactions will be recalled. 100% isolation of transactions requires a sacrifice of speed.

4. continuity and reliability: persistence is also known as persistence. Once a transaction is committed, any changes made to the data must be recorded in the permanent storage, which is usually stored in the physical database. After the software and hardware crash, the InnoDB data table driver uses the log file to reconstruct and modify the data table. Reliability and high speed cannot both be achieved. The innodb_flush_log_at_trx_commit option determines when to save the transaction to the log.

Note: The storage engine MyISAM does not support transactions. The storage engine InnoDB supports transactions. Transactions are only valid for statements that affect data. Show engines: View data engines supported by mysql locks.

Iii. Concepts of Data Reading

1. Dirty read (Dirty Reads): Dirty read refers to reading 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 (may be committed or rolled back, the second transaction reads the uncommitted data and performs further processing accordingly, resulting in uncommitted data dependency. This phenomenon is called dirty reading.

2. Non-Repeatable Reads: A transaction Reads the same record successively, but the data read twice is different. That is to say, this transaction is modified by another transaction between two reads.

3. phantom Reads: A transaction re-Reads previously retrieved data based on the same query conditions, but finds that other transactions have inserted new data that meets the query conditions, this phenomenon is called phantom read.

Iv. 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 and Read Uncommitted): This is the lowest isolation level, allowing other transactions to see Uncommitted data. This level causes dirty reading. If a transaction has started to write data, the write operation of another transaction is not allowed at the same time, but other transactions are allowed to read this row of data. This isolation level can be achieved through the exclusive write lock. This avoids the loss of updates, but may cause dirty reads. That is to say, transaction B reads the uncommitted data of transaction. The SELECT statement is executed in non-locking mode, so dirty data may be read, with the lowest isolation level.

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

Create a simple student table, set the id, name, and num fields, and enable transaction 1. Add a stored procedure to the table. If the transaction is not committed, view the transaction status of the current database. A data transaction is displayed, 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 'name', num int); drop procedure if exists proc_on_sw; delimiter; create procedure proc_on_sw () beginstart transaction; insert into student (name, num) value ('aaa', 1); select * from information_schema.INNODB_TRX; end; delimiter ;; call proc_on_sw ();

Create 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 and Read commit): transactions that Read data allow other transactions to continue to access this row of data, but uncommitted write transactions will prohibit other transactions from accessing this row. This isolation level avoids dirty reads, but may prevent repeated reads. Transaction A reads the data in advance, and transaction B updates the data immediately and commits the transaction. 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;/* global recommendation not use */drop procedure if exists proc_on_up; delimiter; create procedure proc_on_up () beginset 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 transaction starts to read data (the transaction is started), the modification operation is no longer allowed, the transaction is started, and the UPDATE modification operation of other transactions is not allowed, the change is the UPDATE operation. However, there may be phantom read problems. Because phantom read problems correspond to INSERT operations rather than UPDATE operations. Avoid repeated reads and dirty reads, but sometimes Phantom reads may occur. This can be achieved through the "shared read lock" and "exclusive write lock.

set session transaction isolation level repeatable read;

4. serialization: provides strict transaction isolation. It requires the transaction to be serialized. The transaction can only be executed one by one, but cannot be executed concurrently. If transaction serialization is not possible only through the "Row-Level Lock", other mechanisms must be used to ensure that the newly inserted data is not accessed by the transaction that just performs the query operation. Serialization is the highest level of transaction isolation, with the highest cost and low performance. It is rarely used. At this level, transaction sequence execution can avoid dirty reads and non-repeated reads, it also avoids phantom reading.

set session transaction isolation level serializable;

Isolation level dirty read cannot be repeated phantom read
Read not submitted YES
Read submitted NO YES
Repeatable read NO YES
Serializing NO

5. Complete examples include submission and rollback

Drop procedure if exists pro_new; delimiter; create procedure pro_new (out rtn int) begindeclare err INT default 0; -- if an exception occurs, it will automatically process and rollbackdeclare 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 insert; set err = last_insert_id (); -- get the auto-increment IDinsert into student (name, num) VALUEs ('ccc ', err); -- run without exception, commit transaction commit; -- set the return value to 1 set rtn = 1; set autocommit = 1; end; delimiter; set @ n = 1; call pro_new (@ n); select @ n;