MySQL transactions and lock mechanisms

MySQL transactions and lock mechanisms

The characteristic ACID of a transaction, that is, atomicity, consistency, isolation, and durability.

Atomicity ensures that a transaction is a minimum unit, which is inseparable from each other;

Consistency ensures that each operation thread in the transaction cannot be submitted separately. If the operation is successful, the transaction is committed together. If the operation fails, the transaction is rolled back;

Isolation ensures that the data views seen by different transactions are isolated from each other (isolation level can be set );

Durability ensures that the data is permanently saved after the transaction is committed;

The isolation level of transactions defined in the SQL specification:

1. read uncommitted (read uncommitted content)

All transactions can see the execution results of uncommitted transactions. This isolation level is rarely used in actual applications to read uncommitted data, also known as "Dirty read ".

2. read committed (READ submitted content)

This is the default isolation level for most databases, but not for mysql. At the beginning of a transaction, you can only see the changes made by the committed transaction. Any changes made to a transaction from the beginning to before the commit are invisible unless committed. This isolation level is also known as non-repeated read.

3. repeatable read (repeatable read)

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

4. SERIALIZABLE (SERIALIZABLE)

Serializable is the highest isolation level. It forces transaction sorting to make it unrepeatable, solving the phantom read problem. This isolation level adds a shared lock to each read data row. Using this isolation level produces a lot of timeout, which is not used in actual development.

Mysql locking mechanism:

The types can be divided into shared lock and exclusive lock, read lock, and write lock ).

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

Mysql provides three transaction-type storage engines: InnDB, NDB Cluster, and Falcon.

Locks can be obtained in any process of a transaction execution, but these locks are released only when the transaction is committed or rolled back. These are both implicit locks or explicit locks. InnoDB supports explicit locks, for example:

SELECT... lock in share mode (add share lock)

SELECT... for update (apply exclusive lock)

Multi-version concurrency control (important ):

Mysql's transaction storage engine is not a simple and practical row lock mechanism, but a multi-version concurrency control (MVCC) technology. It is associated with the row lock mechanism for practical purposes. In order to cope with higher concurrency, of course, the cost of performance consumption.

Each storage engine has different MVCC implementation methods. The simple implementation of the InnoDB engine is as follows:

InnoDB is implemented by adding two implicit values for each data flight. The two implicit values record the row creation time and expiration time. The system version number when each row stores events. Each time a new transaction starts, the version number is automatically added with 1, and each transaction stores the version number at the start. Each query returns the query result based on the transaction version number.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: