mysql-事务及锁

目录

目录

事务的隔离级别

锁

 全局锁

应用场景：

风险：

解决办法：

表锁

表锁的应用场景

行锁

事务和锁

---

​​​​​​​

---

事务的隔离级别

当数据库有多个事务提交，可能会出现脏读、不可重复读、幻读等问题

为了解决不同的问题，就出现了不同的隔离级别。包括：

        读未提交： 一个事务还没提交时，他做的变更，可以被其他事务看到

        读已提交：一个事务提交后，他做的变更，才能被其他事务看到      

        可重复读：一个事务执行过程中看到的数据，总是跟事务开启时看到的数据是一致的

        串行化：读会加读锁，写会加写锁，一个事务总是在另一个事务执行完后才能执行

开启事务的两种方式

1、begin /start transaction 后，真正执行sql语句时才开始事务，对应commit或rollback结束事务

2、set autocommit=0，开启事务，对应commit或rollback或断开连接结束事物

锁

 全局锁

加锁命令：flush table with read lock ftwrl    ，会锁住数据更新语句、表结构修改语句、更新类事务的提交

应用场景：

做全库逻辑备份

风险：

1、如果在主库上做备份，后续的更新语句不能执行，影响业务

2、如果在从库上做备份，备份期间，从库不能执行主库同步过来的binlog，导致主从同步延迟

解决办法：

可以使用mysqldump -single-transaction 导数据前会开启一个事务，得到一个一致性视图，只适用于innodb引擎

如果只是为了是库处于只读状态，那可以使用set global readonly=true。这样做有两个风险，所以不建议这样使用

1、有些系统中，readonly会被当作业务逻辑设置， 是从库的设置

2、如果备份期间客户端出了异常，FTWRL会自动释放锁，但是readonly不会发生变更，库会一直处于只读状态

表锁

表级别的锁有两种：表锁、元数据锁

表锁：lock tables ... read/write   unlock tables 

        线程1设置lock tables t1 read,t2 write，那线程1也只能执行读t1，读写t2，其他线程写t1、读写t2会被阻塞

元数据锁：meta lock，mysql5.5版本引入的，自动添加

        读锁之间不互斥

        读写锁之间、写锁之间互斥

        可能出现的问题，在执行ddl期间，有很多的dml，会导致服务端session爆满，也会导致内存升高。

表锁的应用场景

行锁

https://blog.csdn.net/liusiqingsong/article/details/119964221?spm=1001.2014.3001.5501

事务和锁

先讲视图，在mysql中，有两个视图的概念：

1、create view ，它是用一个查询语句创建的虚拟表，创建后的视图，跟表的使用方式是一样的

2、innodb在实现mvcc时用到的一致性视图，用于支持RC（读提交）、RR（可重复读）

innodb会为每个事务创建一个活跃事务列表，（活跃是指启动了还没有提交的事务）。

        最低水位：数组中最小的事务id

        最高水位：数组中最大事务id+1

事务分为三个状态，已经提交、启动未提交、未开始事务

当一个事务启动的瞬间，创建的视图中的数据版本会有多个情况：

1、数据版本的事务已经提交，可见

2、事务号大于高水位，数据版本的事务未开始，不可见

3、数据版本的事务在事务列表中还会细分为以下两种情况

        1、事务在当前活跃列表中，说明事务未提交，不可见

        2、事务不再当前活跃列表中（事务id在高水位和低水位之间），说明事务已经提交，可见

一个数据版本，对于一个事务视图来说，有三种情况

1、事务未提交，不可见

2、已经提交的版本，对当前试图可见

3、创建视图后提交的版本不可见

更新数据时，是先读后写，也就是读取当前版本，成为当前读。select也有当前读，share mode 、for update

关键词：当前读、一致性读、行锁、读已提交、可重复读

问题：当备库用–single-transaction 做逻辑备份的时候，如果从主库的 binlog 传来一个 DDL 语句会怎么样？

1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
3:SAVEPOINT sp;
/* 时刻 1 */
4:show create table `t1`;
/* 时刻 2 */
5:SELECT * FROM `t1`;
/* 时刻 3 */
6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */

1、如果在“时刻 2”到达，则表结构被改过，5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；

2、如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 6 执行完成；

3、如果在 4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构；

4、从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。