Four features of database things

ACID, short for the four Essential elements of proper database transaction execution. They include: Atomicity, Consistency, Isolation, Durability. A database that supports Transaction must have these four characteristics; otherwise, the correctness of data cannot be guaranteed during Transaction processing, and the Transaction process may fail to meet the requirements of the Transaction party.

Atomicity (Atomicity)

The first one, atomicity, this is the easiest one. It says that all the operations in a thing together form an atomic package, and either they all succeed or they all fail. This is a basic feature that guarantees that the database will fail or go down for some other reason.

Consistency (Consistency)

Second, consistency, and this is the most misunderstood one, a lot of blogs like to use the bank transfer example of uniformity, which is based on atomicity.

Atomicity only guarantees that all operations within a thing are identical, that everyone is alive and dead, that you’re dead and I’m alive. But atomicity does not guarantee that we all live and die together at the same time. Computer instructions are sequenced, and that determines that the submission of an item will go through a time process, so if I read the database in the middle of the submission, will I read the intermediate result?

In order to prevent such a situation, the consistency of database things stipulates that before and after the submission of things, only the state before and after the submission of things can exist forever, from one consistent state to another consistent state, but it is impossible to appear in the middle process state. That is, the result of the execution of things is a quantized state, not a linear state.

There is a process for a database to commit an object. If there is a time difference between the first second of the commit and the third second of the commit, a deletion process is not completed until the third second of the commit, will the first second access person and the third second access person get different results? There is inconsistency, state of chaos? That’s why consistency is guaranteed: there’s only going to be a front state and a back state, there’s never going to be an intermediate state.

Isolation,

The isolation of things is based on atomicity and consistency, because things are atomized and quantized, so things can be executed concurrently in the form of multiple atomic packages, but each thing does not interfere with each other.

However, since multiple things may be operating on the same resource, different things in order to ensure isolation, there are many locking schemes, and of course this is the implementation of the database, how they do it, we don’t have to go into that.

persistence

Persistence, when an item is submitted, the database state changes forever, as long as the item is submitted, even if it crashes after the submission, it is actually submitted, it does not appear because

It’s like a tattoo that can’t be removed. It’s permanently fixed unless you destroy your hard drive.

Transaction Isolation level (default transaction level is repeatable)

There are only two kinds of database things: reading things and modifying things. In the absence of transaction isolation control, multiple transactions operating on the same data at the same time may affect the desired results. There are generally four situations:

• When two update transactions modify a single data at the same time, this is obviously the most serious situation, and should not happen in the program anyway, because it will cause the loss of updates!
• Dirty data is read when one update transaction updates a piece of data while another read transaction reads an update that has not yet been committed.
• An unrepeatable read occurs when a read transaction reads a piece of data and another update transaction modifies the data.
• When one read transaction reads, another insert transaction inserts a new data, so it is possible to read one more data, resulting in phantom reads.

The first three are of the same data of concurrent operation, the result of the program may have fatal effects, especially the real-time, such as financial, high accuracy requirements of system will not allow the emergence of the 3 cases, compared with the fourth kind of situation will not affect the authenticity of the data, in many cases is allowed, such as social BBS system of real-time demand is not high! To sum up the four situations above, we can roughly understand them as follows (originally, the free combination of two kinds of transactions 2*2=4) :

• Allow changes while changing (lose updates)
• Allow read (dirty read) when modified
• Allow modification while reading (non-repeatable read)
• The problem of allowing insert (phantom read) from top to bottom at read time becomes less serious, but the required performance overhead becomes larger. Because different systems allow different levels of conditions, transaction isolation emerged to allow us to set the concurrent behavior of the database.

To summarize the problems that can occur if transaction isolation is not considered:

Dirty read

A dirty read is one that reads data from another uncommitted transaction during a transaction. When a transaction is modifying data multiple times and the changes have not yet been committed, a concurrent transaction accessing the data will result in inconsistencies between the two transactions. For example, if user A transfers 100 YUAN to user B, run the following SQL command: update account set money=money+100 where name= ‘B’; Update account set money= money-100 where name= ‘A’; When only the first SQL is executed, A notifies B to look at the account and B finds that the money has indeed been transferred to the account (A dirty read occurs). However, regardless of whether the second SQL is executed or not, as long as the transaction is not committed, all operations will be rolled back and when B looks at the account again later, the money has not actually been transferred.

Unrepeatable read

Non-repeatable reads are those that return different values from multiple queries within a transaction scope for a particular data in the database because it was modified and committed by another transaction during the query interval. For example, when transaction T1 reads some data, and transaction T2 immediately modifies the data and commits the transaction to the database, transaction T1 reads the data again and gets a different result, and an unrepeatable read occurs. The difference between an unrepeatable read and a dirty read is that a dirty read reads uncommitted dirty data from another transaction, while an unrepeatable read reads data from a previous transaction. In some cases, unrepeatable reads are not a problem, such as when we query the data multiple times and the result of the last query is the main result. But in other cases, problems may occur. For example, for the same data, A and B may be queried in different order, and A and B may fight…

Virtual reading (phantom reading)

Phantom reading is a phenomenon that occurs when a transaction does not execute independently. For example, transaction T1 changes an item from “1” to “2” in all rows of a table, and then transaction T2 inserts a row entry into the table with the value of “1” and commits it to the database. If a user of transaction T1 looks at the newly modified data, he or she will find that there is still a row that has not been modified. In fact, this row was added from transaction T2 as if it had been hallucinated. Unreal and unrepeatable reads both read another transaction that has already been committed (dirty reads are different). The difference is that unrepeatable reads query for the same data item, whereas unreal reads are for a batch of data as a whole (such as the number of data).

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

Four levels of isolation for database transactions

• Read Uncommitted
• Read Committed
• Repeatable Read (Repeatable Read)
• Serializable

The highest isolation level is Serializable and the lowest is Read Uncommitted. Of course, the higher the isolation level, the lower the execution efficiency. A level like Serializable locks a table (similar to Java multithreading locks) so that other threads can only wait outside the lock, so the isolation level you choose should depend on the actual situation. The default isolation level in the MySQL database is Repeatable Read.

In MySQL database, the above four isolation levels are supported. Repeatable Read is the default; In Oracle databases, only the Serializable level and Read COMMITTED level are supported. The default level is Read COMMITTED.

Select @@tx_ISOLATION;

Set the level of things in mysql:

set[glogal | session] transaction isolation level isolation level name;setTx_isolation = 'Isolation level name; 'Copy the code

Always set the isolation level of a thing before opening it

Read Uncommitted

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 its performance is not much better than other levels. Reading uncommitted data, also known as Dirty reads;

Read Committed

This is the default isolation level for most database systems (but not for MySQL). It satisfies a simple definition of isolation: a transaction can only see the changes made by committed transactions. This isolation level also supports what is called Nonrepeatable Read, because other instances of the same transaction may have new COMMITS in the process of that instance, so the same SELECT may return different results;

Repeatable Read (Repeatable Read)

This is MySQL’s default transaction isolation level and ensures that multiple instances of the same transaction will see the same rows when they concurrently read data. In theory, though, this leads to another thorny problem: Phantom Read. Simply put, phantom reading refers to when a user reads a row in a certain range, another transaction inserts a new row in that range, and when the user reads a row in that range, a new phantom row is found. InnoDB and Falcon storage engines address this problem through the Multiversion Concurrency Control (MVCC) mechanism

Serializable

This is the highest isolation level, and it solves the phantom problem by forcing transactions to be ordered so that they cannot conflict with each other. In short, it places a shared lock on each read row. At this level, a lot of timeouts and lock contention can result. The four isolation levels are implemented with different lock types, which can be problematic if the same data is read. Such as:

Drity Read: a transaction has updated a copy of data, and another transaction has Read the same copy of data. For some reason, the first transaction has rolled back, and the data Read by the second transaction is incorrect.

Non-repeatable read: Data inconsistency between two queries of a transaction. This may be because the original data updated by a transaction was inserted between the two queries.

Phantom Read: a transaction where the number of pens is inconsistent between two queries. For example, if one transaction queries for rows and another inserts new columns, the previous transaction will find columns that it did not have before on subsequent queries.