建表语句
CREATE TABLE `hero` (
`number` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`country` varchar(100) DEFAULT NULL,
PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO hero VALUES
(1, 'l刘备', '蜀'),
(3, 'z诸葛亮', '蜀'),
(8, 'c曹操', '魏'),
(15, 'x荀彧', '魏'),
(20, 's孙权', '吴')
事务并发执行遇到的问题
- 脏写 指一个事务修改了另一个事务未提交的数据
- 脏读 一个事务读取到了另一个事务未提交的数据
- 不可重复读 在某个事务内读取到了其他已提交事务修改后的数据
- 幻读 如果一个事务先根据某些条件查询出一些记录,之后另一个事务想表中插入了符合这些条件的记录,原先事务按之前的条件查询时发现查询出的数据与之前不一致,把另一个事务插入的数据也查询出来了。
四种隔离级别
- 读未提交
- 读已提交 解决脏读问题
- 可重复读 解决脏读、不可重复读问题
- 串行化 解决脏读、不可重复读、幻读问题
锁的分类
按粒度可以划分为锁定整个表的表级锁 和锁定记录行的行级锁。
- 表锁具有开销小,加锁快的特性,表锁的锁定粒度大,发生锁冲突的概率高,支持的并发低
- 行锁具有开销大,加锁慢的特性,行锁的锁定粒度小,发生锁冲突的概率低,支持的并发高
表锁
表级别的S锁 X锁
在对某个表执行 SELECT、INSERT、DELETE、UPDATE语句时,InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的。
InnoDb 提供的表级S锁或者X锁是相当鸡肋的,只会在一些特殊情况下,比如崩溃恢复过程中用到,但可以手动的获取一下锁,
- LOCK TABLES t read :对表t加表级别的S锁
- LOCK TABLES t write:对表t加表级别的X锁
表级别的IS锁 IX 锁
在对某些表添加记录添加X锁前,先添加一个表级别的IX锁,IX锁的使命就是为了在后续在添加表级别的锁时,无需去遍历判断表中是否存在加了锁的记录。IS锁同理。
行锁
record lock
innodb 通过给索引上的索引记录加锁的方式实现行级锁,是针对索引记录的锁定,假设执行了
SELECT * FROM hero where number =1 for update;
会阻塞其他事务对于number=1这条记录的新增、修改、删除操作。
记录锁,区分X锁和S锁.
S锁,当某条记录被事务加上S锁时,其他事务可以获取到S锁,但不能获取到X锁。
SELECT * FROM hero where number = 1 LOCK IN SHARE MODE;
X锁,当某条记录被事务加上X锁时,其他事务不可以获取到S锁,也不能获取到X锁。
SELEFT * FROM hero where number = 1 for update;
S锁举例
#事务一
begin;
# 加s锁
SELECT * FROM hero where number = 1 LOCK IN SHARE MODE;
# 事务二
begin;
# 加S锁,不阻塞
SELECT * FROM hero where number = 1 LOCK IN SHARE MODE;
# 加X锁 阻塞
SELECT * FROM hero where number = 1 FOR UPDATE;
X锁举例
# 事务一
SELECT * FROM hero where number = 1 FOR UPDATE;
# 事务二
# 加S锁 阻塞
SELECT * FROM hero where number = 1 LOCK IN SHARE MODE;
# 加X锁 阻塞
SELECT * FROM hero where number = 1 FOR UPDATE;
gap lock
gap lock 锁定的是索引记录之间的间隙、第一行索引之前的间隙或者最后一行索引之后的间隙。
gap 锁的目的只是为了防止幻读。
如果你给一条记录加了gap锁,并不会影响其他事务给该记录加record lock 和gap lock。
举例:
# 事务1 锁住1->3 之间的间隙,给1-3之间加gap lock x锁
begin;
SELECT * FROM hero where number >1 and number <3 for update;
commit;
# 事务2 锁住3->3之间的间隙,给1-3之间加gap lock x锁
begin;
# 加锁成功,证明其他事务可以给相同记录添加gap lock
SELECT * FROM hero where number >1 and number <3 for update;
# 尝试给2->8之间加gap lock x锁
# 不阻塞
SELECT * FROM hero where number >2 and number <8 for update;
# 阻塞
insert into hero VALUES(26,'d典韦','魏');
COMMIT;
Insert Intention Locks 插入意向锁
某一个事务在插入一条记录时判断插入位置是否被别的事务加了所谓的gap lock
,如果有的话,插入操作是需要等待的。 insert initention lock 表明了当前事务存在后序存在需要进行插入的操作。也就是在等待 gap lock 锁释放时在内存中存在的锁结构。
next key lock
相当于一个索引记录加上该记录之前的一个间隙锁。也就是gap lock 和record lock的合体。
参考 https://tonydong.blog.csdn.net/article/details/103324323