MySQL transaction plus lock mechanism

The characteristics of a transaction are acid, i.e. atomicity, consistency, isolation, persistence.

Atomicity guarantees a transaction as a minimum unit, which is indivisible internally;

The consistency guarantees that each operation thread in the transaction cannot be submitted separately, the success is committed together, and the transaction is rolled back;

Isolation ensures that views of the data between different transactions are independent and isolated from each other (isolation level can be set);

Persistence guarantees that the data will persist after the transaction is committed;

The isolation level of the transaction defined by the SQL specification:

1.READ UNCOMMITTED (Read UNCOMMITTED content)

All transactions can see the execution results of uncommitted transactions, which are seldom used in the actual application and read uncommitted data, also known as dirty reads.

2.READ COMMITTED (Read submissions)

The default isolation level for most databases is this level, but not the default for MySQL. A transaction can only be seen at the beginning of changes that have been submitted to the firm. Any changes made by a transaction from the beginning to the commit are not visible unless submitted. This isolation level is also known as non-repeatable reading.

3.REPEATABLE Read (Repeatable Read)

Locks all data used in the query to prevent other users from updating the data, but other users can insert new Phantom rows into the dataset, and phantom rows are included in subsequent reads of the current transaction. This level is also known as "phantom Reading."

4.SERIALIZABLE (Serializable)

Serializable is the highest level of isolation, and it solves the problem of phantom reading by forcing the transaction to sort and make it non-stressed. This isolation level adds a shared lock to each read row of data, and using this isolation level creates a large number of timeouts that are not normally used in real-world development.

MySQL plus lock mechanism:

Depending on the type can be divided into shared lock and exclusive (EXCLUSIVE lock) or call read lock (read lock) and write lock (write lock).

Divide table and row locks according to granularity. Table locks are implemented by the database server, and row locks are implemented by the storage engine.

MySQL offers 3 transactional storage engines, INNDB, NDB cluster, and Falcon.

Locks can be obtained in any procedure performed by a transaction, but only when the transaction commits or rolls back. These are either implicit or explicitly locked, and InnoDB supports explicit locking, for example:

SELECT .... Lock in SHARE MODE (plus shared lock)

SELECT ..... For UPDATE (plus exclusive lock)

Multi-version concurrency control (important):

MySQL's transaction storage engine is not a simple and practical locking mechanism, but is called the multi-version concurrency control (MVCC) technology, and the row Locking Mechanism Association utility. To cope with higher concurrency, of course, at the expense of performance.

Each storage engine implements a different approach to MVCC, and the InnoDB engine is implemented in a simple way as follows:

The InnoDB is implemented by adding two hidden values to each data navigation. These two implied values record the time the row was created and the expiration time. Each row stores the system version number at the time the event occurred. Each time a new transaction is started, the version number is automatically added 1, and each transaction saves the version number at the beginning, and each query queries the results based on the version number of the transaction.

• This article is from: Linux Tutorial Network

MySQL transaction plus lock mechanism