一、锁
锁的目的:并发控制,根据加锁的范围,划分为:全局锁、表级锁、行锁。
全局锁
定义:给整个数据库实例加锁
命令:Flush tables with read lock,该命令使得整个库处于只读状态,其他语句被阻塞:数据更新语句、数据定义语句和更新类事务的提交语句。
使用场景:全库逻辑备份。
表级锁
mysql里面表级锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)
1)表锁
语法:lock tables … read/write,可以用unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。
注意点:lock tables语句除了限制别的线程读写外,也限制了本线程接下来操作的对象。
示例:
如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句,则其他线程写 t1、读写 t2 的语句都会被阻塞。同时,线程 A 在执行 unlock tables 之前,也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许,自然也不能访问其他表。
2)元数据锁(MDL)
用法:不需要显式使用,在访问一个表的时候被自动加上,当对一个表做增删改查操作的时候,加MDL读锁,当对表结构进行变更时,加MDL写锁
作用:保证读写的正确性
注意点:
安全给小表加字段,首先需解决长事务,事务不提交,就会一直占着MDL锁,如果要做DDL变更的表刚好有长事务在执行,先暂停DDL执行或者kill掉这个长事务
查看是否有长事务执行:
select * from information_schema.innodb_trx;
行锁
定义:针对数据表中行记录的锁
注意:mysql的行锁是在引擎层由各个引擎自己实现的,并不是所有的引擎都支持行锁,比如MyISAM引擎不支持行锁,Innodb支持行锁。
逻辑:在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放,这个就是两阶段锁协议。
应用注意点:
1)如果事务需要锁多行,要把最可能造成锁冲突,最可能影响并发度的锁的申请时机尽量往后放
2)调整语句顺序并不能完全避免死锁,因此引入了死锁和死锁检测的概念。减少死锁的主要方向,就是控制访问相同资源的并发事务量。
死锁
定义:当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,导致这几个线程进入无限等待
策略:
1)等到直到超时,超时时间可以通过参数innodb_lock_wait_timeout来设置,下图展示设置为50s
show variables like 'innodb_lock_wait_timeout';
2)死锁检测,发现死锁后,主动回滚死锁链条中的一个事务,让其他事务得以继续执行,将参数innodb_deadlock_detect设置为on,表示开启这个逻辑
show variables like 'innodb_deadlock_detect';
二、事务
事务特性
1)原子性:一个事务必须被视为不可分割的最小工作单元,整个事务的所有操作要么全部提交成功,要目全部失败回滚。
2)一致性:数据库总是从一个一致性的状态转换到另一个一致性的状态。
3)隔离性:一个事务所做的修改在最终提交以前,对其他事务是不可见的
4)持久性:一旦事务提交,其所做的修改就会永久保存到数据库中
隔离级别
-
未提交读(READ UNCOMMITTED):
事务中的修改,即使没有提交,对其他事务也是可见的。(事务可以读取未提交的数据,被称为脏读) -
提交读(READ COMMITTED):
一个事务开始时,只能看见已经提交的事务所做的修改。这个级别有时候叫做不可重复读,因为执行两次同样的查询,可能得到不一样的结果。 -
可重复读(REPEATABLE READ):
该级别保证了在同一个事务中多次读取同样记录的结果是一致的,解决了脏读的问题。但是无法解决幻读问题。幻读:某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行。 -
串行化(SERIALZABLE):
强制事务串行执行,避免了前面说的幻读问题。简单来说,串行化会在读取的每一行数据上都加锁,可能导致大量的超时和锁争用的问题。实际应用中很少使用该隔离级别
在mysql的8.0版本中,查看系统配置的隔离级别
show variables like '%transaction_isolation%';
设置隔离级别:
SET SESSION TRANSACTION ISOLATIION LEVLE READ COMMITTED;
事务启动时机:
1、start transaciton,并不是一个事务的起点,一致性视图是在执行第一个快照读语句时创建的
2、start transaction with consistent snapshot,该命令执行后,直接开始事务,无需等第一个sql语句执行。
事务视图原则:
一个数据版本,对于一个事务视图来说,除了自己的更新总是可见以外,有三种情况:
1)版本未提交,不可见;
2)版本已提交,但是是在视图创建后提交的,不可见;
3)版本已提交,而且是在视图创建前提交的,可见
示例:
CREATE TABLE `t` ( `id` int(11) NOT NULL, `k` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
insert into t(id, k) values(1,1);
在隔离级别为可重复读下,三个事务的情况
- 事务A
两次的查询结果是(1,2),原因:
start transaciton,并不是一个事务的起点,一致性视图是在执行第一个快照读语句时创建的:select * from t。事务B的修改版本属于情况1:版本未提交,不可见,事务C的修改版本属于情况3:版本已提交,而且是在视图创建前提交的,可见,所以第一次结果是(1,2),等到第2次查询时,事务B的修改版本属于:版本已提交,是在视图创建后提交的,不可见,因此2次的查询结果为(1,2) - 事务B
第一次查询结果是(1,1),第二次更新时被锁定,直到事务C提交,才执行完成,第2次查询结果是(1,3)。
原因:第1次查询时,事务C的修改版本属于情况1:版本未提交,不可见,因此查询结果为(1,1),第2次更新时被锁定,直到事务C提交,才执行完成,第2次查询时,查询结果为(1,3)原因是:更新数据都是先读后写的,而这个读,只能读当前的值,称为“当前读”(current read),事务C已经将id为1的值修改为2,因此事务B的更新是在2的基础上加1,因此结果为3 - 事务C
第一次查询结果是(1,1),提交成功
换一种启动方式:
事务A的2次查询结果为(1,1),事务B和事务C的保持不变
原因:事务A在启动时就创建了一致性视图,非等到第一个sql执行时,第一次查询结果,事务B属于:版本未提交,不可见,事务C属于:版本已提交,在创建视图后提交,不可见,因此结果为(1,1),第2次查询结果,事务B属于:版本已提交,在创建视图后提交,不可见,事务C属于:版本已提交,在创建视图后提交,不可见,因此2次的查询结果为(1,1)