Isolation level of the database

First, what is the database?

a database is a repository of data that is organized, stored, and managed according to a structure that is built on a computer's storage device.

Second, why should the isolation mechanism be needed?

Through the understanding of database definitions, in other words, the data is stored in a file in the form of a physical structure.

The operation of the file is the operation of the IO stream, that is, the operation of reading and writing .

And for our usual data operations, no matter how complex the business, are select and Insert/update/delete.

Let's try to imagine what would happen if I read and write in a multithreaded high concurrency.

If you do not do any processing, there will be a data processing error. That's what we're talking about, dirty reading or phantom reading.

In this case, the usual practice in Java is locking . Corresponding to the database is exclusive locks, shared locks and so on.

and how does that lock handle?

Locking first should be clear yes, when to lock? What's the lock?

When multiple threads are manipulating the same data, we should not lock them.

And the isolation mechanism of database is the lock mechanism of data operation. Of course, the mechanism of the database is far more complex than this, just want to let everyone understand that all the center of gravity is the data, not to be confused by various terminology and forced.

Thirdly, there are four isolation levels in the database:

1. READ UNCOMMITTED (Read UNCOMMITTED content)

2. Read Committed (read submit content)

3. Repeatable Read (can be reread)

4. Serializable (Serializable)

Four levels of isolation:

1. READ UNCOMMITTED (reads uncommitted content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. Reading uncommitted data is also known as dirty reading (Dirty read).

2. Read Committed (reads the submission content)

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

3, Repeatable Read (can be reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. In theory, however, this can lead to another tricky problem: Phantom reading (Phantom read). To put it simply, Phantom reads when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism.

4, Serializable (serializable)

          This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to collide with each other. In short, it is added shared lock . At this level, a large number of timeouts and lock competitions can result.

Once the data is read, the thing sheet is locked . Allow only other transactions to read data, no modifications, and new data operations. Must wait for the current transaction to end.

Iv. 4 major features of the business

    4.1. atomicity (atomicity): The transaction isDatabaseLogical work unit, which modifies the database either in its entirety or not in all execution.
4.2, Consistency (CONSISTEMCY): Before and after the transaction, the state of the database satisfies all the integrity constraints.
4.3. Isolation (Isolation): N transactions that execute concurrently are isolated, one does not affect one, and a transaction cannot be seen by other transactions until it has a commit (by setting the isolation level of the database).
4.4. Persistence (Durability): persistence means that updates to committed transactions cannot be lost when a system or media failure occurs. Persistence is primarily the recovery performance of the DBMS.

Five, dirty reading, non-repeatable reading, Phantom reading

5.1 Dirty reading, non-repeatable reading, and phantom reading are all the embodiment of data operation.

5.2 Dirty reads, non-repeatable reads, and Phantom reads are transactional, that is, what happens when concurrency executes.

5.3 Dirty reads:          

Dirty Read is also called invalid data read out. A transaction that reads another transaction that has not yet committed data is called dirty read.

For example, transaction T1 modifies a row of data but has not yet committed, at which point the transaction T2 reads the data modified by the transaction T1, and after the transaction T1 for some reason rollback, the data that the transaction T2 reads is dirty.

Workaround: Adjust the transaction isolation level of the database to read_committed

5.4 Non-repeatable reads:          

Non-repeatable reads refer to two identical queries returning different results within the same transaction.

For example: Transaction T1 reads a data, transaction T2 reads and modifies the data, and T1 reads the data again in order to verify the read value, it gets different results.

Workaround: Adjust the transaction isolation level of the database to Repeatable_read

5.5 Phantom reads:

For example, system administrator a changes the scores of all students in the database from the specific score to the ABCDE level, but system Administrator B inserts a record of a specific score at this time, and when system administrator a changes to the end, it turns out that there's a record that hasn't changed, as if there was a hallucination. Sample. This is called Phantom Reading.

Workaround: Adjust the transaction isolation level of the database to Serializable_read

Isolation level of the database