Transaction and Transaction Isolation Level

What is a transaction

A transaction is a sequence of operations that access a database. A database application system accesses the database through a transaction set. The correct execution of a transaction causes the database to transition from one state to another.

Transactions must comply with the ACID principles laid down by ISO/IEC. ACID is the abbreviation for atomicity, consistency, isolation, and durability, which means:

1. Atomicity

That is, they are indivisible, and the transactions are either all executed or none executed. If all the sub-transactions of the transaction are committed successfully, all the database operations are committed and the state of the database changes. If a sub-transaction fails, the database operations of the other sub-transactions are rolled back, that is, the database returns to the state before the transaction is executed and no state transition occurs

2. Consistency

Transaction execution transforms the database from one correct state to another

3. Isolation

Transaction changes to this data are not allowed to be made available to any other transaction until the transaction is correctly committed, that is, its possible results should not be displayed to other transactions until the transaction is correctly committed

4. Persistence

When a transaction is correctly committed, the results are always stored in the database, and even if there are other failures after the transaction is committed, the results of the transaction are saved

Role of transactions

Transaction management is critical for enterprise applications, which ensures that every user's operation is reliable and that the integrity of background data is not compromised even if unusual access conditions occur. Just like ATM of a bank, ATM can normally serve customers, but it is also unavoidable to encounter the operation process and sudden failure. At this time, transactions must ensure that the operation of the account is not effective before the failure, just like the user has not used the ATM at all, in order to ensure that the interests of users and banks are not lost.

Problems with concurrent transactions

For example, transaction A and transaction B manipulate the same resource, transaction A has several sub-transactions, transaction B has several sub-transactions, and transaction A and transaction B have various problems in the case of high concurrency. To sum up, there are five main types of problems: the first type of lost updates, the second type of lost updates, dirty reads, non-repeatable reads, magic reads. Among the five types, the first is missing updates, the second is not important, let alone talk about dirty reading, non-repeatable reading and magic reading.

1. Dirty Reading

The so-called dirty reading means that transaction A reads the data that transaction B has not submitted, such as bank withdrawal, transaction A opens the transaction, then switches to transaction B, transaction B opens the transaction --> takes away 100 yuan, then switches to transaction A, transaction A must read the original data in the database, because transaction B takes away 100 yuan and does not commit. The balance of the accounts in the database must still be the original balance, which is dirty reading.

2. Non-repeatable reading

Non-repeatable reading refers to reading a data twice in a transaction and reading the data inconsistently. Or take bank withdrawal as an example, transaction A opens transaction --> finds out that the balance of the bank card is 1000 yuan, then switches to transaction B opens transaction --> transaction B takes away 100 yuan --> submits, the balance in the database becomes 900 yuan, then switches to transaction A, transaction A finds out the balance of the account again is 900 yuan, so for transaction A, Account balance data read twice in the same transaction is inconsistent, which is not a repeatable read.

3. Illusional Reading

Magic reading refers to the discovery of data that has not been manipulated during an operation within a transaction. For example, Student Information, Transaction A opens a transaction --> Modify all students check-in on that day is false, then switch to Transaction B, Transaction B opens a transaction --> Transaction B inserts a student data, then switch to Transaction A, Transaction A submits a transaction and finds a data that has not been modified by itself. This is a hallucination, as if hallucination had occurred. Hallucinations occur only if transactions in concurrent transactions have insert and delete operations.

Transaction isolation level

Transaction isolation levels are created to address these issues. Why there is a transaction isolation level, because the higher the transaction isolation level, the fewer problems will be generated under concurrency, but the greater the performance cost will be incurred at the same time, so there is often a trade-off between concurrency and performance. Therefore, several levels of transaction isolation are set up so that different projects can choose the appropriate level of transaction isolation based on the concurrency of their own projects and compensate for concurrency problems that may arise outside the level of transaction isolation in the code.

There are four levels of transaction isolation, but Spring offers five for users, so take a look:

1,DEFAULT

The default isolation level, which is different for each database, is used if Spring configures transactions with isolation set to this value. By the way, if you are using MySQL, you can use "select @@tx_isolation" to view the default transaction isolation level

2,READ_UNCOMMITTED

Read uncommitted, i.e. read uncommitted data, so it is clear that this level of isolation does not resolve any of the dirty, non-repeatable, hallucinatory reads and is therefore rarely used

3,READ_COMMITED

Reading committed, that is, being able to read those submitted data, naturally prevents dirty reads, but does not limit non-repeatable and magic reads

4,REPEATABLE_READ

Repeat reading, that is, lock the data after it is read out, similar to "select * from XXX for update". Make it clear that the data is read out for update purposes, so add a lock to prevent others from modifying it. REPEATABLE_READ means the same thing. It reads a piece of data. If the transaction does not end, no other transaction can change the record. This resolves the problem of dirty and unrepeatable reading, but the problem of magic reading still cannot be solved.

5,SERLALIZABLE

Serialization, the highest level of transaction isolation, no matter how many transactions, can execute all the sub-transactions in one transaction before all the sub-transactions in another transaction can be executed. This solves the problem of dirty, non-repeatable and magic reads.

Specialized diagrams on the Web list concurrency issues addressed by transaction isolation levels in tabular form:

Again, it must be emphasized that not the higher the transaction isolation level, the better the transaction isolation level, the higher the transaction isolation level, which means that it is necessary to invest all means to lock to ensure the correctness of the transaction, then the efficiency will be reduced, so there is often a trade-off between efficiency and concurrency correctness in actual development, which is usually set to READ_COMMITED, at this time to avoid dirty reading, concurrency is also good, then through some other means to solve the problem of non-repeatable reading and magic reading is good.

Viewing and modifying the isolation level of things

First, explain several commands that MySQL uses to view and modify transaction isolation levels:

  • View transaction isolation level using select @@tx_isolation
  • Use SET session TRANSACTION ISOLATION LEVEL Serializable to modify the current session transaction isolation level; (The parameters can be Read uncommitted|Read committed|Repeatable read|Serializable)
  • Modify the global transaction isolation level using SET global TRANSACTION ISOLATION LEVEL Serializable; (The parameters can be Read uncommitted|Read committed|Repeatable read|Serializable)

Modify the transaction isolation level of a session, such as MyBatis, getSqlSession(), only for this one-time Session; For example, the CMD command line is only valid for this window.

The global transaction isolation level has been modified so that all subsequent sessions are valid and existing sessions are unaffected.

[Mysql] - Understand the four isolation levels of transactions by example

The SQL standard defines four isolation levels, including specific rules that define which changes within and outside a transaction are visible and which are not.

Low isolation levels generally support higher concurrent processing and have lower overhead.

First, we use the test database, create a new tx table, and open two windows as shown to operate on the same database:

Level 1: Read Uncommitted (read uncommitted)

(1) All transactions can see the results of other uncommitted transactions
(2) This isolation level is rarely used in practical applications because it does not perform much better than other levels.
(3) The problem raised at this level is Dirty Read: uncommitted data is read

#First, modify the isolation level
set tx_isolation='READ-UNCOMMITTED';
select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+

#Transaction A: Start a transaction
start transaction;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#Transaction B: Start a transaction as well (then the two transactions cross)
       In Transactions B Execute UPDATE statement without submitting
start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#Transaction A: Can Transaction A see this updated data at this time?
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |   --->You can see it! That means we read about business B Data not yet submitted
|    2 |    2 |
|    3 |    3 |
+------+------+

#Transaction B: Transaction B rolled back, still uncommitted
rollback;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#Transaction A: What you see in transaction A is also data that B has not committed
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |      --->Dirty reading means I'm in this business(A in)´╝îaffair B Although not submitted, I can see any changes in its data!
|    2 |    2 |
|    3 |    3 |
+------+------+

Level 2: Read Committed

(1) This is the default isolation level for most database systems (but not for MySQL)
(2) It meets the simple definition of isolation: a transaction can only see changes made by committed transactions
(3) The problem with this isolation level is that it is not repeatable Read: Non-repeatable Read means that we may see different results when executing the exact same select statement in the same transaction.
The possible reasons for this are: (1) there is a cross transaction with a new commit, which results in a change in the data; (2) When a database is operated on by multiple instances, other instances of the same transaction may have new commits in between

#Modify isolation level first
set tx_isolation='read-committed';
select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+

#Transaction A: Start a transaction
start transaction;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#Transaction B: Start a transaction as well (then the two transactions cross)
       Update data in this transaction and not commit
start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#Transaction A: Can we see changes in the data in transaction A at this time?
select * from tx; --------------->
+------+------+                |
| id   | num  |                |
+------+------+                |
|    1 |    1 |--->Can't see it!  |
|    2 |    2 |                |
|    3 |    3 |                |
+------+------+                |->Same select Statement with different results
                               |
#Transaction B: What if Transaction B is committed? |
commit;                        |
                               |
#Transaction A: |
select * from tx; --------------->
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |--->Because of transaction B Submitted, so in A We see data changes
|    2 |    2 |
|    3 |    3 |
+------+------+

Level 3: Repeatable Read

(1) This is the default transaction isolation level for MySQL
(2) It ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently
(3) Possible problems at this level - Phantom Read: When a user reads a range of data rows, another transaction inserts new rows in the range. When the user reads a range of data rows again, a new Phantom row is found
(4)InnoDB and Falcon storage engines solve this problem through a multiversion concurrency control (MVCC) mechanism

#First, change the isolation level
set tx_isolation='repeatable-read';
select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

#Transaction A: Start a transaction
start transaction;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#Transaction B: Open a new transaction (then the two transactions cross)
       In Transactions B Update data in and submit
start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |
|    2 |    2 |
|    3 |    3 |
+------+------+
commit;

#Transaction A: Can A see the data change even though transaction B has already been committed?
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 | --->Still invisible!(This Level 2 is different, which also means that level 3 solves the problem of non-repeatable reading)
|    2 |    2 |
|    3 |    3 |
+------+------+

#Transaction A: Transaction A can only see data changes if it has also committed
commit;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Level 4: Serializable

(1) This is the highest isolation level
(2) It solves the magic reading problem by forcing transaction ordering so that it cannot conflict with each other. In short, it is a shared lock on each read row of data.
(3) At this level, it may lead to a large number of timeouts and lock competition

#Modify isolation boundaries first
set tx_isolation='serializable';
select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+

#Transaction A: Open a new transaction
start transaction;

#Transaction B: This cross-transaction cannot change data until A has no commit
start transaction;
insert tx values('4','4');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
update tx set num=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Tags: Database MySQL Interview Transaction

Posted on Tue, 19 Oct 2021 12:32:49 -0400 by hem.bhatt1787