mysql各种锁
根据级别分为:全局锁,表级锁,页级锁,间隙锁,临键锁,行级锁
根据锁共享策略分为:共享锁,排他锁,意向共享锁,意向排他锁
根据加锁策略分为:乐观锁,悲观锁
其他锁相关:自增锁,mdl锁
全局锁
全局锁可以将整个数据库实例加锁:
锁住整个数据库,只允许读取数据
方法一:
mysql> flush tables with read lock; //加锁 Query OK, 0 rows affected (0.05 sec) mysql> unlock tables; //释放锁 Query OK, 0 rows affected (0.00 sec)
此方法发生客户端断开后会自动释放锁
方式二:
mysql> set global read_only=on;//加锁 Query OK, 0 rows affected (0.00 sec) mysql> set global read_only=off; //释放锁 Query OK, 0 rows affected (0.00 sec) mysql> show global variables like "%read_only%"; //查看锁 +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb\_read\_only | OFF | | read_only | OFF | | super\_read\_only | OFF | | transaction\_read\_only | OFF | +-----------------------+-------+ 4 rows in set (0.00 sec)
此方法不能锁住 super用户,在连接断开也会保持锁
表锁
表锁可对一个表或多个表进行加锁.
加锁:
mysql> lock tables user_list read;//加读锁,只允许读取数据,当前连接不允许写入,但是允许读取, //其他连接不允许读取,不允许写入 Query OK, 0 rows affected (0.00 sec) mysql> select userId,password from user_list limit 1; +--------+----------+ | userId | password | +--------+----------+ | 1821 | NULL | +--------+----------+ 1 row in set (0.00 sec) mysql> update user_list set userId=1; 1099 - Table 'user_list' was locked with a READ lock and can't be updated mysql> unlock tables;//释放锁 Query OK, 0 rows affected (0.00 sec) mysql> lock tables user_list write;//加写锁,只允许写入数据,当前连接可读取可写入,其他进程不允许读取不允许写入 Query OK, 0 rows affected (0.00 sec)
注意:unlock tables;将释放当前连接的所有表锁.
mdl锁
为了防止在事务中出现表结构操作,导致事务无法保证前后一致性问题,mysql增加了 （meta data lock，MDL) 锁.
在操作一个表数据时,会自动加上 MDL读锁,在变更表结构或者删除表时,会自动加上 MDL写锁
页级锁
只有RDB引擎才支持页级锁
锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折中的页级锁，一次锁定相邻的一组记录。
这个我也不会,没法演示
间隙锁Gap Lock
间隙锁将锁定一个范围,但不包括记录本身.作用是为了防止同一事务下2次读出现幻读的情况,该锁只会在隔离级别为RR(可重复读,序列化) 时存在.
什么是幻读
假设数据库数据为:
id主键
b 索引
c
1
2
3
5
6
8
10
9
13
当启用事务时:
事务1
事务2
事务3
Q1: begin;
查询 b=6的数据,for update 只有一条数据
Q2: 将id为1的b数据更新为6
Q3:查询b=6的数据 这里变成了2条
Q4:插入一条b=6的数据
Q5:查询b=6的数据 这里变成了3条
在这3个事务中,由于事务级别为重复读,理应Q3,Q5数据都一样,在Q1中,应该会查出b=6的数据并锁住,但是Q2的中,id为1的数据2,并不涉及锁,同时Q3是新增一条数据,无法锁住
行锁只能根据索引锁住存在的数据,如果数据不存在时,将无法锁住,就会导致出现在可重复读时却出现幻读的情况,所以mysql引入了间隙锁
间隙锁如何解决幻读
在插入数据时,mysql额外增加了间隙锁的概念,在插入表数据后,会生成 "前开后闭"的间隙区间:
-∞,2
2,6
6,9
9,∞
当给b=6加锁时,其实是给2,6和6,9 间隙加锁,保证此2个间隙不会数据不会被删除,增加数据导致数据不一致
主键条件或者唯一索引下:
如果通过不存在的主键索引加锁,将会通过主键进行间隙锁加锁
如果通过存在的主键范围加锁,将会通过主键进行间隙锁加锁
如果通过存在的主键直接精确查找加锁,将只会增加行锁
普通索引下:
如果在存在的索引中加锁,将会在索引2边进行间隙锁加锁
如果在不存在的索引中加锁,将会在此索引间隙中加锁
行锁
行锁是在索引记录上的锁,当表中没有索引时,innodb会自动创建个隐藏主键索引用于做行锁.
在操作数据时,将自动给此条数据加锁:
临键锁
临键锁就是 行锁+间隙锁的组合
共享锁和排他锁
共享锁(S锁) 排它锁(X锁)其实就是我们说的读锁和写锁
在查询语句中,mysql自动加 S锁,其他连接也只能加S锁查询,不能加写锁更新
在insert,update,delete 语句中,将自动加 X锁,其他语句不能对锁住的数据操作(包括读取)
意向锁
意向锁是mysql表级锁,
当需要对数据进行加 共享锁/排他锁时,需要先对此表进行加意向(共享/排他)锁
此锁为mysql自动增加,无需用户干预
乐观锁和悲观锁
乐观锁 是指业务层面上,对修改数据不会冲突的情况做出的乐观判断,先进行更新数据,再进行判断.需要自己实现,
例如:
扣除用户A的金额
开启事务.
声明一个版本号更新到用户A记录中
先进行扣除金额
再判断版本号是否和当前生成的版本号一致
如果不一致则回滚,一致则提交
悲观锁是指在业务层面上,对修改数据抱有一定会冲突的考虑进行事先加锁,共享锁/排它锁 都可以认为是悲观锁
自增锁
如果表存在自增字段,则mysql会额外增加一个自增锁用于控制自增数.
自增锁的配置 innodb_autoinc_lock_mode 3种模式
0:traditonal 表锁,每次都会产生表锁
1:consecutive 轻量锁,innodb默认,正常插入获得批量的锁,可以保证连续插入
2:interleaved 不锁表,当在主从同步时,可能产生数据不一致的问题
