mysql lock none_关于MySQL的锁和InnoDB Lock

原标题：关于MySQL的锁和InnoDB Lock

About lock in MySQL and InnoDB lock

I reside in the present.

实验环境

MySQL 5.7.21 on docker^1

主要内容

MySQL Lock

InnoDB Lock

Deadlock MySQL中四种类型的锁

Table, 表锁

Row, 表锁

Page, 页锁

Matedata, 元数据锁

其中，page lock only in BDB storage engine. Metadata Lock

检查锁状态 mysql -uroot

-e

"show processlist\G" | tee processlist.log mysql -uroot

-e

"show engine innodb status\G" | tee innodb-status.log

关键参数

锁超时， lock_wait_timeout，默认值31536000s(1year) InnoDB Lock

两个特点 支持事务 行级锁

四个问题 更新丢失, Lost Update 脏读, Dirty Reads 不可重复读, Non-Repeatable Reads 幻读, Phantom Reads

两个级别 InnoDB Row Lock, InnoDB行锁 InnoDB Table Lock, InnoDB表锁

四种实现形式 共享锁 排他锁 意向共享锁 意向排他锁 检查InnoDB Lock状态 02:25:53 (root@localhost) [(none)]> show status like 'innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | +-------------------------------+-------+ 5 rows in set (0.00 sec) select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G select * from information_schema.innodb_trx\G -- processlist SHOW FULL PROCESSLIST; InnoDB Row Lock

InnoDB实现的两种类型行锁

S Lock (Shared lock, 共享锁)

X Lock (Exclusive lock, 排他锁)

一般来说，读写操作的锁不同。读锁(或叫共享锁)允许并发线程读取加锁的数据，但禁止写数据。相反，写锁(或叫排他锁)阻止其他线程的读写操作。

InnoDB行锁的三种算法

Record lock

Gap lock

Next-key lock：Record Lock + Gap Lock, 锁定一个范围，并且锁定记录本身

如果不通过索引条件检索数据，那么InnDB将对表中的所有记录加锁，实际效果跟表锁一样！

SQL优化或是检查锁的时候，还需要注意表数据量和相关SQL语句的索引(explain)使用状况。

对于键值在条件范围内但不存在的记录，叫“间隙(GAP)”。 InnoDB Table Lock

InnoDB内部使用的两种表级意向锁(Intention Lock)

IS, Intention Share Lock, 意向共享锁

IX, Intention Exclusive Lock, 意向排他锁 Reference

https://bugs.mysql.com/bug.php?id=989

MySQL排错指南 Chapter 2

深入浅出MySQL Chapter 20 锁问题

深入浅出MySQL Chapter 20.3.9 关于死锁

https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlock-detection.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

https://shawn0915.github.io/mysql/2018/02/20/mysql-lock.html Footnotes

责任编辑：