锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。
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)

在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。
间隙锁一定程度解决在可重复读事务隔离级别下幻读的问题。
比如

  1. session 1查询a>5 and a<10
  2. session 2插入a=7
  3. session 1再次查询a>5 and a<10就会出现幻读(第一次没有7,第二次有7)
  4. 而间隙锁锁住(5, 10),session 2的插入操作被阻塞,就不会出现幻读了
  5. sql走的索引为非唯一索引
  6. 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张表
    1. INNODB_TRX:记录事务信息
    2. INNODB_LOCKS:记录锁信息
    3. INNODB_LOCK_WAITS:记录锁等待信息
  • 自增长与锁
    自增长会产生锁的性能花销,但是与查询的性能提升对比,还是值得的。也可以自行在系统中维护主键的自增长,不依赖数据库。
  • 外键与锁
    当锁住parent表(X锁)的某一行时,child表插入数据(关联parent表的那一行)时,会阻塞。此时不会使用一致性非锁定读

原文地址:https://www.cnblogs.com/konghuanxi/p/14785556.html