By: Not Washing Dishes Studio – Hotown

Source: Database transaction and isolation levels

The copyright belongs to the author, please indicate the source

In the use of databases, we often think of a set of operations as an independent unit, which constitutes a single logical unit of work called a transaction.

Transaction model

A database system needs to maintain the following four properties of transactions, collectively referred to as ACID (Atomicity, Consistency, Isolation, Persistence).

A simple transaction model

We specify this model with T, and its SQL is roughly of the following form:

-- Operation 1: Deduct 10 yuan from account A
UPDATE account SET amount = amount - 10 WHERE user_name = 'A'

-- Operation 2: Add 10 yuan to account B
UPDATE account SET amount = amount + 10 WHERE user_name = 'B'
Copy the code

T is the simplest transfer model. User A transfers 10 yuan to user B’s account book.

We then use this transaction model to explain the four characteristics of transactions.

First, atomicity

Atomicity means that a transaction contains a single set of operations that are not separable. And if any operation in the transaction fails, ensure that the database is rolled back to the state before the entire transaction.

In the T transaction, there are two operations, one is to subtract $10 from account A and one is to add $10 to account B. The two operations are inseparable.

If we treat these two operations as two separate transactions, then assume the initial state:

Account A: 100 Yuan Account B: 100 yuanCopy the code

Now we have performed operation A, and the balance of account A becomes 90 yuan. And then and then! There was an explosion in the server room due to some uncontrollable force. The end result would be:

Account A: 90 Yuan Account B: 100 yuanCopy the code

A and B have an endless quarrel.

B: Please transfer the money to me.

A: I did! Look, 10 yuan has been charged to my account!

B: I didn’t get it from here. Look for yourself, it’s still 100 yuan!

.

Ii. Consistency

The consistency principle requires that transactions be executed without changing the consistency of the database. That is, if the database is consistent before the transaction, the consistency still exists after the transaction.

Take T transaction as an example. Before T execution, the total account balance of A and B is 200 yuan, so we need to ensure that after T execution, the total account balance of A and B is still 200 yuan.

Three, persistence

The principle of persistence requires that once a transaction has successfully completed execution and committed to the database, the update will be persistent. That is, as long as the transaction is successfully executed, any system failure cannot undo the transaction’s commit.

There are some minor bugs with this concept, such as if the results of a transaction are stored in memory and all data is lost in the event of an outage, we need to commit the data to external disk and keep records of updated information so that the database can be restored to its previous state after a restart. Since this concept is beyond the scope of our discussion, we will not repeat it here.

Four, isolation

Isolation ensures that the state of the system after transactions are executed concurrently is equivalent to the state after those transactions are executed sequentially in some order.

If more than one transaction is executed concurrently, even if we ensure atomicity and consistency of the transaction, the operations are not executed strictly serially, but interspersed in some invisible form that is likely to result in an inconsistent final state.

For example, we call the previous transaction T as transaction T1 and subdivide the operations in T1. Their actual operation in the system should look something like this:

/ * * *read(x) : Passes x from the database to the executionread* * write(x) : Write x from the main memory buffer of the transaction that performed the write and write it back to the database (there is actually a commit process, which is ignored here) */read(A);
A := A-10;
write(A);


read(B);
B := B+10;
write(B);

Copy the code

In addition, we define T2 and calculate the value of A+B:

read(A);
read(B);
A := A+B;
Copy the code

How do parallel transactions perform? If we’re lucky, it might execute completely in T1, T2 order, so we should end up with a temp state of 200.

However, if there is A situation where A in T1 is successfully deducted and entered into the database, and the operation of increasing the balance to B is not completely completed, but T2 is executed after B := B+10 is executed. Although the variable B has indeed changed, it has not been written into the database. So temp in T2 becomes 90+100=190.

The process would look something like this:

-- T1
read(A): A := A-10; write(A); Here the value of A in the database becomes 90read(B); B := B+10; Here B does change, but is not committed to database T2read(A); --A = 90
read(B); Temp := A+B; T1 write(B) -- where changes to B are committed to the databaseCopy the code

To ensure isolation, a concurrency control system exists in the database system to fulfill this responsibility.

The isolation level of the transaction

Before introducing the isolation level of a transaction, let’s introduce the concepts of dirty reads, phantom reads, and unrepeatable reads.

Dirty read, phantom read, unrepeatable read

  • Dirty read: A dirty read is when a transaction accesses data and modifies it, but before the commit, another transaction accesses the same data and modifies it. The general model is as follows (here we use the previously ignored COMMIT operation, which is when the transaction completes and enters the commit state) :
-- T1
read(A);
A := A+1;
write(A);

--T2
read(A);
A := A+2;
write(A);
commit;

-- T1
commit;
Copy the code
  • Not repeatable read: refers to in a transaction, read twice for the same data, but at the end of this transaction has not yet (after first read, second read before), another transaction reads the same data, and modified, then led to two repeated read data inconsistencies, formed the state not repeatable read.
Let's say our initial state, A is equal to 10 T1read(A); -- A = 10;

-- T2
read(A);
A = A+10;
write(A);
commit; -- A = 20;

-- T1
read(A); -- A is equal to 20, different than the first timeCopy the code
  • Phantom: A phantom occurs when two transactions do not execute independently. Here is an example of this using SQL:

-- T1
UPDATE users SET status = 1;

-- T2
insert users (`status`) values ('0')
Copy the code

T1 = 0 ？？？？？？; T1 = 0 ？？？？？？; T1 = 0 ？？？？？？

Transaction isolation level

  • Serializable: The highest level of isolation in SQL that can avoid dirty, phantom, and unrepeatable reads. The cost is relatively heavy, will greatly affect the performance of the database.

  • Repeatable read: Only committed data is allowed to be read, and no other transactions can update the data while a transaction reads an item twice. This state does not avoid illusory reading.

  • Read COMMITTED: Only committed data can be Read, but repeatable reads are not required. This state only avoids dirty reads.

  • Read Uncommitted: Allows uncommitted data to be Read. This is the lowest isolation level, and dirty reads, phantom reads, and unrepeatable reads are unavoidable.

Attention: All isolation levels do not allow dirty writes. That is, if a data item has been written by another transaction that has not committed or terminated yet, no other transaction is allowed to write to it.