Database transactions concurrency look at that.

Read two days Java source code, a little confused, today turned a wave of interview exchange paste, alas, look at the big companies out of those who fear you through the interview problem, really feel their future a dim, dim, dim, dark ...

But, Xi or to learn, from the face of the question to see the database concurrency problem, before class clearly learned the principles of the database, but look at the topic of the brain is still a blank, blank, blank, white ... Both are learning not to summarize the pits between

So, come on, learn again, and summarize at the same time. A good memory is better than a rotten pen head!

Four characteristics of database transactions (ACID)

atomicity (atomicity): refers to all the operations contained in a transaction, either wholly or completely.

Consistency (consistency): refers to a database moving from one consistent state to another consistent state, containing only committed transactions, if some transactions have not yet completed the forced interruption, and the transaction changes to the database has been written to the physical database, The database is in an incorrect state at this time. For example, a to B transfer, not a deduction of money, B but did not receive.

Isolation (Isolation): refers to the non-interference between the various concurrent transactions.

Persistence (Durability): means that once a transaction is committed, modifications to the database are persistent and can be resumed even if the database fails.

Problems caused by database transaction concurrency

Dirty read: Also called read dirty data, refers to the current transaction read the other transactions have not committed the modification. Dirty reads occur because a transaction is visible to other transactions before committing the changes to the database. A small chestnut: for example A and b at the same time to sell tickets, the current system only one ticket, at this time Nicholas went to a place to buy a ticket, a will be in the database of the remaining votes-1, at this time someone to buy a ticket B, b query database found now Wood has tickets, so tell the buyers no tickets. But! b after the buyer left, Nicholas suddenly did not want to buy, so a cancel the operation before, and submit the transaction, at this time, resulting in two no sell tickets. This is because B reads the data that is not submitted by a.

Non-repeatable read: Refers to a transaction two reads the result is not the same, this is due to the interval at two reads another transaction modifies the database and commits. The difference between non-repeatable and read dirty data is that dirty reads occur before a transaction commits, and non-repeatable reads occur after the transaction commits.

Phantom reads: Transactions are queried two times during the operation, and the results of the second query contain data that did not appear in the first query or missing data from the first query (the same SQL statement does not require two queries). This is due to the fact that another transaction was inserted into the data during the two queries. The difference between non-repeatable reads and Phantom reads is that the former is for a record, and the latter is for a batch of records.

These are some of the reading issues, and there are some update issues

Update loss issues

The first type of update loss issue: A and B update the database, a failure rollback to the origin point B to the database update to overwrite.

The second type of update is missing: A and B update the database, and a update to the database overrides the B update to the database.

For the problems caused by transaction concurrency and some examples can refer to 76445267

Four isolation levels for transactions

To ensure the isolation of transactions, the database provides four levels of isolation

Read UNCOMMITTED: One transaction does not allow other transactions to write while it is in the write operation, but allows read operations from other data. This level of isolation prevents updates from being lost, but can cause read dirty data

Read Committed: Uncommitted write transactions will prevent other transactions from accessing the row. One level of isolation can solve the problem of reading dirty data, but there are still non-repeatable read problems

Repeatable reads: Transactions that read data prohibit write operations on other transactions (avoid non-repeatable reads), but allow read transactions, and write transactions prohibit all other transactions (avoid dirty reads). There is still an issue with Phantom reading at this level

Serialization: Requires transaction serialization, which can only be executed one by one, and this level of isolation avoids dirty reads, non-repeatable reads, and Phantom read problems, but poor performance, generally rarely used

Testing the isolation level of the database reference https://www.jianshu.com/p/8d735db9c2c0 and https://www.jb51.net/article/116477.htm must be seen for ease of understanding.

View the isolation level of the current transaction in MySQL:

Select @ @tx_isolation;

Set the isolation level of the transaction in the MySQL database:

Set  [Glogal | session]  Transaction Isolation  Level isolation level name;
Or
set tx_isolation=' isolation level name; '

Well, first sauce,

Database transactions concurrency look at that.