mysql-innodb-锁

锁定读的语句加锁类型注意事项select ... for update加X锁务必加上BEGIN， START TRANSACTION或者 SET AUTOCOMMIT=0select ... lock in share mode 加S锁

在最前

这是读书笔记，Mysql，innodb系列一共3篇。

• Mysql-innodb-B+索引
• Mysql-innodb-锁
• Mysql-innodb-事务预计20200530）

锁基本概念

加锁方式

记录r进行上X锁，先对数据库A、表、页上加意向锁IX，才能对记录r上X锁。

兼容性

锁的监控表

查询锁的情况：information_schema下

• innodb_trx事务表
• innodb_locks锁表
• innodb_lock_wait锁等待表

锁算法

3种锁算法

Record Lock

单行记录加锁

Gap Lock

Gap Lock间隙锁，锁一个范围

阻止多个事务将记录插入到同一范围内

Next Key Lock

Next Key Lock：Record Lock+Gap Lock，锁一个范围+锁一个记录

查询的列是唯一索引的情况时，降级为Record Lock。

举例说明

建表插入数据

CREATE TABLE z(
a INT,
b INT,
PRIMARY KEY(a),
index index_b(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

begin;SELECT*FROM z WHERE b=3 FOR UPDATE;

会话B

begin;SELECT*FROM z WHERE a=5 LOCK IN SHARE MODE;

不能执行Record Lock 锁定了5

2. INSERT INTO z SELECT 4,2;    

不能执行有Gap Lock

非一致性锁定读/一致性锁定读

非一致性锁定读

1. 不需要等待访问的行上X锁的释放，直接读快照，提高了数据库的并发性。

2. 快照数据来自undo段。undo段会用在事务回滚，所以快照数据本身没有额外开销。

3. 读快照信息不需要上锁，没有事务需要处理历史数据。

READ COMMITTED和 REPEATABLE READ快照定义的区别

一致性锁定读

锁常见问题

脏读，违反隔离性Isolation

不同的事务下，当前事务可以读到另外事务未提交的数据。

read uncommitted隔离级别下会发生

不可重复(Phantom Problem幻读)

一个事务内两次读到的数据是不一样的情况（当前事务没有结束。另外一个事务修改了）。

READ COMMITTED下会发生，会读到已经提交的数据 。

默认的事务隔离级别是

REPEATABLE READ。采用Next-Key Locking的算法，解决。

锁一个范围+锁一个记录。

丢失更新

任何隔离级别下都不会发生，但是应用层面会发生.

//假设id=3的账号余额为100，A事务转账99，
Update t set a=1 where id=3
//B事务转账1
Update t set a=99 where id=3
//B事务后提交，最后余额是99，A事务的丢失了

解决方式：

串行化处理，乐观锁等

阻塞

一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源

innodb_lock_wait_timeout来控制等待时间默认50s

innodb_rollback_on_timeout设置超时时是否回滚，默认off，不回滚。

死锁

两个事务，争夺锁，相互等待。

死锁发生的概率一般很低

• 系统中事务的数量（n），数量越多发生死锁的概率越大
• 每个事务操作的数量（r），每个事务操作的数量越多，发生死锁的概率越大
• 操作数据的集合（R），越小则发生死锁的概率越大

解决方式：

设置超时，等待超时的回滚，没有超时的继续，但是并发下降

innodb_lock_wait_timeout来控制等待时间默认50s

通常来说InnoDB存储引擎选择回滚undo量最小的事务

补充2-自增长与锁

自增长的列，必须是索引，且必须是索引的第一个列。

AUTO-INC Locking：

当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化。

执行： SELECT MAX(auto_inc_col)FROM t FOR UPDATE;

插入操作会依据这个自增长的计数器值加1赋予自增长列

该锁在执行完插入自增长值的SQL后释放

提高了部分性能，但是对应insert select会受影响，需要等待另外一个事务的完成AUTO-INC Locking。

Mysql5.1.22后，提供了轻量级互斥量，在内存中计算自增值提高性能

补充3-外键与锁

innodb外键自动加索引

插入或更新数据时，

先使用SELECT…LOCK IN SHARE MODE方式，即主动对父表加一个S锁。