MySQL transaction details

Atomicity:
	Either all succeed or all fail. Undo log achieves consistency:
	For example, the sum of the two amounts before and after the transfer remains unchanged. Isolation: The database provides a certain isolation mechanism to ensure that the transaction runs in an "independent" environment that is not affected by external concurrent operations. Lock, mvcc multi-version concurrency control. Durable: Transaction submission persists to disk redo log

Dirty Reads (Dirty Reads) A transaction accesses uncommitted data from another transaction:
	When a transaction is accessing data and modifying the data, and this modification has not yet been committed to the database, another transaction also accesses the data and then uses the data.
Non-Repeatable Reads (a transaction performs the same query twice, but obtains different data):
	A transaction reads some data and then reads the data it read before. It finds that the data is inconsistent with the data it read before. Update and delete phantom reads (a transaction makes the same query twice and obtains different data):
	A transaction re-reads previously queried data according to the same query conditions, but finds that other transactions have inserted new data that meets its query conditions.

Set transaction isolation level A, B
	set session transaction isolation level read uncommitted;
sessionA
Start transaction start transaction;
Insert a data INSERT INTO `db_test`.`t_user`(`id`, `name`) VALUES (5, 'DuQi');
sessionB
Another connection queries select * from t_user;
	+----+----------+
	| id | name |
	+----+----------+
	| 1 | ZhangSan |
	| 2 | LiSi |
	| 3 | WangWu |
	| 4 | LaoWang |
	| 5 | DuQi |
	+----+----------+
	At this time, connection B queries the record id of the uncommitted transaction of connection A, which is 5
	
Here we verify that a session has read uncommitted data from another transaction

Modify the transaction isolation level set session transaction isolation level read committed;
A starts a transaction start transaction;
Verify the update B executes the query statement MySQL [db_test]> select * from t_user;
		+----+----------+
		| id | name |
		+----+----------+
		| 1 | ZhangSan |
		| 2 | LiSi |
		| 3 | WangWu |
		| 4 | LaoWang |
		| 5 | DuQi |
		+----+----------+
	A executes the update statement update t_user set name = 'duqi' where id = 5;
	B executes the query statement start transaction;
		MySQL [db_test]> select * from t_user;
		+----+----------+
		| id | name |
		+----+----------+
		| 1 | ZhangSan |
		| 2 | LiSi |
		| 3 | WangWu |
		| 4 | LaoWang |
		| 5 | DuQi |
		+----+----------+
	A commits the transaction commit;
	B executes the query statement (the results of two queries for the same transaction are inconsistent)
		MySQL [db_test]> select * from t_user;
		+----+----------+
		| id | name |
		+----+----------+
		| 1 | ZhangSan |
		| 2 | LiSi |
		| 3 | WangWu |
		| 4 | LaoWang |
		| 5 | duqi |
		+----+----------+
Continue to verify the deletion. A starts a transaction. B starts a transaction. start transaction;
	A deletes a record delete from t_user where id = 5;
	Transaction B query is normal, and the deleted records are still in MySQL [db_test]> select * from t_user;
		+----+----------+
		| id | name |
		+----+----------+
		| 1 | ZhangSan |
		| 2 | LiSi |
		| 3 | WangWu |
		| 4 | LaoWang |
		| 5 | DuQi |
		+----+----------+
	A commit;
	B continues to query and finds that the results of multiple queries on the same transaction are inconsistent. MySQL [db_test]> select * from t_user;
		+----+----------+
		| id | name |
		+----+----------+
		| 1 | ZhangSan |
		| 2 | LiSi |
		| 3 | WangWu |
		| 4 | LaoWang |
		+----+----------+

Verify the insertion of A and B. Start transaction. start transaction;
	A inserts records INSERT INTO `db_test`.`t_user`(`id`, `name`) VALUES (5, 'DuQi');
	B queries MySQL [db_test]> select * from t_user;
		+----+----------+
		| id | name |
		+----+----------+
		| 1 | ZhangSan |
		| 2 | LiSi |
		| 3 | WangWu |
		| 4 | LaoWang |
		+----+----------+	
	A commits the transaction commit;
	B query can also query the transaction submitted by A MySQL [db_test]> select * from t_user;
		+----+----------+
		| id | name |
		+----+----------+
		| 1 | ZhangSan |
		| 2 | LiSi |
		| 3 | WangWu |
		| 4 | LaoWang |
		| 5 | DuQi |
		+----+----------+

Modify the transaction isolation level set session transaction isolation level repeatable read;
A and B start transaction start transaction;
A inserts a piece of data	
	INSERT INTO `db_test`.`t_user`(`id`, `name`) VALUES (5, 'DuQi');
B Query MySQL [db_test]> select * from t_user;
	+----+----------+
	| id | name |
	+----+----------+
	| 1 | ZhangSan |
	| 2 | LiSi |
	| 3 | WangWu |
	| 4 | LaoWang |
	+----+----------+
A commits the transaction commit;
B transaction query MySQL [db_test]> select * from t_user;
	+----+----------+
	| id | name |
	+----+----------+
	| 1 | ZhangSan |
	| 2 | LiSi |
	| 3 | WangWu |
	| 4 | LaoWang |
	| 5 | DuQi |
	+----+----------+
It may be found that between different transactions, the insertion can be queried. Let's continue to verify the update and deletion. A and B start the transaction. A updates update t_user set name = 'duqi' where id = 5;
	B Query MySQL [db_test]> select * from t_user;
		+----+----------+
		| id | name |
		+----+----------+
		| 1 | ZhangSan |
		| 2 | LiSi |
		| 3 | WangWu |
		| 4 | LaoWang |
		| 5 | DuQi |
		+----+----------+	
	A commits the transaction
	B continues to query MySQL [db_test]> select * from t_user;
		+----+----------+
		| id | name |
		+----+----------+
		| 1 | ZhangSan |
		| 2 | LiSi |
		| 3 | WangWu |
		| 4 | LaoWang |
		| 5 | DuQi |
		+----+----------+
	Let's continue to verify the deletion. A and B start transactions. Transaction A executes the delete operation delete from t_user where id = 5;
		Transaction B executes the query MySQL [db_test]> select * from t_user;
			+----+----------+
			| id | name |
			+----+----------+
			| 1 | ZhangSan |
			| 2 | LiSi |
			| 3 | WangWu |
			| 4 | LaoWang |
			| 5 | duqi |
			+----+----------+
		A commits the transaction, and B continues to query MySQL [db_test]> select * from t_user;
			+----+----------+
			| id | name |
			+----+----------+
			| 1 | ZhangSan |
			| 2 | LiSi |
			| 3 | WangWu |
			| 4 | LaoWang |
			| 5 | duqi |
			+----+----------+
You may find that the REPEATABLE-READ transaction isolation level solves the problems of deletion and update, but the problem of insertion still exists.

The implementation of mvcc in MySQL INNODB is mainly to improve the concurrent performance of the database and to handle read-write conflicts in a better way, so that even when there is a read-write conflict, non-locking and non-blocking concurrent reading can be achieved.

Operations such as select lock in share mode (shared lock), select for update; update, insert, delete (exclusive lock) are all current reads.
Why is it called Current Reading?
	That is, it reads the latest version of the record. When reading, it must ensure that other concurrent transactions cannot modify the current record, and the read record will be locked.

For example, an unlocked select operation is a snapshot read, which is a non-blocking read without locking.
The premise of snapshot read is that the isolation level is not serial level. Snapshot read at serial level will degenerate into current read.
The reason for snapshot read is to improve concurrency performance. The implementation of snapshot read is based on multi-version concurrency control, namely MVCC. MVCC can be considered a variant of row lock, but it avoids locking operations in many cases and reduces overhead.

MVCC multi-version concurrency control refers to maintaining multiple versions of a data so that there is no conflict in read and write operations. Snapshot read is a non-blocking read function of MySQL to implement MVCC.
The specific implementation of the mvcc module in Mysql is implemented by three implicit fields, undo logs, and readview components.

1. Read-read: There is no problem and no concurrency control is required. 2. Read-write: There are thread safety issues, which may cause transaction isolation level problems, and may encounter dirty reads, non-repeatable reads, and phantom reads. 3. Write-write: There are thread safety issues and there may be update loss problems.

1. When reading and writing the database concurrently, it is possible to do so without blocking the write operation during the read operation, and the write operation does not need to block the read operation, thereby improving the performance of concurrent reading and writing of the database. 2. It solves transaction isolation problems such as dirty reads, phantom reads, and non-repeatable reads, but it cannot solve the problem of update loss.

DB_TRX_ID Most recently modified transaction id:
	6 bytes, records the transaction ID that created this record or last modified this record
DB_ROLL_PTR rollback pointer:
	7 bytes, pointing to the previous version of this record, used to cooperate with undolog, pointing to the previous old version DB_ROW_ID hidden primary key:
	6 bytes. If the database table does not have a primary key, InnoDB will automatically generate a 6-byte row_id.

When performing insert operations, the undo log generated is only needed when the transaction is rolled back, and can be discarded immediately after the transaction is committed. When performing update and delete operations, the undo log generated is not only needed when the transaction is rolled back, but also when the snapshot is read, so it cannot be deleted casually. Only when the snapshot read or transaction rollback does not involve the log, the corresponding log will be cleared uniformly by the purge thread (when data is updated or deleted, only the old record is set. If the deleted_id of a record is true, and DB_TRX_ID is visible to the read view of the purge thread, then this record can definitely be cleared)

When performing an insert operation, a corresponding delete statement will be generated. When performing a delete operation, the insert statement of the original data will be backed up. When performing an update, the update statement of the original data will be recorded. This operation is convenient for record rollback.

The biggest function of READ VIEW is to make visibility judgments. That is to say, when a transaction is executing a snapshot read, a Read View is created for the record, and it is used as a condition to determine which version of the data the current transaction can see. It is possible that the latest data is read, or it is possible that the latest data is read, or it is possible that a certain version of the data in the undolog of the current record is read. The visibility algorithm followed by read view is mainly to take out the DB_TRX_ID in the latest record of the data to be modified, and compare it with the ID of other active transactions in the system. If the DB_TRX_ID is compared with the attributes of READ VIEW and does not meet the visibility, then the DB_TRX_ID in the undolog is taken out through the DB_ROLL_PTR rollback pointer for comparison, that is, the DB_TRX_ID in the linked list is traversed until a DB_TRX_ID that meets the conditions is found. The old record where this DB_TRX_ID is located is the latest old version of the data that the current transaction can see.

trx_list:
	A list of values ​​used to maintain the transaction IDs that are active in the system at the time the Read View is generated
up_limit_id:
	Record the minimum ID of the transaction ID in the trx_list list
low_limit_id:
	The system has not yet allocated the next transaction ID when the Read View is generated

1. First, determine whether DB_TRX_ID < up_limit_id. If it is less than up_limit_id, the current transaction can see the record where DB_TRX_ID is located. If it is greater than or equal to, proceed to the next determination. 2. Determine whether DB_TRX_ID >= low_limit_id. If it is greater than or equal to, it means that the record where DB_TRX_ID is located only appears after the Read View is generated, so it is definitely not visible to the current transaction. If it is less than, proceed to the next determination. 3. Determine whether DB_TRX_ID is in an active transaction. If it is, it means that at the time of Read View generation, this transaction is still active and has not been committed. The modified data cannot be seen by the current transaction. If it is not, it means that this transaction has been committed before Read View is generated, so the modified result can be seen.