事务控制 隔离级别 表锁 行锁 间隙锁的使用
一、事务控制
事务的概念:
一个或一组sql语句组成一个执行单元,这个执行单元作为一个不可分割的整体,要么全部成功,要么就全部失败.
事务的ACID属性:
- 隐式事务: 事务没有明显的开启和结束的标记
比如insert update delete -
显式事务: 事务具有明显的开启和结束的标记.
前提: 必须设置自动提交功能为禁用
1. 事务的创建:
默认是开启状态.
# 步骤1: 开启事务
set autocommit=0;
start trasaction; #(可以不写)
# 步骤2: 编写事务中的sql语句(select insert update delete)
语句1
语句2
...
# 步骤3: 结束事务
commit; 提交事务
rollback; 回滚事务
savepoint 保存点名; 设置保存点
rollback to 保存点名; 回滚至保存点
2. 并发事务
# 事务的并发问题是如何发生的
多个事务同时操作同一个数据库的相同数据时.
# 事务的并发问题有哪些
脏读.不可重复读.幻读.
# 事务的并发问题如何解决
通过设置隔离级别来实现解决并发问题.
二、事务的隔离级别
# 4种级别:
READ-UNCOMMITTED 读未提交
READ-COMMITTED 读已提交
repeatable-read 可重复读 (默认级别)
serializable 串行化 (效率太低)
# 查看默认的隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
# 修改隔离级别:
set session transaction isolation level read uncommitted;
select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1.READ-UNCOMMITTED
读未提交下的 脏读和幻读
2.READ-COMMITTED
读已提交下的 不可重复读
3.repeatable-read
可重复读 下的幻读情况.
4.serializable
串行化情况下锁表.
5.解决方案
## 注意 ！！！设置当前的全局变量会立即生效. 设置global全局变量需要重启生效.
6.设置成RR级别
- 防止不可重复读现象
利用的就是Undo的一致性快照,MVCC重要功能. - 通过RR,可以解决99以上的幻读问题,为了更加严谨,加入了GAP锁,nex-lock下一键锁.
三、MySQL锁机制
1.锁的定义
锁就是计算机协调多个进程或线程并发访问某一资源的机制.
- 保证事务的隔离性和一致性
- 锁是属于资源的,不是事务本身的特性.
每次事务需要资源的时候,需要申请持有资源的锁. - 生活场景: 购物网站的库存剩余与结算的问题.
2.锁的分类
1.）按资源类型分
- 内存锁:
- mutex锁,latch锁 : 包含内存数据不被置换或清空
- 对象锁粒度:
- MDL锁(元数据锁) : 修改元素时.(alter) DDL,备份
- Table_lock(表锁) : DDL,备份(全局表锁),手动lock table
- recorded(row) lock(行锁) : 锁定聚簇索引
- GAP间隙锁 : RR级别下,普通辅助索引间隙锁(非唯一性锁)
- Next_lock : GAP + row lock,普通辅助索引的范围锁
2.）按功能分类
- IS : 意向共享锁 表级别
- IX : 意向排他锁 表级别
- S : 共享锁 读锁 行级别
- X : 排他锁 写锁 行级别
3.）按对数据的操作类型分:
- 读锁
- 写锁
4.）按对数据操作粒度分:
- 表锁:：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
- 行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
- 页锁(很少)：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
在 InnoDB 中实现了两个标准的行级锁，可以简单的看为两个读写锁：
S 共享锁：又叫读锁，其他事务可以继续加共享锁，但是不能继续加排他锁。
X 排他锁：又叫写锁，一旦加了写锁之后，其他事务就不能加锁了。
四、表级锁
MySQL 由连接池组件、管理服务和工具组件、SQL 接口组件、查询分析器组件、优化器组件、 缓冲组件、插件式存储引擎、物理文件组成。
在 MySQL 中存储引擎是以插件的方式提供的，在 MySQL 中有多种存储引擎，每个存储引擎都有自己的特点。
# 可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：
mysql> show status like 'table%';
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| Table_locks_immediate | 1000267 |#产生锁表次数
| Table_locks_waited | 0 |#表锁争用等待次数
| Table_open_cache_hits | 500028 |
| Table_open_cache_misses | 3 |
| Table_open_cache_overflows | 0 |
+----------------------------+---------+
如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。
1.手动锁表
# 锁表的语法
lock table 表名 read(write),表名2 read(write), 其他;
#查看上锁的表
show open tables;
例子:
mysql> lock table test1 read,test2 write;
mysql> show open tables like 'test%' ;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| bigdata | test1 | 1 | 0 |
| bigdata | test2 | 1 | 0 |
+----------+-------+--------+-------------+
# 解锁(解锁所有表)
方式一
unlock tables;
方式二
show processlist;
找到锁进程，kill id ;
-----
mysql> show processlist;
+----+-------------+-----------+---------+---------+
| Id | User | Host | db | Command |
+----+-------------+-----------+---------+---------+...
| 1 | system user | | NULL | Connect |
| 2 | system user | | NULL | Connect |
| 12 | root | localhost | bigdata | Query |
+----+-------------+-----------+---------+---------+...
mysql> kill 12;
ERROR 1317 (70100): Query execution was interrupted
mysql> show status like 'table%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 13
Current database: bigdata
该方式会终止root进程后重新连接database
# 锁表应用
锁定数据表，避免在备份过程中，表被更新
mysql>LOCK TABLES tbl_name READ;
为表增加一个写锁定：
mysql>LOCK TABLES tbl_name WRITE;
2.加读锁的情况
mysql>LOCK TABLES mylock READ;
- 本session可读.不可写.并且不能读其他表
- 其他session可读,但是写阻塞等待.可以查询更新其他表.
3.加写锁的情况
mysql>LOCK TABLES mylock write;
- 本session可读.可写. 不能读其他表
- 其他session读阻塞等待,写阻塞等待.可以查询更新其他表.
4.表读锁和写锁的小结
# 简答来说,读锁会阻塞写,不会阻塞读.
# 写锁会同时阻塞写操作和读操作.
五、行级锁
# innodb存储引擎
set autocommit=0;
# 可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0 |
| InnoDB_row_lock_time | 0 |#等待总时长
| InnoDB_row_lock_time_avg | 0 |#等待平均时长
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |#等待总次数
+-------------------------------+-------+
5 rows in set (0.01 sec)
如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，发现锁争用比较严重.
1.行锁会升级表锁故障
## 重要,索引失效,行锁会升级为表锁.(重要的生产故障)
# 1.类型不匹配，隐式转换
例子1:
其中ab都已经建立索引,a为int类型,b为varchar类型
mysql> update 表1 set a=41 where b=4000;
"注意此处b=4000中的4000没有加引号,mysql会做隐式处理,然后索引失效,行锁升级表锁. 导致其他用户阻塞."
# 2.range范围过大，自动从局部升级为全表扫描。
例子2:
表(id,num)中数据辅助索引列num为1到50号,
如果执行: desc update 表 set id=10 where num<10;
+----+-------------+-------+------------+-------+
| id | select_type | table | partitions | type |
+----+-------------+-------+------------+-------+
| 1 | SIMPLE | user | NULL | range |
+----+-------------+-------+------------+-------+
此时type是range,执行的是GAP间隙锁范阻塞围1-9,
如果执行: update 表 set id=10 where num<30;
"当执行的范围超过一定比例,默认索引就从range级别变成了full全表扫描级别.查看mysql> select * from sys.innodb_lock_waits\G
发现此时的锁变成了全表行锁.这时就会导致其他用户阻塞."
# 查看具体锁阻塞可以用以下命令
mysql> select * from sys.innodb_lock_waits\G
- 行锁定演示：
2.如何锁定一行
# 锁定某一行语法:
select xxx.. for update;
3.间隙锁
间隙锁（Gap Lock）是Innodb在提交下为了解决幻读问题时引入的锁机制，（下面的所有案例没有特意强调都使用可重复读隔离级别）幻读的问题存在是因为新增或者更新操作，这时如果进行范围查询的时候（加锁查询），会出现不一致的问题，这时使用不同的行锁已经没有办法满足要求，需要对一定范围内的数据进行加锁.
间隙锁就是解决这类问题的。在可重复读隔离级别下，数据库是通过行锁和下一键锁（next-key lock）来组成间隙锁的.
加锁规则有以下特性，我们会在后面的案例中逐一解释：
1.加锁的基本单位是（next-key lock）,他是前开后闭原则
2.插叙过程中访问的对象会增加锁
3.索引上的等值查询--给唯一索引加锁的时候，next-key lock变为行锁
4.索引上的等值查询--向右遍历时最后一个值不满足查询需求时，next-key lock 变为间隙锁
5.唯一索引上的范围查询会访问到不满足条件的第一个值为止
案例一：间隙锁简单案例
案例数据
| id(主键) | c（普通索引） | d（无索引） |
|---|---|---|
| 5 | 5 | 5 |
| 10 | 10 | 10 |
| 15 | 15 | 15 |
| 20 | 20 | 20 |
| 25 | 25 | 25 |
| 步骤 | 事务A | 事务B |
|---|---|---|
| 1 | begin; select * from t where id = 11 for update; | - |
| 2 | - | insert into user value(12,12,12) |
| 3 | commit; | - |
当有如下事务A和事务B时，事务A会对数据库表增加（10，15]这个区间锁，这时insert id = 12 的数据的时候就会因为区间锁（10，15]而被锁住无法执行。
案例二： 间隙锁死锁问题
| 步骤 | 事务A | 事务B |
|---|---|---|
| 1 | begin; select * from t where id = 9 for update; | - |
| 2 | - | begin; select * from t where id = 6 for update; |
| 3 | - | insert into user value(7,7,7) |
| 4 | insert into user value(7,7,7) |
- |
不同于写锁相互之间是互斥的原则，间隙锁之间不是互斥的，如果一个事务A获取到了（5,10]之间的间隙锁，另一个事务B也可以获取到（5,10]之间的间隙锁。事务A获取到（5,10]之间的间隙锁不允许其他的DDL操作，在事务提交，间隙锁释放之前，事务B也获取到了间隙锁（5,10]，这时两个事务就处于死锁状态.
# 查看死锁情况:
mysql> show engine innodb status \G
mysql> show variables like '%deadlock%';
# 通过配置记录死锁的日志:
vim /etc/my.cnf
innodb_print_all_deadlocks=1
案例三： 等值查询—唯一索引
| 步骤 | 事务A | 事务B | 事务C |
|---|---|---|---|
| 1 | begin; update u set d= d+ 1 where id = 7; | - | - |
| 2 | - | insert into u (8,8,8); |
- |
| 4 | - | - | update set d = d+ 1 where id = 10 |
1.加锁的范围是（5,10]的范围锁
2.由于数据是等值查询，并且表中最后数据id = 10 不满足id= 7的查询要求，故id=10 的行级锁退化为间隙锁，（5,10）
3.所以事务B中id=8会被锁住，而id=10的时候不会被锁住
案例四： 等值查询—普通索引
| 步骤 | 事务A | 事务B | 事务C |
|---|---|---|---|
| 1 | begin; select id form t where c = 5 lock in share mode; | - | - |
| 2 | - | update t set d = d + 1 where id = 5 | - |
| 4 | - | - | insert into values (7,7,7) |
1.加锁的范围是（0,5]，（5,10]的范围锁
2.由于c是普通索引，根据原则4，搜索到5后继续向后遍历直到搜索到10才放弃，故加锁范围为（5,10]
3.由于查询是等值查询，并且最后一个值不满足查询要求，故间隙锁退化为（5,10）
4.因为加锁是对普通索引c加锁，而且因为索引覆盖，没有对主键进行加锁，所以事务B执行正常
5.因为加锁范围（5,10）故事务C执行阻塞
6.需要注意的是，lock in share mode 因为覆盖索引故没有锁主键索引，如果使用for update 程序会觉得之后会执行更新操作故会将主键索引一同锁住
案例五： 范围查询—唯一索引
| 步骤 | 事务A | 事务B | 事务C |
|---|---|---|---|
| 1 | begin; select * form t where id >= 10 and id <11 for update | - | - |
| 2 | - | insert into values(8,8,8) insert into values(13,13,13) |
- |
| 4 | - | - | update t set d = d+ 1 where id = 15 |
1.next-key lock 增加范围锁（5,10]
2.根据原则5，唯一索引的范围查询会到第一个不符合的值位置，故增加（10，15]
3.因为等值查询有id =10 根据原则3间隙锁升级为行锁，故剩余锁[10,15]
4.因为有id<11查询并不是等值查询，故[10,15]不会退化成[10,15)
5.故事务B（13,13,13）阻塞，事务C阻塞
案例六： 范围查询—普通索引
| 步骤 | 事务A | 事务B | 事务C |
|---|---|---|---|
| 1 | begin; select * form t where c >= 10 and c <11 for update | - | - |
| 2 | - | insert into values(8,8,8) |
- |
| 4 | - | - | update t set d = d+ 1 where c = 15 |
1.next-key lock 增加范围锁（5,10]，（10，15]
2.因为c是非唯一索引，故（5,10]不会退化为10
3.因为查询并不是等值查询，故[10,15]不会退化成[10,15)
4.所以事务B和事务C全部堵塞
案例七： 普通索引-等值问题
上面的数据增加一行（30,10,30），这样在数据库中存在的c=10的就有两条记录
| 步骤 | 事务A | 事务B | 事务C |
|---|---|---|---|
| 1 | begin; delete from t where c = 10 | - | - |
| 2 | - | insert into values(12,12,12) |
- |
| 4 | - | - | update t set d = d+ 1 where c = 15 |
1.next-key lock 增加范围锁（5,10]，（10，15]
2.因为是等值查询故退化为（5,10]，（10，15），故事务B阻塞，事务C执行成功.
加锁的范围如下图
案例八： 普通索引-等值Limit问题
| 步骤 | 事务A | 事务B | 事务C |
|---|---|---|---|
| 1 | begin; delete from t where c = 10 limit 2 | - | - |
| 2 | - | insert into values(12,12,12) |
- |
| 4 | - | - | update t set d = d+ 1 where c = 15 |
1.根据上面案例8改造，将delete增加limit操作2的操作
2.因为知道了数据加锁值加2条，故在加锁（5，10]之后发现已经有两条数据，故后面不在向后匹配加锁。所以事务B执行成功，加锁范围如下
- 所以变更数据时要减少范围查询的使用.
