mysql innodb 查询锁_mysql-innodb-锁

写在最前

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

锁基本概念

锁类型

说明

锁级别

意向共享锁 IS Lock

事务想要获得一张表中某几行的数据的共享锁

表级别锁

意向排他锁IX Lock

事务想要获得一张表中某几行数据的排他锁

表级别锁

共享锁S Lock

允许事务读取一行数据

行级别锁

排他锁X Lock

允许事务更新或删除一条数据

行级别锁

加锁方式

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

兼容性

IS

IX

S

X

IS

兼容

兼容

兼容

不兼容

IX

兼容

兼容

不兼容

不兼容

S

兼容

不兼容

兼容

不兼容

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。

举例说明

建表插入数据

会话A

会话B

不能执行Record Lock 锁定了5

不能执行有Gap Lock

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

非一致性锁定读

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

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

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

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

隔离级别

读取的快照数据

是否默认

隔离级别

存在的问题

READ COMMITTED

读锁定行最新的一份提交过数据

否

会出现幻读

REPEATABLE READ

读锁定行事务开始前的版本

是

无

一致性锁定读

锁定读的语句

加锁类型

注意事项

select … for update

加X锁

务必加上BEGIN，

START TRANSACTION或者

SET AUTOCOMMIT=0

select … lock in share mode

加S锁

锁常见问题

脏读，违反隔离性Isolation

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

read uncommitted隔离级别下会发生

不可重复(Phantom Problem幻读)

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

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

默认的事务隔离级别是

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

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

丢失更新

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

解决方式：

串行化处理，乐观锁等

阻塞

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

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锁。