innodb默认行锁,如like不确定扫描范围也会锁表,也可不加锁

保证共享资源一致性

实现事务隔离性

死锁

自动检测死锁,回滚一或几个事务防死锁

共享锁(Share Lock)

SELECT ... LOCK IN SHARE MODE; 

查询结果每行加共享锁

没有其他线程对查询结果集用排他锁时,可成功申请,否则阻塞

排他锁(eXclusive Lock)

X 锁,写锁、独占锁

SELECT ... FOR UPDATE;

申请条件同上

行锁

行锁,锁数据少 > 并发好,mysql一般都行锁处理并发事务

行锁(Record Lock)

锁直接加在索引记录上面,锁住的是 key

表没索引,用隐式主键(ROWID)锁定

间隙锁(Gap Lock)

锁定范围,但不包含记录本身

唯一作用就是防其他事务插入操作,防幻读

当隔离级别降至 RC 或innodb_locks_unsafe_for_binlog=ON 关闭间隙锁

Next-Key Lock

行锁+范围锁(Record Lock + Gap Lock),锁定范围,和记录本身

即 UPDATE、DELETE 不仅锁定 WHERE 条件扫描过的所有索引记录,而且会锁定相邻的键值,其他事务就不能在这个间隙修改或者插入记录,同样防幻读

RR 隔离级别下,Next-key Lock 是默认行记录锁定算法

事务

开启事务获取隐式锁,提交/回滚时释放锁,根据隔离级别自动处理锁

ACID

隔离性通过 InnoDB 锁就可以实现

原子性、一致性、持久性通过 redo 和 undo 完成

多版本的并发控制协议(MVCC)

与 MVCC 相对的,是基于锁的并发控制

MVCC 数据分多版本,各自关联一个时间戳

读只能取小于其时间戳的最大时间戳的数据;写用两阶段封锁协议

最大好处是:读不加锁,读写不冲突。极大增加并发性能

读操作

  • 快照读 (snapshot read)

读取的是记录的可见版本 (有可能是历史版本),不用加锁

  • 当前读 (current read)

读取记录最新版本,且返回记录加锁,保证其他事务不会再并发修改这条记录

每行数据后添加两个额外隐藏值(何时被创建,何时过期(或被删除))来实现 MVCC

记录何时创建/过期实现方式,基于时间戳(Oracle),基于事务 ID(Innodb、Postgres)

事务 ID 实现中,每开启一个新事务,事务的版本号就会递增

select、insert、delete update 操作数据的规则如下(Repeatable reads 事务隔离级别下)

  • SELECT 时,读取创建版本号 <= 当前事务版本号 并且 删除版本号为空或> 当前事务版本号
  • INSERT 时,保存当前事务版本号为行的创建版本号
  • DELETE 时,保存当前事务版本号为行的删除版本号
  • UPDATE 时,插入一条新纪录,保存当前事务版本号为行创建版本号,同时保存当前事务版本号到原来删除的行

MVCC空间换时间,减少锁使用,大多数读不用加锁,且也能保证只会读取到符合标准的行,也只锁住必要行

MVCC 只工作在 RC 和 RR 隔离级别

快照读(snapshot read)

只读早于或等于自己事物版本号的行记录,提高读并发。简单 select 属于快照读,不加锁

select * from table where ?

不同隔离级别不同

RC 读锁定行最新快照数据

RR、Serializable 读事务开始时版本

当前读(current read)

特殊的读操作,插入 / 更新 / 删除操作,属于当前读,需要加锁

select * from table where ? lock in share mode
select * from table where ? for update
insert into table values (…)
update table set ? where ?
delete from table where ?

幻读(Phantom Read)

同事务中,同样操作读两次,得到的记录数不相同

当事务不是独立执行时发生的一种现象

第一个事务对一个表中的数据进行了修改,涉及全部数据行

同时第二个事务向表中插入一行新数据

第一个事务发现表中还有没有修改的数据行,就好象发生了幻觉一样

解决办法:当前事务完成数据处理之前,其他事务不可以添加新数据

脏读(Dirty Read)

读到了另一个事务没有提交的数据

不可重复读(Non-repeatable Read)

同事务中,两次读取同一数据,得到内容不同

事务没结束时,另一事务修改同一数据。第一个事务中的两次读数据之间,由于第二个事务的修改,两次读到的的数据可能是不一样的

解决办法:如果只有在修改事务完全提交之后才可以读取数据,则可以避免该问题

简单区别不可重复读和幻读:不可重复读偏向的是 update、delete 带来的问题,幻读是 insert 带来的问题

隔离级别(Isolation Level)

InnoDB 支持的 4 种隔离级别:

级别解释
Read Uncommited【未提交读】也就是可能读取到其他会话中未提交事务修改的数据,一般不会用。(允许脏读)
Read Committed (RC)【已提交读】只能读取到已经提交的数据,数据的读取都是不加锁的,但是数据的写入、修改和删除是需要加锁的。Oracle 默认级别。(防止脏读)
Repeatable Read (RR)【可重复读】在同一个事务内的查询都是事务开始时刻一致的。InnoDB 默认级别。在 SQL 标准中,该隔离级别消除了不可重复读,但是还存在幻象读。(可防止脏读、不可重复读)
Serializable【可串行化】完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞,会降低数据库的效率。(可防止脏读、不可重复读、幻读)

4 种隔离级别区别:

隔离级别脏读(Dirty Read)不可重复读(NonRepeatable Read)幻读(Phantom Read)
未提交读(Read uncommitted)可能可能可能
未提交读(Read uncommitted)不可能可能可能
可重复读(Repeatable read)不可能不可能可能
可串行化(Serializable)不可能不可能不可能

Mysql 下查看、操作隔离级别常用命令:

select @@tx_isolation;  // 查看当前会话的事务隔离级别
select @@global.tx_isolation; // 查看全局事务隔离级别

二阶段锁协议(2PL)

Two-phase Locking(两阶段锁)

锁操作分 加锁/解锁 两阶段,且保证加锁解锁阶段不相交

加锁阶段:只加锁,不放锁

解锁阶段:只放锁,不加锁

加锁阶段:读前申请并获得 S 锁,写前申请并获得 X 锁。加锁不成功,事务等待,直到加锁成功才继续执行

事务                  加锁/解锁处理
begin;  
insert into test .....  加 insert 对应的锁
update test set...  加 update 对应的锁
delete from test ....   加 delete 对应的锁
commit;                 事务提交时,同时释放 insert、update、delete 对应的锁

InnoDB 行锁

通过给索引上的索引项加锁(理解这点很重要)来实现,Oracle通过在数据块中对相应数据行加锁来实现

InnoDB 行锁实现特点:只有通过索引条件检索数据,InnoDB 才使用行级锁,否则,InnoDB 将使用表锁!

除单个 SQL 组成的事务外,锁是逐步获得的,InnoDB 中发生死锁是可能的

查看锁情况

  1. innodb_row_lock

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     |
+-------------------------------+-------+
  1. InnoDB Lock Monitor

显示 InnoDB 的内部状态,每隔 15 秒 把 SHOW ENGINE INNODB STATUS 命令的输出重定向到标准错误输出流

如果设置了 innodb-status-file=1 ,还会将上述命令的输出额外写到一个名为 innodb_status.pid 的文件中

开启了 InnoDB Lock Monitor,还会打印额外的锁信息

打开 InnoDB Standard Monitor 和 InnoDB Lock Monitor:

set global innodb_status_output=ON;
set global innodb_status_output_locks=ON;

查看 session 级别和 global 级别的 autocommit 设置

select @@session.autocommit; // 当前 session
select @@global.autocommit; // 全局
show variables like 'autocommit'; // 查看当前 autocommit 模式

autocommit 模式的设置对拥有 super 权限的用户是无效的。 不要随便更改全局的 autocommit=0,可能会因为更新操作没有 commit 而导致死锁的产生。

主动探知死锁两个方法:

  1. 两个事务相互等待超过阀值对其中一个事务进行回滚,太被动
  2. wait-for graph 算法主动检测,加锁请求无法立即满足需要并进入等待时算法都触发

当探知到死锁时,Innodb 回滚正在等待的事物,结束死锁

高并发的 MySQL 服务器上,事务会递归检测死锁,当超过一定的深度时,性能的下降会变的不可接受。facebook 早就提出了禁止死锁检测

总结

Innodb 默认隔离级别是 RR,防止脏读、不可重复读,Next-key lock解决幻读

MVCC 好处:

  • 普通读不加锁,提高并发性能

  • 能保证只会读取到符合标准的行,也只锁住必要行