Shulou(Shulou.com)06/01 Report--

The SQL standard defines four types of isolation levels, including specific rules that define which changes inside and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrent processing and have lower system overhead.

Read Uncommitted (read uncommitted)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications because its performance is not much better than other levels. Reading uncommitted data is also known as Dirty Read.

Read Committed (read submission)

This is the default isolation level for most database systems (but not the default for MySQL). It satisfies the simple definition of isolation: a transaction can only see changes that have been committed to the transaction. This isolation level also supports so-called non-repeatable reads (Nonrepeatable Read), because other instances of the same transaction may have a new commit during the instance processing, so the same select may return different results.

Repeatable Read (reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently. But in theory, this leads to another thorny problem: Phantom Read. To put it simply, phantom reading means that when the user reads a range of data rows, another transaction inserts a new row in that range, and when the user reads the range of data rows, they will find a new "phantom" row. InnoDB and Falcon storage engines solve this problem through multi-version concurrency control (MVCC,Multiversion Concurrency Control) mechanisms.

Serializable (serializable)

This is the highest isolation level, and it solves the problem of phantom reading by forcing the ordering of transactions so that they cannot conflict with each other. In short, it adds a shared lock to each read row of data. At this level, it can lead to a lot of timeouts and lock competition.

These four isolation levels are implemented with different lock types, which are prone to problems if the same data is read. For example:

Dirty reading (Drity Read): one transaction has updated a piece of data, and another transaction has read the same data at this time. For some reason, if the previous RollBack operates, the data read by the latter transaction will be incorrect.

Non-repeatable read (Non-repeatable read): data inconsistencies between two queries of a transaction, which may be due to the insertion of original data updated by a transaction between the two queries.

Phantom Read: the number of data pens is inconsistent in two queries of a transaction, for example, one transaction queries several columns of data (Row), while another transaction inserts new columns of data at this time, the previous transaction will find several columns of data that it did not have before in the next query.

In MySQL, these four isolation levels are implemented, each of which may cause problems as follows:

Next, you will test several isolation levels using MySQL's client program. The test database is test and the table is tx; table structure:

Idint

Num

Int

The two command line clients are Ameme B; constantly change the isolation level of A, and modify the data on the B side.

(1) set the isolation level of A to read uncommitted (read not submitted)

Mysql > SET tx_isolation='READ-UNCOMMITTED' sets the isolation level to READ-UNCOMMITTED

Before B updates the data:

Client A:

B update data:

Client B:

Client A:

After the above experiment, it can be concluded that transaction B updates a record but does not commit, and transaction A can query out the uncommitted record. Cause dirty reading. Uncommitted reads are the lowest isolation level.

(2) set the transaction isolation level of client A to read committed (read committed)

Mysql > SET tx_isolation='READ-COMMITTED' sets the isolation level to READ-COMMITTED

Before B updates the data:

Client A:

B update data:

Client B:

Client A:

Through the above experiments, it can be concluded that the committed read isolation level solves the problem of dirty reading, but there is a problem of unrepeatable reading, that is, transaction A has inconsistent data in two queries, because transaction B updates a piece of data between two queries. Committed reads allow only committed records to be read, but do not require repeatable readings.

(3) set the isolation level of A to repeatable read (repeatable)

Mysql > SET tx_isolation='REPEATABLE-READ' sets the isolation level to REPEATABLE-READ

Before B updates the data:

Client A:

B update data:

Client B:

Client A:

B insert data:

Client B:

Client A:

From the above experiments, it can be concluded that the repeatable read isolation level only allows the client to read the committed record, and the data updated and committed by other clients can only be seen after the transaction of the client has been committed. This will also cause illusion, because the data read by the client before and after the transaction commit may be different. if other clients update the data and commit the transaction, the current client has not done anything. It is just that the content read before and after the transaction commit is inconsistent, resulting in illusory reading.

(4) set the isolation level of A to serializable (Serializable)

Mysql > SET tx_isolation='SERIALIZABLEA' sets the isolation level to SERIALIZABLEA

The A side opens the transaction and the B side inserts a record

Side An of the transaction:

B side of the transaction:

Because transaction A's isolation level is set to serializable at this time, transaction B can only wait because it is not committed after starting the transaction.

Transaction A commits the transaction:

Transaction A side

Transaction B side

Serializable completely locks the field, and if a transaction queries the same data, it must wait until the previous transaction is completed and unlocked. Is a complete isolation level that locks the corresponding data table, thus causing efficiency problems.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.