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 不锁表,当在主从同步时,可能产生数据不一致的问题