MySQL 六.锁
数据库的难点之一就是：一方面最大可能的处理并发，另一方面还需要用户读取的数据一致性；为此，数据库设计了锁(locking)机制；作为开发者，最好是了解这些所得机制，帮助我们更好实现我们的业务处理；
6.1 什么是锁
数据库和文件系统最大的区别之一就是锁机制；锁机制是用来处理共享资源的并访问。InnoDB支持行级别的锁，但是不仅仅是数据库的行数据，缓存池里面LRU 列表的增删改查都涉及到锁；
注意：不同数据库，不同存储引擎对于锁的实现都是不一样的；
6.2 lock 和latch
lock 和latch 都可以被称为锁，但是两个有着不一样的意义； 本章节主要是lock
latch:闩锁 ，一种轻量级的锁，要求锁的时间非常的短 ，在InnoDB存储引擎中 latch 分为mutex 和 rwlock ;保证并发线程操作临界资源的准确性，一般没有检查死锁的机制；
lock: lock 的对象是事务，用来锁定的是数据库中的对象，如表，页，行。并且这些被lock的对象一般是在事务commit或者是rollback 的时候在进行释放。 也会有死锁；
我的理解就是latch 就是数据库内部实现的代码锁,是数据库对象的一个熟悉，就像是Java对象头里面的那个当前线程，lock 是数据库对象的锁，可以表示一个事务
## sql 查看latch状态 (debug 版本)
show engine Innodb mutex ; -- 输出字段如下
### lock 的查看;
SHOW ENGINE INNODB STATUS
select * from information_schema.INNODB_TRX/INNODB_LOCKS/INNODB_LOCK_WAITS
6.3 InnoDB存储引擎中的锁
6.3.1 锁的类型
InnoDB中实现了下面两种标准的行级锁
- 共享锁 S LOCK 允许事务读一行数据
- 排它锁 X LOCK 允许事务修改或者删除一行数据；
事务T1 获取 R 行的 S 锁， 事务 T2 可以也立刻获取 R 行的S锁 ；但是如果T3 想要获取R 行上的X 锁 这种情况下需要等待T1,T2 释放锁
兼容性如下
| S | X | |
|---|---|---|
| S | 兼容 | 不兼容 |
| X | 不兼容 | 不兼容 |
InnoDB支持多粒度的锁,这种锁定是支持表级别或者是行级别锁同事存在;它的具体实现就是意向锁,意向锁支持多个粒度 的锁定
现在如果是要对表1 上面的一条记录r 上一个X 锁,那么,就需要对表,页 上一个意向锁IX ,最后在给记录上X锁,;粒度一层层变细.在加锁的过程中,其中一个阻塞后,后续的加锁操作需要等待前面的加锁完成后才能进一步加细粒度的锁;
InnoDB支持多粒度锁定，允许行锁和表锁共存,意向锁是表级锁，指示事务稍后(共享锁是完全兼容的)需要对表中的行使用哪种类型的锁（共享锁或独占锁）。意向锁有两种类型
兼容情况如下
| IS | IX | S | X | |
|---|---|---|---|---|
| IS | 兼容 | 兼容 | 兼容 | 不兼容 |
| IX | 兼容 | 兼容 | 不兼容 | 不兼容 |
| S | 兼容 | 不兼容 | 兼容 | 不兼容 |
| X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
如果请求事务与现有锁兼容，则向请求事务授予锁，但如果与现有锁冲突，则不会。事务一直等到冲突的现有锁被释放。如果锁定请求与现有锁定发生冲突并且由于会导致死锁而无法授予 ，则会发生错误;
在InnoDB 的锁信息，下面三张表 可以简单的查看并分析可能存在的锁问题
select * from information_schema.INNODB_TRX;
select * from information_schema.INNODB_LOCKS;
select * from information_schema.INNODB_LOCK_WAITS;
INNODB_TRX
这个是老版本的字段,在最新的版本中又添加了一下更加详细的记录数据
INNODB_LOCKS
INNODB_LOCK_WAITS
可以看到哪一个事务阻塞了另外一个事务
6.3.2 一致性非锁定读
一致性非锁定读 consistent nonlocking read 是指InnoDB存储引擎通过多版本控制的方式来读取当前时间数据库中的数据,如果数据在执行update /delete 的操作 ,读取的操作不需要等待锁的释放,相反的回去读取该部分数据的历史快照;
读取是通过undo 段实现; undo 是用来事务的回滚操作
在InnoDB 的默认设置下,这个是默认的读取方式,读取数据不会占用表上面的锁,可以极高的提高数据库的并发性;
但是在不同的事务隔离级别下,读取方式的不同,并不是每个隔离级别都才用非锁定的一致读; 但是就算是一致性非锁定读,对数据快照的定义也不同;
在上图中可以看到,一个数据可能会有多个历史的版本,一般称之为多版本技术,由此带来的版本控制,叫做多版本控制MVCC
在事务的隔离级别是READ COMMITED 和 REPEATABLE READ (InnoDB 的默认隔离级别),InnoDB 采用的是非锁定的一致性读.但是对快照的定义是不同的.
READ COMMITED隔离级别下,是读取被锁定行的最新一份快照.REPEATABLE READ事务隔离级别下 读取的是事务开始的时候的数据库版本;
测试一下 这些特性 先创建一张表
create table demo
(
id int auto_increment
primary key,
name varchar(255) null
)
charset = utf8;
原始数据
READ COMMITED
| 顺序 | 结果1 | 会话1 | 会话2 |
|---|---|---|---|
| 1 | begin ; | ||
| 2 | A | select * from demo where id = 120 ; | |
| 3 | begin ; | ||
| 4 | update demo set name = ‘B’ where id = 120; | ||
| 5 | A | select * from demo where id = 120 ; | |
| 6 | commit ; | ||
| 7 | B | select * from demo where id = 120 ; | |
| 8 | commit ; |
REPEATABLE READ
| 顺序 | 结果1 | 会话1 | 会话2 |
|---|---|---|---|
| 1 | ```sq | begin ; | |
| 2 | A | select * from demo where id = 120 ; | |
| 3 | begin ; | ||
| 4 | update demo set name = ‘B’ where id = 120; | ||
| 5 | A | select * from demo where id = 120 ; | |
| 6 | commit ; | ||
| 7 | A | select * from demo where id = 120 ; | |
| 8 | commit ; | ||
| B | select * from demo where id = 120 ; |
可以看到在REPEATABLE READ 的隔离级别下,一个事务内读取的数据是一致的,( 注: 但是遇到更新还是会找最新的数据) READ COMMITED 读取的数据是最新的版本数据,从数据库理论的角度来说,其违反了事务的ACID 事务的I isolation隔离性
6.3.3 一致性锁定读
InnoDB的事务默认隔离级别是 REPEATABLE READ,select 的操作是一致性非锁定读,但是在某种情况下,用户需要显示的对数据库读取操作加锁以保证数据逻辑的一致性.这个就要求数据库需要支持加锁的语句,对于select 语句这种只读操作,InnoDB支持两种一致性的锁定读(locaking read):
- select … from update 添加 X 锁 其他事务不能加任何的锁
- select … lock in share mode 添加S 锁 其他事务可以加S锁 但是不能加X 锁
update
| 顺序 | 结果1 | 会话1 | 会话2 | 结果2 |
|---|---|---|---|---|
| 1 | begin ; | |||
| 2 | A | select * from demo where id = 120 for update ; | ||
| 3 | begin | |||
| 4 | select * from demo where id = 120 for update ; | 等待 挂起 | ||
| 5 | select * from demo where id = 120(非一致性读取) | A | ||
| 6 | commit; | |||
| 7 | 步骤4 挂起状态取消 可以读取数据 | |||
| 8 | commit; |
share mode
| 顺序 | 结果1 | 会话1 | 会话2 | 结果2 |
|---|---|---|---|---|
| 1 | begin ; | |||
| 2 | A | select * from demo where id = 120 lock in share mode ; | ||
| 3 | begin | |||
| 4 | select * from demo where id = 120 lock in share mode | A | ||
| 5 | select * from demo where id = 120(非一致性读取) | A | ||
| 6 | select * from demo where id = 120 for update; | 等待 挂起 | ||
| 7 | commit; | |||
| 8 | 步骤4 挂起状态取消 可以读取数据 | |||
| commit; |
6.3.4 自增长与锁
一般我们创建表习惯使用自增长的id. 在InnoDB 存储引擎的内存结构中,对于具有自增长的值得表都有一个自增长的计数器 auto-increment counter. 每次有数据插入的话,计数器就会被初始化 执行下面的语句的得到计数器的值
select max(auto_inc_col) from t from update;
插入操作会依据这个自增长的计数器值+1 给到即将插入行的自增长列; 这种方式被称为 AUTI-INC Locking.这种锁是一个特殊的表锁机制.这个表锁是在获取到自增长值得时候就释放 ,而不是等待插入事务结束后释放;
这种锁机制一定程度上提高了并发插入的性能(减少了锁时间),但是还是存在一些问题(因为插入有锁了);一个插入数据的事务必须等待前面一个插入(不是事务)的完成.其次如果一个插入是 INSERT ....SELECT 这种的大量数据的插入,那么另一个事务中的插入就会被阻塞住;
MySQL 5.1.22 后新的实现机制:轻量级互斥量的自增长实现机制,大大提高自增长插入性能.并且加入参数innodb_autinc_lock_mode 来控制自增长的模式.默认参数是1 ;自增长的插入也是有分类的,自增长的实现也是和插入分类相关;
| 插入分类 | 说明 |
|---|---|
| insert-like | 所有的插入语句,如insert ,replace,insert…select ,replace…select, load data 等 |
| simple inserts | 插入前就知道插入行数 insert ,replace |
| bulk inserts | 插入前不确定插入行数 insert…select ,replace…select, load data |
| mix-mode inserts | 一部分是自增长的 一部分是确定的插入 |
innodb_autinc_lock_mode | 说明 |
|---|---|
| 0 | AUTI-INC Locking 原有旧的实现方式 |
| 1 | simple inserts 才用互斥量(mutex) 去实现.bulk insert ;还是原有AUTI-INC Locking实现方式 不考虑回滚的话,每次插入自增长是连续的, relication 也能很好的工作 |
| 2 | 所有的都是互斥量(mutex) 去实现,每次插入是,可能出现不连续的自增长列relication 主从架构 在statment-Base 方式会有问题,ow-Base 才能保证主从一致 |
此外需要注意的是:InnoDB和MyISAM 不同 MyISAM 是表锁设计,不用考虑到并发插入的问题 注意:在master 使用InnoDB和 slave 上使用MyISAM 的 relication 主从复制架构下 需要考虑的问题
注意: 自增长的列必须是索引,而且作为组合索引不能放在后面,只能放在第一个列
create table t3(
a int auto_increment,
b int,
key (b,a)
)engine = innodb;
[42000][1075] Incorrect table definition; there can be only one auto column and it must be defined as a key
6.3.5 外键和锁
前面有说过,主要是为了数据完整性约束,但是不建议在数据库做这个约束,而是在应用层做约束检查;就是不建议使用;
如果更新表,会查询关联的外键的表,外键表有X锁;
-
如果是非锁定一致性读 ,可能会存在数据不一致的问题
-
如果使用的是LOCK IN SHARE MODE 的话, 外键表对关联表的查询会阻塞,知道外检表的X锁释放
6.4 锁的算法
6.4.1 行锁的三种算法
InnoDB有三种行锁的算法
Record Lock: 单行记录上的锁;锁住索引记录,没有索引的话,就锁住主键Gap Lock: 间隙锁 ,锁定一个范围,但不包含记录本身;Next-Key Lock:Record Lock+Gap Lock锁定一个范围,包含记录本身,
Next-Key Lock :行的查询才用这种算法,比如索引 10,11,13,20 四个值 被Next-Key Locking锁的区间为
(-∞,10],(10,11],(11,13],(13,20],(20,+∞),
这个设计是为了解决phantom problem (虚读)
同样的还有 previous locking 技术 锁定的区间就是(-∞,10),[10,11),[11,13),[13,20),[20,+∞),
示例: 如果 事务T1 Next-Key Locking 锁定了 (10,11], (11,13] 当数据 12插入是 锁定的范围是(10,11], (11,12] (12,13]
当查询的索引是唯一的时候,锁会降级为Record Lock (只有唯一索引单个查询才会降级,如果唯一索引是多个列,而查询条件只有多个列里面的一个,其实查询也还是变成了range 查询 ,而不是point查询 不会降级!!! )
测试数据: demo
| id | name |
|---|---|
| 120 | A |
| 121 | B |
| 122 | D |
| 顺序 | 结果1 | 会话1 | 会话2 | 结果2 |
|---|---|---|---|---|
| 1 | begin ; | |||
| 2 | A | select * from demo where id = 120 lock in share mode ; | ||
| 3 | begin | |||
| 4 | INSERT INTO test.demo (id, name) VALUES (123, 'C'); | |||
| 5 | commit; | |||
| 6 | commit; |
2 中只有一条记录 不是范围 唯一索引 ,锁会降级为Record Lock ==> 会话2 中插入立刻执行 ==> 提高并发性
测试数据:demo2
| id(主键索引) | age (辅助索引) |
|---|---|
| 1 | 1 |
| 3 | 1 |
| 5 | 3 |
| 7 | 6 |
| 10 | 8 |
辅助索引 区间为 (-∞,1],(1,3],(3,6],(6,8],(8,+∞),
辅助索引的锁Next-Key Locking 锁定范围为
1 锁足了(1,3] ,
2 并且会使用gap lock锁足下一个键值 (3,6]
| 顺序 | 结果1 | 会话1 | 会话2 | 结果2 |
|---|---|---|---|---|
| 1 | begin ; | |||
| 2 | A | select * from demo2 where age = 3 for update; | ||
| 3 | begin | |||
| 4.1 | select * from demo2 where age = 5 lock in share mode ; | 挂起 原因 1 | ||
| 4.2 | insert into test.demo2 (id, age) values (4, 2); | 挂起 原因 2 | ||
| 4.3 | insert into test.demo2 (id, age) values (6, 5); | 挂起 原因2 | ||
| 4.4 | insert into test.demo2 (id, age) values (10, 10); | 直接执行 | ||
| 5 | commit ; | |||
| 6 | commit; |
注意:测试顺序是 1,2,3,4.1 5 6 ; 1,2,3,4.2 5 6 ; …
Gap Lock 的作用是为了阻止多个事务将记录插入到同一个范围内,而这个会导致Phantom Problem ((虚读))问题的产生. 例如在上面的例子里面条件 age > 3 锁定 在插入一个 age = 100 ,没有间隙锁锁住所有[3 ,+∞) 的范围就会导致两次读取数据不一致 ;
可以显示的关闭 Gap Lock
1.设置隔离级别为READ COMMITED
2.将参数 Innodb_locks_unsafe_for_binlog 设置为1
6.4.2 解决Phantom Problem
Phantom Problem: 同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行;
在默认的事务隔离级别 REPEATABLE READ下，InnoDB存储引擎采用Next-Key Locking机制来避免Phantom Problem(上面讲过了),这个不同于其他数据库,可能需要SERIALIZABLE才能实现
6.5 锁问题
锁机制可以实现事务的隔离性要求,是的事务可以并发的工作.相反的,锁也会带来潜在的问题
6.5.1 脏读
脏读(Dirty Read) ,首先理解脏数据,不同与之前说的脏页(缓存池里面被修改的数据页,还没有来得及刷新到磁盘 ,是内存和磁盘的异步造成的);脏数据是一个事务读取到了另外一个事务没有提交的数据;
生产环境中并不常见 ,因为一般设置的隔离级别都是在READ_UNCOMMITED之上 ;MySQL是 REPEATABLE READ ,SQLSERVER/ORACLE 都是READ_COMMITED
6.5.2 不可重复读
一个事务内两次读到的数据是不一样的情况，这种情况称为不可重复读;
不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的却是已经提交的数据，但是其违反了数据库事务一致性的要求
一般来说，不可重复读的问题是可以接受的，因为其读到的是已经提交的数据，本身并不会带来很大的问题;所以有的厂商设置为READ COMMITTED
InnoDB存储引擎的默认事务隔离级别是READ REPEATABLE，采用Next-Key Lock算法，避免了不可重复读的现象
6.5.3 丢失更新
个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致;
事务T1修改 记录r 为v1,还没有提交事务T2修改 记录r 为v2,还没有提交事务T1提交事务T2提交
但是在当前数据库的任何隔离级别下,DML操作都需要加一定的锁 T2 会阻塞,直到T1提交; 但是会演变成另一个问题;程序执行的结果是
T1 查询的更新结果是v1, 程序获取后显示在页面上 ,当T1提交的时候 数据库其实已经是 v2 了,
比如是转账业务 account ; user_id= 1 , money = 100
如果 sql都是
update account set money = money - xxx where user_id= 1
那也是没有问题的,但是往往在业务中是先查询 余额 看看 是否可以转账 然后获取到转账后的数据在set sql 一般就是
update account set money = 8000(一个具体的数字) where user_id= 1
一旦使用上面这个方法的时候 就是丢失更新数据
处理丢失更新的方法
| 顺序 | 结果1 | 会话1 | 会话2 | 结果2 |
|---|---|---|---|---|
| 1 | begin ; | |||
| 2 | select @money from account where user_id= 1 for update; | |||
| 3 | begin | |||
| 4 | select @money from account where user_id= 1 for update; | 挂起 | ||
| 5 | 其让业务 … @money 处理 | ` | 挂起 原因 2 | |
| 6 | update account set money =@money- 8000 where user_id= 1 | 挂起 原因2 | ||
| 7 | commit ; | 直接执行 | ||
| 8 | 4 挂起取消 执行查询 | |||
| 9 | update account set money =@money- 6000 where user_id= 1 | |||
| 10 | commit; |
6.6 阻塞
锁之间具有兼容性,当两个锁不兼容的时候,一个事务中的锁需要等待另外一个事务中的锁释放所占用的资源;
show variables like '%innodb_lock_wait_timeout%'
| Variable_name | Value |
|---|---|
innodb_lock_wait_timeout | 50 |
锁的等待时间是50s 可以动态的修改; 超时的错误为
[40001][1205] Lock wait timeout exceeded; try restarting transaction
还有一个参数 设置为超时的事务是否回滚 默认OFF 不回滚
show variables like '%innodb_rollback_on_timeout%'
| Variable_name | Value |
|---|---|
innodb_rollback_on_timeout | OFF |
注意: InnoDB 在大部分情况下都不会对异常进行回滚; 需要开发者最好自己声明rollback或者commit
6.7 死锁
6.7.1 死锁概念
死锁: 两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象;不干预的情况下,会一致耗下去;
最简单的解决办法是设置一个超时时间 ,就是上面说的innodb_rollback_on_timeout 一个超时的时候就会释放锁资源; 除了超时解决死锁的问题,还有一种方式: 当前大部分数据库采用 wait-for graph 来检测死锁,一种更加主动的检测死锁的方式 InnoDB页采用这种方式,这个需要数据库保存以下两种信息;
- 锁的信息链表
- 事务等待列表
通过这些信息 可以构造一张图 ,如果图中存在回路 ,就代表有死锁的存在,箭头表示当前等待的状态,没有剪头表示已经存在的锁
在 事务等待列表 Transaction Wait Lists 有4个事务 t1 t2 t3 t4 在处于等待中
事务t1 需要 row1 S锁 处于等待 ==> 事务t2 持有 row1 X 锁 ==> 需要 事务t2 释放 row1 X 锁 t1->t2
事务t2 需要row2 X 锁 处于等待 ==>事务t1 事务t4 持有 row2 S锁 ==>需要 事务t1 事务t4释放 row2 S锁 t2->t1 t2->t4
事务t3 需要row2 X 锁 处于等待 ==>事务t1 事务t4 持有 row2 S锁 ==>需要 事务t1 事务t4释放 row2 S锁 t3->t1 t3->t4
按照顺序 还会有t3->t2 (虽然t2 这个时候还没有X锁)
根据上面关系 会有如下图,存在回路(t1,t2),存在死锁
wait-for graph 采用深度优先算法实现,InnoDB1.2 之前采用递归方式实现 性能相比非递归较差,1.2后采用非递归形式;
6.7.2 死锁概率 (没懂)
至少两次等待才会有死锁的形成 纯数学分析
threads: n+1 ==> transaction : n+1 一个事务:r+1 操作 每个操作是R 中 1 个数据并且占有锁 假设nr<<R(操作数据较小)
每个线程需要持有 r+1 个锁 平均一下; 在其中一个获取锁L的情况下
线程1: 已获得 1个锁 ;对比 获取L锁的概率为 1/(r+1), 线程2: 已获得 2个锁 ;对比 获取L锁的概率为 2/(r+1) ,
已经获取的锁个数: (1+2+…r)/(r+1) 约等于 r/2
每个线程 :每行数据是1/R 等待概率为 nr/2R ;
如果锁长度是2 则 死锁概率 (n2*r4)/(4R2)
6.7.3 死锁的示例
| 顺序 | 结果1 | 会话1 | 会话2 | 结果2 |
|---|---|---|---|---|
| 1 | begin ; | |||
| 2 | select * from demo2 where id = 3 for update ; | |||
| 3 | begin | |||
| 4 | select * from demo2 where id= 5 for update ; | |||
| 5 | 挂起 | select * from demo2 where id= 5 for update ; | ||
| 6 | |select * from demo2 where id = 3 for update ;|Deadlock found when trying to get lock; try restarting transaction 死锁检测` | |||
| 7 | commit ; |
6.8 锁升级
锁升级（Lock Escalation）是指将当前锁的粒度降低。 表的1000个行锁升级为一个页锁，或者页锁升级为表锁。 数据库的设计中认为锁是一种稀有资源，锁会消耗大量的内存空间,而且想避免锁的开销，可以把锁升级,一定程度上提高了效率;
InnoDB存储引擎不存在锁升级的过程;,不是根据记录来生成锁的,是根据每个事务访问的数据页加锁的,不管是一个事务锁住一个记录还是多个记录,开销一般是一致的
比如是一个表有300W页 数据 ,每个数据页100 记录 ,也就是3亿条数据,如果是全表扫描的更新 ,需要全部记录加X锁,如果是根据每行数据的话,10byte/row ==> 3GB 内存,
而InnoDB使用位图(bitmap),一个页上面的记录的所有的锁信息存在bitmap 上面 每页锁需要30 byte* 300W ==> 90MB
扫一扫
2732
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
