Isolation level of transaction concurrency

Transaction

A transaction is a series of operations performed as a single logical unit of work. A logical unit of work must have four properties, called atomicity, consistency, isolation, and persistence (ACID) properties, to be a transaction.

transactional concurrency

A database is shared by multiple users (transactions), which in this case is called concurrency when multiple users access the data at the same time.

Issues that may occur under transaction concurrency are missing updates

两个事务都同时更新一行数据，一个事务对数据的更新把另一个事务对数据的更新覆盖了。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。

Dirty Read

一个事务读取到了另一个事务未提交的数据操作结果。这是相当危险的，因为很可能所有的操作都被回滚。

Non-REPEATABLE READ

一个事务对同一行数据重复读取两次，但是却得到了不同的结果。

Phantom reading

幻读发生在当两个完全相同的查询执行时，第二次查询所返回的结果集跟第一个查询不相同。

Isolation level READ UNCOMMITTED for transaction

当前事务可以读取另一个事务未提交的记录，非常危险，因为另一个事务可能完全回滚！

Read Committed

保证一个事务修改的数据提交后才能被另外一个事务读取。

REPEATABLE READ

这种事务隔离级别可以防止脏读，不可重复读。但是可能出现幻读!

Serialization of

事务被处理为顺序执行。除了防止脏读，不可重复读外，还避免了幻像读。

lbcc-Isolation LevelRead not submitted

Without any locks.

Read Committed

Read Data plus shared lock
Change data plus exclusive locks
Share Lock Read Immediate release

REPEATABLE READ

Read Data plus shared lock
Change data plus exclusive locks
Shared lock transaction ends before release

Serialization of

Read Data plus shared lock
Change data plus exclusive locks
Shared lock transaction ends before release
Increase range Lock

What the hell? REPEATABLE Read and read submissions two isolation levels the conclusions are inconsistent with the LBCC protocol!!! The suspicion of life ...

MVCC protocol mvcc– Multi-version concurrency control protocol

This is achieved by saving the data at a point-in-time snapshot, that is, the data that each transaction sees is consistent no matter how long it takes to execute. Different storage engine MVCC implementations are different, typically with optimistic concurrency control and pessimistic concurrency control.

MVCC implementation principle (InnoDB engine)

The InnoDB engine will add two hidden columns per row, representing the row to create and delete versions, the version number of the transaction to be created, and the version number to be automatically incremented each time a new transaction is started.

Give me a chestnut and steal a few pictures ...

Initializing data

Transaction 1

Transaction 2

Read Committed

读事务每次都读取undo log中最近的版本，因此两次对同一字段的读可能读到不同的数据（幻读），但能保证每次都读到最新的数据

REPEATABLE READ

Reads the specified version each time so that no phantom reads are generated, but the latest data may not be read

Summarize

1. For many SQL, the higher the isolation level, the tighter the lock that InnoDB adds to the recordset, the higher the likelihood of a lock collision, and the greater the impact on the concurrency transaction's rationality.

2. So in our project, we should try to use a lower isolation level to reduce the chance of lock contention.

3. The InnoDB engine uses indexes as much as possible to prevent lock tables.
4. The probability of a small transaction having a lock conflict is also smaller if the transaction size is reasonably used.
5. Use equal conditions as much as possible to avoid the effect of next-key locks on concurrent insertions (Gap Lock)

Reference

1. High performance MySQL
2. In layman's MySQL

Isolation level of transaction concurrency