mysql 锁 & 事务
发布于
Top
mysql

锁作用

  • 保证共享资源一致性

  • 实现事务隔离性

锁粒度(等级)

  • 表级锁:开销小,加锁快; 不会死锁;锁定粒度大,发生锁冲突的概率最高 ,并发度最低
  • 行级锁:开销大,加锁慢;会出现死锁(锁是逐步获得的);锁定粒度最小,发生锁冲突的概率最低,并发度也最高
  • 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

存储引擎各自实现锁机制,server层不感知存储引擎的锁实现

InnoDB锁模式

行锁

  • 共享锁(S)

  • 排他锁(X)

表锁

  • 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的 IS 锁

  • 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的 IX 锁

允许行锁和表锁共存,实现多粒度锁机制

详解 MySql InnoDB 中意向锁的作用


隐式& 显式 锁定

隐式锁定

事务执行过程中,使用两阶段锁协议:

  • 随时都可以执行锁定,InnoDB会根据隔离级别在需要的时候自动加锁

  • 锁只有在执行commit或者rollback的时候才会释放,并且所有的锁都是在 ``同一时刻` 被释放

显式锁定 :

select ... lock in share mode
select ... for update

乐观&悲观锁

乐观锁

select id,value,version from TABLE where id = #{id}

更新时,为防止发生冲突,需要这样操作

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version}

失败后重试或回滚

悲观锁

直接调用数据库的相关语句

悲观锁涉及的另外两个锁概念:共享锁与排它锁

共享锁(S):

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

确保自己查到的数据没有被其他的事务正在修改,也就是说确保查到的数据是最新的数据,并且不允许其他人来修改数据(获取数据上的排他锁)

自己不一定能够修改数据,因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁


当前事务对读锁进行修改操作,很可能会造成死锁

create table innodb_lock(
id bigint primary key auto_increment,
v int
);

insert into innodb_lock (id,v) values(1,'dd');
Transaction A Transaction B
set autocommit=0;select * from innodb_lock where id=1 lock in share mode; set autocommit=0;select * from innodb_lock where id=1 lock in share mode;
update innodb_lock set v=2 where id=1;
等待
等待 update innodb_lock set v=2 where id=1;
Deadlock found when trying to get lock; try restarting transaction
Affected rows: 1

排他锁(X):

SELECT * FROM table_name WHERE ... FOR UPDATE

自己查到的数据确保是最新数据,并且查到后的数据只允许自己来修改

行锁

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

多个事务操作同一行数据时,后来的事务:

  1. 处于阻塞等待状态,避免脏读等数据一致性问题

  2. 可以操作其他行数据,解决表锁高并发性能低的问题

# Transaction-A
set autocommit = 0;
update innodb_lock set v=1001 where id=1;
commit;

# Transaction-B
update innodb_lock set v=2001 where id=2;
update innodb_lock set v=1002 where id=1;

InnoDB的行锁是针对索引加的锁,不是针对记录加的锁。并且该索引不能失效,否则都会从行锁升级为表锁

索引失效:

  1. explain看 key = null
  2. like 等不确定扫描范围

  • 劣势:开销大;加锁慢;会出现死锁

  • 优势:锁的粒度小,发生锁冲突的概率低;处理并发的能力强

  • 加锁方式: 自动加锁

    1. UPDATE、DELETE和INSERT,InnoDB自动给涉及数据集加排他锁

    2. 普通SELECT,InnoDB不会加任何锁;也可以显示的加锁

间隙锁

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

用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁

对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

例,假如emp表中只有101条记录,其empid的值分别是 1,2,…,100,101

Select * from  emp where empid > 100 for update;

是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁

InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,也会使用间隙锁

间隙锁目的:

  • 防止幻读,满足隔离级别的要求

    要是不用间隙锁,其他事务插入empid>100的记录,本事务如果再次执行上述select,就会发生幻读

  • 满足恢复和复制的需要

    通过 BINLOG 实现恢复和主从复制

    Binlog 按事务提交先后顺序记录, 恢复也是按这个顺序进行

    由此可见,MySQL 的恢复机制要求:在一个事务未提交前,其他并发事务不能插入满足其锁定条件的任何记录,也就是不允许出现幻读

更新丢失

A在卡有100元钱,某一刻用户B向A转账50元(称为B操作),同时有用户C向A转账50元(称为C操作)

B操作从数据库中读取他此时的余额100,计算新的余额为100+50=150

C操作也从数据库中读取他此时的余额100,计算新的余额为100+50=150

B操作将balance=150写入数据库,之后C操作也将balance=150写入数据库

最终A的余额变为150!=正确值200

原因是B和C向A发起转账请求时,同时打开了两个数据库会话,进行了两个事务,后一个事务拿到了前一个事务的中间状态数据,导致更新丢失

常用解决思路:

  • 加锁同步执行

  • update前检查数据一致性

  • 原子更新 update account set balance=balance+50 where xx


update,delete操作会自动加排他锁, 但即使加了排他锁也无法阻止select操作

而select XX for update 可以对select操作加上排他锁

所以为了防止更新丢失可以在select时加上for update加锁,这样就可以阻止其余事务的select for update (但注意无法阻止select)


在一个事务系统中,死锁是确切存在并且是不能完全避免的

InnoDB会自动检测事务死锁,立即回滚其中某个事务,并且返回一个错误。根据某种机制来选择那个最简单(代价最小)的事务来进行回滚

Next-Key Lock

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

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

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

事务

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

ACID

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

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

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

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

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

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

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

锁和多版本数据(MVCC)是 InnoDB 实现一致性读和 ISO/ANSI SQL92 隔离级别的手段

在不同的隔离级别下,InnoDB 处理 SQL 时采用的一致性读策略和需要的锁是不同的

读操作

  • 快照读 (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) 不可能 不可能 不可能

二阶段锁协议(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. 两个事务相互等待超过阀值对其中一个事务进行回滚,太被动
  2. wait-for graph 算法主动检测,加锁请求无法立即满足需要并进入等待时算法都触发

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

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

总结

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

MVCC 好处:

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

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