Mysql lock mechanism Summary

1. isolation level

(1) Read uncommitted (RU)

(2) Read committed (RC)

At the RC isolation level, each statement reads the updates of committed transactions. If other transactions are committed between two queries, the results of the two queries are inconsistent. However, the read/write isolation level is widely used in the production environment.

(3) Repeatable Read, RR)

How does mysql implement the Repeatable read isolation level.

(4) Serializable)

Dba will make a trade-off between concurrency and meeting business needs, and select an appropriate isolation level.

2. Concurrent Scheduling

(MVCC) compared with the traditional lock-based concurrency control, the main feature is that the read is not locked. 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

4. deadlock

5. Lock type

For mysql, the transaction mechanism is mostly implemented by the underlying storage engine. Therefore, the mysql layer only has 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.

1. (The read is not locked). 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 equivalent to the write lock on the table.
3. Innodb also locks data records (clustered indexes) with row locks.
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)

(Y indicates compatibility, n indicates incompatibility ):

7. mysql lock-related operations

(1). view the last deadlock Information

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

8. Typical SQL statement lock analysis

: Blog has an article "MySQL lock Processing Analysis" on mysql lock write very detailed, you can study well, the link address: http://hedengcheng.com /? P = 771