mysql知识点

一、锁

锁的目的：并发控制，根据加锁的范围，划分为：全局锁、表级锁、行锁。

全局锁
定义：给整个数据库实例加锁
命令：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）持久性：一旦事务提交，其所做的修改就会永久保存到数据库中

隔离级别

1. 未提交读（READ UNCOMMITTED）:
   事务中的修改，即使没有提交，对其他事务也是可见的。（事务可以读取未提交的数据，被称为脏读）

2. 提交读（READ COMMITTED）：
   一个事务开始时，只能看见已经提交的事务所做的修改。这个级别有时候叫做不可重复读，因为执行两次同样的查询，可能得到不一样的结果。

3. 可重复读（REPEATABLE READ）:
   该级别保证了在同一个事务中多次读取同样记录的结果是一致的，解决了脏读的问题。但是无法解决幻读问题。幻读：某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。

4. 串行化（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);

在隔离级别为可重复读下，三个事务的情况

1. 事务A
   两次的查询结果是（1,2），原因：
   start transaciton，并不是一个事务的起点，一致性视图是在执行第一个快照读语句时创建的：select * from t。事务B的修改版本属于情况1：版本未提交，不可见，事务C的修改版本属于情况3：版本已提交，而且是在视图创建前提交的，可见，所以第一次结果是（1,2），等到第2次查询时，事务B的修改版本属于：版本已提交，是在视图创建后提交的，不可见，因此2次的查询结果为（1,2）
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
3. 事务C
   第一次查询结果是（1,1)，提交成功

换一种启动方式：

事务A的2次查询结果为（1,1）,事务B和事务C的保持不变
原因：事务A在启动时就创建了一致性视图，非等到第一个sql执行时，第一次查询结果，事务B属于：版本未提交，不可见，事务C属于：版本已提交，在创建视图后提交，不可见，因此结果为（1,1），第2次查询结果，事务B属于：版本已提交，在创建视图后提交，不可见，事务C属于：版本已提交，在创建视图后提交，不可见，因此2次的查询结果为（1,1）