Hello, no see for a week, how have you been this week?

Recently learning MySQL related knowledge, just learned this knowledge point, sorted out and everyone to learn together.

What is a transaction

A transaction is a set of atomic SQL queries, or a single unit of work. — High Performance MySQL

All SQL statements contained in a transaction either execute successfully or fail. The classic example is a bank transfer, which is already out of date.

Four Properties of Transactions (ACID)

  • A transaction must be viewed as an indivisible, minimal unit of work, in which all operations either succeed or fail. It is impossible to execute only some of them.

  • Consistency A database always transitions from one consistent state to another consistent state. Take transfer as an example, A transfers money to B, B receives the money, there is no such situation as A transfers money to B, B does not receive the money.

  • Isolation Typically, changes made by one transaction are not visible to other transactions until they are finally committed. Again, in the case of transfer, A transfers money to B, and if B doesn’t receive the money, in fact, A doesn’t withhold the money.

  • Durability Once a transaction is committed, their modifications are permanently saved in the database.

Transaction isolation level

  • Changes made in Read uncommitted transactions are visible to other transactions even if they are not committed. Transactions can read uncommitted data, also known as dirty reads. This level has a lot of problems and is rarely used in combat.

  • When a transaction starts, only the changes made by the committed transactions can be “seen”. That is, any changes made by a transaction are not visible to other transactions from the time it starts until it commits. Sometimes it is also unrepeatable because executing the same query twice may result in different results. This is the default isolation level for most databases.

  • Repeatable Read This level ensures that the same record is read repeatedly in the same transaction with consistent results. It solves the dirty read problem, but phantom read problem exists. This is the default isolation level for MySQL.

  • Serializable This is the highest isolation level for a database and avoids phantom reads by forcing transactions to be executed sequentially. In short, Serializable locks every row of data that is read, so it can cause a lot of timeouts and lock contention. This isolation level is rarely used in practice.

Put it all together in a table.

Isolation level Dirty read Unrepeatable read Phantom read
Read uncommitted

(Not submitted for reading)		 Yes Yes Yes
Read committed

(Submit read)		 No Yes Yes
Repeatable read

(Repeatable)		 No No Yes
Serializable

(Serializable)		 No No No
conclusion

It’s all concurrency, not so much for a single thread.

Welcome to pay attention to the public number: Charon, share Java knowledge, we make progress together.