mysql锁机制
锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。
innodb_lock_wait_timeout：锁的等待时间（默认50秒）
表锁
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。
LOCK TABLE XXX;
UNLOCK TABLES;
-- 行锁语法(S锁)
select ... LOCK IN SHARE MODE
注：MyISAM锁粒度到表级别，InnoDb到行级别
- 表共享读锁（Table Read Lock）
不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作（阻塞）。
举例子：session1（简称s1）给表A加读锁
s1能读取表A，不能读取表B，且不能更新表A（会报错）
s2能读取表A，能读取表B，更新表A会阻塞（等读锁解除才会执行） - 表独占写锁（Table WriteLock）
会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
举例子：session1给表A加写锁
s1能读取和更新表A，不能读取表B（会报错）
s2不能对表A进行读取和更新（会阻塞），对表B的所有操作均正常。
行锁
Innodb对页进行管理 + 位图 的方式实现粒度到行级别的锁
mysql不存在锁升级（Sql Server在查询同一对象数据量达到5000会从行锁升级到页锁）
- 共享锁（S Lock）
- 排他锁（X Lock）
意向锁
意向锁是一种不与行级锁冲突表级锁。
- 意向共享锁（IS Lock）
- 意向排他锁（IX Lock）
作用：
如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。
说人话：
如果需要对页上的记录r进行上X锁，则需要分别对数据库A、表、页上意向锁IX，最后对记录r上x锁。
意义在于如果此时对该表进行DDL，需要产生表X锁，此时表上有IX锁，与表X锁互斥，阻塞等待。无需进入到每一页中扫描是否有行X锁。（此处只是举例子，还有其他兼容互斥情况）
间隙锁（Gap Lock）
在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
间隙锁一定程度解决在可重复读事务隔离级别下幻读的问题。
比如
- session 1查询a>5 and a<10
- session 2插入a=7
- session 1再次查询a>5 and a<10就会出现幻读（第一次没有7，第二次有7）
- 而间隙锁锁住(5, 10)，session 2的插入操作被阻塞，就不会出现幻读了
- sql走的索引为非唯一索引
- sql是一个范围查询，此时即使索引不是非唯一索引，也会加gap lock
Next-Key Lock
为了解决幻读出现
如果索引有10,11,13这三个值，那么被Next-Key Locking的区间为：
(-$\infty$, 10] (10, 11] (11, 13] (13, +$\infty$)
Next-Key Lock在查询的列是唯一索引的时候，会降级为行锁。
举栗子：
CREATE TABLE Z (a INT, b INT, PRIMARY KEY(a), KEY(b));
INSERT INTO z SELECT 1, 1;
INSERT INTO z SELECT 3, 1;
INSERT INTO z SELECT 5, 3;
INSERT INTO z SELECT 7, 6;
INSERT INTO z SELECT 10, 8;
-- 执行锁定语句(会话a)
-- 辅助索引产生Next-Key Lock(1, 3) Gap Lock(3, 6)
-- 聚簇索引产生行锁 (5)
SELECT * FROM z WHERE b=3 FOR UPDATE;
-- 会话b
SELECT * FROM z WHERE a=5 LOCK IN SHARE MODE; -- 阻塞，聚簇行锁5
INSERT INTO z SELECT 4,2; -- 阻塞，Next-Key Lock(1, 3)
INSERT INTO z SELECT 6,5; -- 阻塞，Gap Lock(3, 6)
INSERT INTO z SELECT 11,7; -- 成功
一致性非锁定读
通过mvcc，产生行锁时，依然可以读取改行数据的快照（快照在undo log中）
相关信息
- 在INFORMATION_SCHEMA架构下这3张表
- INNODB_TRX：记录事务信息
- INNODB_LOCKS：记录锁信息
- INNODB_LOCK_WAITS：记录锁等待信息
- 自增长与锁
自增长会产生锁的性能花销，但是与查询的性能提升对比，还是值得的。也可以自行在系统中维护主键的自增长，不依赖数据库。 - 外键与锁
当锁住parent表（X锁）的某一行时，child表插入数据（关联parent表的那一行）时，会阻塞。此时不会使用一致性非锁定读
原文地址：https://www.cnblogs.com/konghuanxi/p/14785556.html
