How MySQL uses transactions

Basics

A transaction is an atomic operation on a group of SQL statements. That is, if an error occurs in one of the SQL statements in the group, the other SQL statements in the same group will not be executed.

You can use it as a test. After you execute a set of SQL statements, you can check whether the results are correct. If they are correct, you can choose to submit. If they are not correct, you can roll back and restore to the original state.

In MySQL, all operations are automatically committed by default, and become manually committed when a transaction is started.

Basic Use

Open separately

Single opening means opening a transaction for a group of SQL statements.

CREATE TABLE user(
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name CHAR(12) NOT NULL,
  balance INT UNSIGNED 
); -- Create a user table INSERT INTO user(name,balance) VALUES
  ("Yunya",1000),
  ("Ken",500); -- Insert data start transaction; -- Start a transaction, and all addition, deletion and modification operations must be submitted manually UPDATE user SET balance = 500 WHERE name = "Yunya"; -- Yunya transfers 500 to Ken
  UPDATE user SET balance = 1000 WHERE name = "Ken";
  SELECT * FROM user; -- Verify whether there is an error COMMIT; -- Commit the transaction: manually commit the above two UPDATE
  -- ROLLBACK; -- Transaction rollback: Use rollback BEGIN when the transfer amount is incorrect -- Close the transaction, and all addition, deletion and modification operations are automatically committed

Globally enabled

If all SQL statements use transaction operations, we can turn off automatic submission through SET AUTOCOMMIT=0 to enable the transaction mechanism, so that all statements are of transaction type.

-- Turn off autocommit SET AUTOCOMMIT = 0;

INSERT INTO user(name,balance) VALUES
	('Jack',8000);
	
COMMIT;

-- Enable automatic commit SET AUTOCOMMIT = 1;

Transaction Isolation

Concurrency issues

When high-concurrency access encounters isolation issues for multiple transactions, the following may occur:

Dirty read: Transaction A reads the data updated by transaction B, and then B rolls back the operation. In this case, the data read by A is dirty data. Non-repeatable read: Transaction A reads the same data multiple times. During the process of transaction A reading the data multiple times, transaction B updates and commits the data, resulting in inconsistent results when transaction A reads the same data multiple times.
Phantom read: System administrator A changes the grades of all students in the database from specific scores to ABCDE grades, but system administrator B inserts a record with a specific score at this time. When system administrator A finishes the change, he finds that there is still a record that has not been changed, as if an illusion has occurred. This is called phantom read.
Non-repeatable reads and phantom reads are easily confused. Non-repeatable reads focus on modifications, while phantom reads focus on additions or deletions. To solve the problem of non-repeatable reads, you only need to lock the rows that meet the conditions. To solve the problem of phantom reads, you need to lock the table.

Isolation Level

The system default isolation level is level 3, and phantom reads may occur.

Query settings

Query isolation level

select @@tx_isolation;

Setting the isolation level

set session transaction isolation level read uncommitted; -- set session is only valid for the current session, set global is valid globally

The above is the details of how MySQL uses transactions. For more information about MySQL transactions, please pay attention to other related articles on 123WORDPRESS.COM!