Transaction: understanding is a processing unit. This processing unit may contain deleted and modified operations that need to be processed. It is necessary to ensure that the processing unit is successfully completed or not executed at all.

Transaction characteristics: atomicity, consistency, isolation and persistence

Transactions are divided into display transactions and implicit transactions. Display transactions are operated by developers. Implicit transactions enable the automatic submission function by default

1. Check whether the auto submit function is enabled, on yes, off No

show variables like 'autocommit';

2. Set the submission method, 0 is not automatic, 1 is automatic

 set autocommit=1;

 3. Create a table

CREATE TABLE `stusent` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

4. Start transaction, commit transaction, rollback

START TRANSACTION;
INSERT INTO `wcy`.`stusent` ( `id`, `name`, `age` )
VALUES
	( 1, 'Zhang San', 20 );
INSERT INTO `wcy`.`stusent` ( `id`, `name`, `age` )
VALUES
	( 2, 'Li Si', 10 );
INSERT INTO `wcy`.`stusent` ( `id`, `name`, `age` )
VALUES
	( 3, 'wangwu', 18 );
INSERT INTO `wcy`.`stusent` ( `id`, `name`, `age` )
VALUES
	( 4, 'zhaoliu', 20 );
COMMIT;

START TRANSACTION;
DELETE 
FROM
	stusent 
WHERE
	age = 20;
ROLLBACK;

5. Use of savepoint keyword; A batch of data needs to be executed. You can divide this batch of data into several small batches, set a savepoint on a batch, and then roll back on this savepoint

START TRANSACTION;
INSERT INTO `wcy`.`stusent` ( `id`, `name`, `age` )
VALUES
	( 1, 'Zhang San', 20 );
INSERT INTO `wcy`.`stusent` ( `id`, `name`, `age` )
VALUES
	( 2, 'Li Si', 10 );
	-- Set a save point
SAVEPOINT parm;
INSERT INTO `wcy`.`stusent` ( `id`, `name`, `age` )
VALUES
	( 3, 'wangwu', 18 );
INSERT INTO `wcy`.`stusent` ( `id`, `name`, `age` )
VALUES
	( 4, 'zhaoliu', 20 );
	-- Rollback to parm Save it
ROLLBACK TO parm;
COMMIT;

select * from stusent

6. Set read-only transaction; Only query, not delete,update,insert, etc

-- Set transaction read-only
start transaction read only;

select * from stusent

DELETE from stusent where age = 20

 7. Dirty read (read uncommitted): a transaction reads uncommitted data from another transaction during execution.

For example, transaction 1 starts to execute, and transaction 2 then executes. Transaction 2 reads the data of transaction 1 during execution, and transaction 1 has not been committed. Transaction 1 may commit successfully, commit failed, and roll back the data, resulting in problems with the data read by transaction 2, which is called dirty reading.

8. Read committed: all data read during transaction execution are committed data of other transactions;

For example, when transaction 1 performs data reading, the data read is the data submitted by transactions 2, 3 and 4

9. Repeatable reading: no matter how many operations are performed during transaction execution, the results read each time are the same.

10. Unreal reading: in a transaction, there is no data in the query database, but an error is reported when inserting (modifying), which is understood as unreal reading.

For example, transaction 1 queries a piece of data in the database table, and if there is no query, then inserts a piece of data and an error is reported; Is unreal reading;

The reason is that during the query time of transaction 1, transaction 2 performs the insertion action, and an error is reported when transaction 1 is re inserted. Also, since transaction 1 is repeatable, the results of each query are the same.

11. Isolation level of transactions

When multiple transactions are executed at the same time, the isolation level of transactions is required to ensure the correctness of data;

For example, during the execution of transaction 1, transaction 2 can be allowed to read the uncommitted data of transaction 1, or no permission can be restricted to read the uncommitted data;

There are four isolation levels:

Read uncommitted: read uncommitted

Read committed: READ-COMMITTED

Repeatable read: REPEATABLE-READ

Serial: serial

The isolation level in the above 4 is getting stronger and stronger, and the concurrency will be low