MySQL_InnoDB transaction and lock details

Question: Why is transaction introduced?

1>. Data Integrity

2>. Data Security

3>. Make full use of system resources to improve the system's concurrent processing capability

1. Transaction Features

Transactions have four features: Atomiocity, Consistency, and Isolation)

And Durability (ACID.

1.1 atomicity

A transaction is the logical unit of work of a database. All operations in a transaction are either done or not done.

1.2 consistency

The result of transaction execution must be that the database changes from a consistent state to another consistent state.

1.3 isolation

The execution of a transaction cannot be disturbed by other transactions. That is, the internal operations of a transaction and the data used for other

Transactions are isolated, and the transactions executed concurrently do not interfere with each other.

1.4 durability

Once a transaction is successfully committed, modifications to data in the database are persistent. Other

Operations or faults should not affect the execution results.

2. Transactions and locks in the InnoDB Engine of MySQL

2.1 SELECT ...... LOCK IN SHARE MODE

Add a shared lock to the data searched in the session transaction. If the data searched in the session transaction has been exclusive locked by other session transactions, the shared lock will wait for the end of the transaction to be added. If the wait time is too long, the lock wait timeout required by the transaction will be displayed.

2.2 SELECT ..... FOR UPDATE

When a read update is added to the data searched in a session transaction, other session transactions cannot be added with other locks and must wait until the end of the transaction.

2.3 INSERT, UPDATE, DELETE

The session transaction adds an exclusive lock to the data operated by the DML statement, and the transactions of other sessions will wait for it to release the exclusive lock.

2.4 gap and next key lock)

The InnoDB engine automatically adds a gap lock (or range lock) to the shared lock, update lock, and exclusive lock in session transactions when they need to be added to a range value domain ), lock non-existing data to prevent phantom writing.

Note:

The situations described in 2.1, 2.2, 2.3, and 2.4 are also related to the transaction isolation level set by MySQL.

3. Four transaction isolation Modes

3.1 READ UNCOMMITED

Dirty reads are allowed during the SELECT statement, that is, the SELECT statement reads data modified by other transactions but not committed.

3.2 READ COMMITED

The SELECT statement cannot be read repeatedly, that is, the same query statement is executed twice in the same transaction, when the data queried by other transactions is modified and committed, the data read twice is inconsistent.

3.3 REPEATABLE READ

The SELECT statement can be read repeatedly, that is, the same query statement is executed twice in the same transaction, and the data obtained is always consistent.

3.4 SERIALIZABLE

The only difference from repeatable reads is that the normal SELECT statement is changed to SELECT… by default .... Lock in share mode. That is, it adds sharing to the data involved in the query statement, blocking other transactions from modifying real data.

1 2 Next Page