MySQL Lock mechanism usage summary

1. Isolation level

(1) Read not submitted (read uncommited,RU)

At this isolation level, transactions are completely non-isolated, resulting in dirty reads that can read UNCOMMITTED records and will not be used in real-world situations.

(2) Read commit (read commited,RC)

can only read to committed records, this isolation level, there will be a phantom read phenomenon, so-called Phantom Read refers to the same transaction, multiple executions of the same query, the return of the record is not exactly the same phenomenon. The root cause of phantom reads is that, at the RC isolation level, each statement reads the update of the committed transaction, and if there are other transaction commits between the two queries, the results of two queries are inconsistent. Nonetheless, read-commit isolation levels are widely used in production environments.

(3) Repeatable read (Repeatable read, RR)

The REPEATABLE read isolation level solves the problem of non-repeatable reads, but still does not solve the problem of phantom reading. So what's the difference between a non-repeatable read and a phantom reading? Non-repeatable reading the emphasis is on modification, that is, reading the data, the value of two reads is not the same, while Phantom Reading focuses on the number of records change "insert and delete." The general textbook tells us that only to the serialization isolation level to solve the phantom reading problem, but MySQL InnoDB more special, RR that solves the Phantom reading problem, mainly through the gap lock implementation. In addition, not all databases implement this isolation level, which is followed by a brief description of how MySQL implements the REPEATABLE read isolation level.

(4) serialization (Serializable)

In the serialization isolation mode, the dirty read, illusion is eliminated, but the transaction concurrency decreases sharply, the transaction isolation level is inversely proportional to the concurrency of the transaction, the higher the isolation level, the lower the concurrency of the transaction. In a real-world production environment,DBAs make tradeoffs between concurrency and meeting business needs, choosing the right isolation level.

2. Concurrency Dispatch mode

Another thing that is closely related to the isolation level is concurrent scheduling, which implements the isolation level through concurrent scheduling. For concurrent scheduling, different database vendors have different implementation mechanisms, but the basic principle is similar, all by locking to protect the data object is not simultaneously modified by multiple transactions. Multiple versions of concurrency control (MVCC) compared to the traditional lock-based concurrency control is the main feature of the read is not locked, this feature for the read and write less scenes, greatly improving the concurrency of the system, so most of the relational database has been implemented MVCC.

3. Two Phase Lock protocol

The meaning of the two-phase lock protocol is that the transaction is divided into two phases, the first phase being a blockade, and the second phase releasing the blockade. The two-stage blockade guarantees the correctness of concurrent scheduling. The two-phase blockade increases concurrency, but it also leads to deadlocks, as opposed to a one-phase blockade (all locks are acquired at once for transactions).

4. Deadlock

The so-called deadlock refers to two or more transactions, each occupying each other's desired resources, the formation of a cycle of waiting, one another can not continue to execute a state.

5. Lock type

Depending on the type of lock, it can be divided into shared lock, exclusive lock, intent shared lock and intent exclusive lock. According to the size of the lock, and can be divided into row lock, table lock. For MySQL, the transaction mechanism is more on the bottom of the storage engine to implement, so theMySQL plane is only table lock, and the support transaction INNODB storage engine implemented a row lock (record lock ),Gap Lock, Next-key lock. The record lock of MySQL is the lock of index record, because InnoDB is the index organization table;Gap Lock is the lock of index record gap, which is only valid at RR isolation level;Next-key Lock is a record lock plus record A combination of gap locks. MySQL implements RR isolation levels through gap locks and Next-key locks .

Description

1. For update operations (read not locked ), only the index can be the uplink lock, otherwise, the clustered index on each row of the write lock, the actual equivalent to write locks on the table.
2. If multiple physical records correspond to the same index, there will be a lock conflict if they are accessed simultaneously.
3. When a table has multiple indexes, different transactions can lock different rows with different indexes, plus InnoDB locks the data records (clustered indexes) with row locks .
4. MVCC concurrency control mechanism, no operation will block the read operation, the read operation will not block any operation, only because the read is not locked.

6. lock mutex and compatibility relationship matrix

	X (exclusive lock )	S (Shared lock )	IX (Intent exclusive lock )	Is (intent shared lock )
X	N	N	N	N
S	N	Y	N	Y
Ix	N	N	Y	Y
Is	N	Y	Y	Y

(Y means compatible andn is incompatible ):

7.mysql Lock related Operations

(1). View last deadlock information

Show InnoDB engine status;

View information about the LATEST detected deadlock column.

(2) View the lock being used

SELECT r.trx_id waiting_trx_id,
R.trx_query Waiting_query,
b.trx_id blocking_trx_id,
B.trx_query Blocking_query,
b.trx_mysql_thread_id Blocking_thread,
B.trx_started,
b.trx_wait_started
From Information_schema.innodb_lock_waits W
INNER JOIN Information_schema.innodb_trx b
On b.trx_id = w.blocking_trx_id
INNER JOIN Information_schema.innodb_trx R
On r.trx_id = w.requesting_trx_id

(3) View the isolation level of a transaction

Show variables like ' tx_isolation ';

8. Typical SQL statement lock analysis

(1). Select ... lock in share mode gets the shared lock. "For the intent shared lock on the table, for each row read, the uplink-level shared lock"
(2). Select ... for update obtains an exclusive lock "for an intent exclusive lock on a table, and an uplink-level exclusive lock for each row read"
(3). INSERT INTO Target_tab select * from Source_tab where ...
(4). CREATE TABLE New_tab as SELECT ... From Source_tab where ...
(3) and (4) under the RR isolation level, the source_tab is locked to prevent phantom reading; The RC isolation level is not locked.
(5). FLUSH TABLES with READ LOCK
The global read lock locks all the tables in all libraries in the database, and mysqldump uses this command.

MySQL Lock mechanism usage summary