Mysql lock mechanism usage summary, mysql Lock Mechanism

Mysql lock mechanism usage summary, mysql Lock Mechanism

1. isolation level

(1) Read uncommitted (RU)

At this isolation level, transactions are completely isolated and dirty reads are generated. uncommitted records can be read, which is not used in actual cases.

(2) Read committed (RC)

Only committed records can be read. At this isolation level, phantom read occurs. Phantom read refers to executing the same query multiple times in the same transaction, the returned records are not exactly the same. The root cause of phantom read is that at the RC isolation level, each statement reads the updates of committed transactions. If there are other transactions committed between two queries, the two query results are inconsistent. However, the read/write isolation level is widely used in the production environment.

(3) Repeatable Read, RR)

The Repeatable read isolation level solves the problem of non-repeatable read, but it still does not solve the problem of phantom read. What is the difference between non-repeated reading and phantom reading? The Repeatable read focuses on the modification, that is, the read data. The two reads have different values. Phantom reads focus on the change in the number of records [insert and delete ]. Generally, textbooks tell us that phantom reading can be solved only at the serial isolation level, but mysql's innodb is quite special. RR solves the phantom reading problem, mainly through the GAP lock. In addition, not all databases have implemented this isolation level. We will briefly introduce how mysql achieves the Repeatable read isolation level.

(4) Serializable)

In the serialized Isolation Mode, dirty reads are eliminated, but the transaction concurrency decreases sharply. The transaction isolation level is inversely proportional to the transaction concurrency level. The higher the isolation level, the lower the transaction concurrency. In the actual production environment, dba will make a trade-off between concurrency and business requirements, and select an appropriate isolation level.

2. Concurrent Scheduling

Another thing closely related to the isolation level is concurrency scheduling, which achieves isolation level through concurrency scheduling. For concurrent scheduling, different database vendors have different implementation mechanisms, but the basic principle is similar. locks are used to protect data objects from being modified by multiple transactions at the same time. Compared with the traditional lock-based concurrency control, multi-version concurrency control (MVCC) is mainly characterized by read locks. This feature greatly improves the concurrency of the system for scenarios with fewer reads and writes, therefore, most relational databases implement MVCC.

3. Two-phase lock protocol

The two-phase lock protocol means that transactions are divided into two phases. The first stage is to obtain the lock, and the second stage is to release the lock. Two-phase blocking ensures the correctness of concurrent scheduling. Compared with one-stage blocking (one-time acquisition of all the locks required by the transaction), the two-phase blocking improves the concurrency, but also brings about the possibility of deadlocks.

4. deadlock

A deadlock refers to a State in which two or more transactions occupy the resources that the other party expects to obtain, form a cyclic wait, and cannot continue to execute each other.

5. Lock type

Based on the lock type, the locks can be divided into shared locks, exclusive locks, intention shared locks, and intention exclusive locks. Based on the lock granularity, it can also be divided into row locks and table locks. For mysql, the transaction mechanism is mostly implemented by the underlying storage engine. Therefore, mysql only supports table locks, the innodb Storage engine that supports transactions implements row locks (record locks), gap locks, and next-key locks. Mysql record locks are essentially the index record locks, because innodb is the index organization table; gap locks are the gap locks of index records, which are valid only at the RR isolation level; the next-key lock is a combination of the record lock and the gap lock before the record. Mysql uses the gap lock and next-key lock to implement the RR isolation level.

Note:

1. For update operations (read locks), only the index can be used for the upstream lock; otherwise, the lock will be written on each row of the clustered index, which is actually equivalent to the write lock on the table.
2. If multiple physical records correspond to the same index, a lock conflict will also occur if they are simultaneously accessed;
3. When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, innodb also uses row locks to lock data records (clustered indexes.
4. In the MVCC concurrency control mechanism, any operation will not block the read operation, and the read operation will not block any operation because the read is not locked.

6. Lock mutex and Compatibility Matrix

	X (exclusive lock)	S (shared lock)	IX (intention exclusive lock)	IS (intention share lock)
X	N	N	N	N
S	N	Y	N	Y
IX	N	N	Y	Y
IS	N	Y	Y	Y

(Y indicates compatibility, n indicates incompatibility ):

7. mysql lock-related operations

(1). view the last deadlock Information

Show innodb engine status;

View information about latest detected deadlock.

(2) view the lock in use

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 transaction isolation level

Show variables like 'tx _ isolation ';

8. Typical SQL statement lock analysis

(1). select... lock in share mode to obtain the shared lock. [Intention-sharing locks for tables; uplink-level sharing locks for each row read]
(2). select... for update to obtain the exclusive lock [intention exclusive lock for the table; row-level exclusive lock will apply to 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) at the RR isolation level, the source_tab will be locked to prevent phantom reading. At the RC isolation level, the source_tab will not be locked.
(5). FLUSH TABLES WITH READ LOCK
Global read lock: locks all tables in all databases in the database. This command is used by mysqldump.