在 MySQL 当中, 关于 innodb 的锁类型总共可以分为四种, 包含了行锁和表锁, 分别是
基本锁 - [ 共享锁 (Shared Locks:S 锁) 和排它锁(Exclusive Locks:X 锁)]
意向锁 - [ intention lock, 分为意向共享锁 (IS 锁) 和意向排他锁(IX 锁)]
行锁 - [ record Locks,gap locks,next-key locks,Insert Intention Locks ]
自增锁 - [ auto-inc locks ]
下面是各种锁之间的对应兼容情况(ps: 在某一篇博客上看到, 忘了是哪一篇, 觉得好就截下来了嘻嘻):
InnoDB 三种行锁的算法:
Record Lock: 单个行记录上的锁, 只锁定记录本身
Gap Lock: 间隙锁, 锁定一个范围, 但不包括记录本身. 目的是为了防止同一个事物的两次当前读, 出现幻读的情况
Next-Key Lock:1+2, 锁定一个范围, 并锁定记录本身. 目的: 解决幻读
共享锁
共享锁 shared locks(S 锁)也称读锁, 允许其他事物再加 S 锁, 不允许其他事物再加 X 锁
加锁方式:
select...lock in share mode
注意:
对于使用共享锁的事务, 其他事务只能读, 不可写
如果执行了更新操作则会一直等待, 直到当前事务 commit 或者 rollback
如果当前事务也执行了其他事务处于等待的那条 sql 语句, 当前事务将会执行成功, 而其他事务会报死锁
并且允许其他锁共存
排它锁
排它锁 Exclusive Locks(X 锁)也称写锁, 不允许其他事务再加 S 锁或者 X 锁
加锁方式:
select ... for update
→ for update:InnoDB 默认是行级别的锁, 当有明确指定的主键时, 使用的是行锁; 否则使用的是表锁. 使用情况详细如下:
明确指定主键, 并且由此记录, 行级锁. 例:
select name,age from tb_user where id = '1' for update(id 是主键)
明确指定主键 / 索引, 若查无记录, 无锁. 例:
select name,age from tb_user where id = '1' for update(id 是主键, 但不存在 id = 1 的数据)
无主键 / 索引, 表级锁. 例:
select name,age from tb_user where age = 12 for update(age 是普通字段)
主键 / 索引不明确, 表级锁. 例:
select name,age from tb_user where age = 12,id = '1' for update(id 是主键, age 不是, 但数据库有此数据)
注意:
对于排它锁的事务, 其他事物可读, 但不可进行更新操作
for update 仅使用与 InnoDB, 并且必须开启事务, 在 begin 和 commit 之间才生效
当一个事务进行 for update 的时候, 另一个事务也有 for update 时会一直等待, 直到之前的事务 commit 或 rollback 或断开连接释放锁才拿到锁进行后面的操作(排它锁不能共存)
innoDB 引擎. 默认对 update,delete,insert 加排他锁, select 语句默认不加锁
乐观锁
读取出记录, 并将此版本一同读出, 执行更新操作并对记录的版本号 + 1. 此时, 将待提交记录的版本号与数据库对应表的记录版本好进行对比, 如果大于数据库原有版本号的话, 予以更新; 否则认为是过期数据, 更新失败. 目的是为了用于解决并发问题.
例: A,B 两个人同时修改同一条记录, 设数据库原有金额是 100 元, A 对金额 + 100,B 往数据库 - 50, 正常结果是 150, 但由于并发, 结果有可能是 200, 或者 50
解决: A B 同时读取出数据版本为 1,A 对金额 + 100, 并修改数据版本为 2, 提交数据, 此时数据版本为 1, 更新成功. B 读取数据版本 1, 对金额 - 50, 此时结果为 50, 并修改数据版本为 2, 提交数据, 对比数据库原版本 2, 没有比原版本高, 更新失败
间隙锁
间隙锁是在索引记录之间的间隙的锁定, 或在最后一个索引记录之前或之后的间隙上的锁定
使用唯一索引搜索唯一一行的一句不需要间隙锁锁定(不包括搜索条件包含多列唯一索引的某些列的情况, 查询出的多条记录, 会发生间隙索引), 详细例子如下:
前提:(id 是主键索引)由于搜索结果是唯一的一条记录, 所以不会使用间隙锁
select id,name,age from tb_user where id = '1'
前提:(id 是主键索引, age 是非索引字段)由于搜索结果可能不止一条记录, 所以会使用间隙锁 select id,name,age from tb_user where id = '1' and age = 13
演示:
→ 数据结构:
- CREATE TABLE `tb_user` (
- `id` int(10) NOT NULL,
- `name` varchar(255) NOT NULL DEFAULT '',
- KEY `index_id` (`id`),
- KEY `index_name` (`name`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
→ 初始化数据:
ps: 我现在使用的是数据库默认的隔离级别: repeatable read, 并在本地开启两个客户端进行测试.
→ 客户端 1: 开启事务, 在客户端中修改 id 为 1-6 之间的数据, 此时 id=2 这行记录是不存在的
→ 客户端 2: 开启事务, 往数据库中添加 id 为 2 的记录时会发现该操作会被阻塞!
-->上述情况就说明了有间隙锁的存在
--> 接下来我修改了隔离级别为 read commited, 可以发现上述添加操作, 即 id =2 的记录会添加成功, 说明 read commited 的隔离级别不会使用间隙锁.
注意:
间隙锁在 InnoDB 的唯一作用就是防止其它事务的插入操作, 以此来达到防止幻读的发生, 所以间隙锁不分什么共享锁与排它锁.
如果 InnoDB 扫描的是一个主键 / 唯一索引, 那么 InnoDB 只会采用行锁 (Record Lock) 方式来加锁, 而不会使用间隙锁 (Next-Key Lock) 的方式.
间隙锁只是阻止其他事物插入到间隙当中, 并不阻止不同的事物在同一间隙上获得间隙锁.
将隔离级别设置为 read_commited 或启用 innodb_locks_unsafe_for_binlog 系统变量 (现已被弃用) 可明确禁止使用间隙锁
MVCC(Snapshot read vs current read)
MVCC, 基于多版本的并发控制协议, 最典型的是读不加锁, 读写不冲突, 其包含两种读操作, 即快照读 (snapshot read) 与当前读(current read).
快照读: 读取记录的可见版本, 不加锁.
当前读: 读取记录的最新版本, 当前读返回的记录, 都会加锁, 保证其他事物不会再修改这条记录
那具体哪些操作为当前读, 哪些操作又是快照读呢, 让我们来看一下:
→ 快照读: 简单的读操作, 属于快照读, 不加锁.(不过有些会有点小例外)
例: select * from tb_user where ?
→ 当前读: 特殊的读操作, 属于当前读, 需要加锁.
- 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 ?;
所有以上的语句, 都属于当前读, 读取记录的最新版本. 并且, 读取之后, 还需要保证其他并发事务不能修改当前记录, 对读取记录加锁. 其中, 除了第一条语句, 对读取记录加 S 锁 (共享锁)外, 其他的操作, 都加的是 X 锁 (排它锁).
来源: http://www.jianshu.com/p/358cc1eb40ac