Basic knowledge of Database Transaction Management

I recently learned Spring transaction management, summary and general basic knowledge.

Transaction Management Overview

A transaction is a logical unit of work that includes a series of operations. Transaction Processing ensures that data-oriented resources are not updated permanently unless all operations in the transaction unit are successfully completed. By combining a set of related operations into a unit that either succeeds or fails, you can simplify error recovery and make the application more reliable. Transactions have four basic features, namely acid, which we often call. These include:
1. Atomic (atomicity, the "Atom" here indicates that each operation in the transaction is inseparable)
The operations contained in a transaction are considered as a logical unit. The operations in this logical unit either succeed or fail.
2. Consistency (consistency)
Consistency means that only valid data will be written to the database, otherwise it will roll back to the initial state. Transactions ensure that the database status changes from one consistent state to another consistent state.
3. Isolation (isolation)
When multiple transactions are concurrently executed, the execution of one transaction should not affect the execution of other transactions.
4. Durability)
The changes made to the database by committed transactions should be permanently stored in the database.

Exceptions caused by concurrent Transaction Processing
1. Update loss
Transaction 1: update a record.
Transaction 2: update the records in transaction 1.
Transaction 1: Call commit for commit.
Transaction 2: Call commit for commit.
In this case, the modification made by transaction 1 is completely overwritten by the modification of transaction 2, which is called the loss of update.
When two or more transactions select the same row and update the row based on the originally selected value, the update will be lost. Because every transaction does not know the existence of other transactions, the final update will overwrite the updates made by other transactions, which will lead to data loss.

2. Dirty read: A transaction reads data not committed by another transaction, so you may see data that is finally rolled back by another transaction.
Transaction 1: update a record.
Transaction 2: read the records updated by transaction 1.
Transaction 1: Call commit to commit or call rollback for rollback.
In this case, transaction 2 reads data stored in the database memory, which is called dirty read. The read data is dirty data.

For more information, see Dirty read. When a transaction accesses data and modifies the data, the modification has not been committed to the database, another transaction also accesses the data and then uses the data. Because the data has not been committed, the data read by another transaction is dirty data, and the operations performed based on the dirty data may be incorrect.

3. nonrepeatable read: In the same transaction, the same data is read twice and the obtained content is different. That is, when a transaction reads data that has previously been read again, it finds that the data has been modified by another committed transaction.
Transaction 1: query a record.
Transaction 2: update the records queried by transaction 1.
Transaction 2: Call commit for commit.
Transaction 1: query the last record again.
In this case, transaction 1 queries the same data twice, and the obtained content is different, which is called non-repeated read.

4. Phantom read: In the same transaction, the same operation is used to read the data twice and the number of records is different. That is, a transaction re-executes a query and returns a batch of records that meet the query conditions, but these records contain new records generated by other recently committed transactions.
Transaction 1: Query all records in the table
Transaction 2: insert one or more records
Transaction 2: Call commit for commit
Transaction 1: Query all records in the table again
At this time, the record queried twice by the transaction is different, which is called phantom read.

Explanation: phantom read refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which involves all the data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts a new row of data into the table. In the future, the user who operates the first transaction will find that there are still data rows in the table that have not been modified, just like an illusion.

Transaction isolation level
To avoid exceptions in concurrent transaction processing, the standard SQL Specification defines the transaction isolation level in 4.
1. Read uncommitted
The lowest level of transaction isolation is read "uncommitted", which means that the current transaction can read this change even if another concurrent transaction has not been committed. This is not safe. Read uncommitted allows dirty reads, but does not allow loss of updates. 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.
The transaction isolation level at this level is unacceptable for most application systems with strict logic. The appearance of dirty reads will bring great risks to the system's concurrent logic.

2. Read committed
The literal translation is to read "commit", which means that a transaction will not read data that has been modified but not committed by another transaction. Read committed allows repeated reads but does not allow dirty reads. A transaction that reads data allows other concurrent transactions to continue to access this row of data, but uncommitted write transactions will prohibit other transactions from accessing this row.
This level of transaction isolation is the most commonly used level, which is the default isolation level for most databases and also applies to most systems.

3. Repeatable read
The literal translation is "repeatable reading", which means that when the same transaction executes the same query statement successively, the results are the same. This also means that it is impossible for a transaction to update data that has been read but not committed (rolled back) by another transaction. Repeatable read prohibits repeated reads and dirty reads, but sometimes Phantom reads may occur. The transaction that reads data will prohibit the write transaction (but allow the read transaction), and the write transaction will prohibit any other transactions.

4. serializable
The highest level of transaction isolation also provides the strictest isolation mechanism. The literal translation is "serialization", which means that this transaction cannot be executed concurrently by other transactions. Serializable provides a strict transaction isolation level, which can prevent dirty reads, non-repeated reads, and Phantom reads. It requires that the transaction be serialized and executed only one by one, but not concurrently.