深入浅出MySQL系列(2)—MySQL的事务和锁剖析

目录

1.事务

1.1 事务的特性：ACID

1.2 事务使用方法

1.3 事务隔离级别

1）事务并发处理可能会存在异常：

2）事务隔离级别：

2.锁（并发控制问题）

2.1 读写锁

2.2 锁粒度划分

1）全局锁

2）表级锁

3）行锁

---

1.事务

事务： 事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL 中，事务支持是在引擎层实现的。（MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。）

1.1 事务的特性：ACID

1）A，也就是原子性： 一个事务必须被视为一个不可分割的最小工作单元；

2）C，就是一致性（Consistency）：一致性指的就是数据库在进行事务操作后，会由原来的一致状态，变成另一种一致的状态。也就是说当事务提交后，或者当事务发生回滚后，数据库的完整性约束不能被破坏。

3）I，就是隔离性（Isolation）：指的是每个事务都是彼此独立的，不会受到其他事务的执行影响。也就是说一个事务在提交之前，对其他事务都是不可见的。

4）D，指的是持久性（Durability）：事务提交之后对数据的修改是持久性的，即使在系统出故障的情况下，比如系统崩溃或者存储介质发生故障，数据的修改依然是有效的。因为当事务完成，数据库的日志就会被更新，这时可以通过日志，让系统恢复到最后一次成功的更新状态。

1.2 事务使用方法

事务常用语句：

• START TRANSACTION 或者 BEGIN：显式开启一个事务

• COMMIT：提交事务。当提交事务后，对数据库的修改是永久性的。

• ROLLBACK 或者 ROLLBACK TO [SAVEPOINT]，意为回滚事务：意思是撤销正在进行的所有没有提交的修改，或者将事务回滚到某个保存点

• SAVEPOINT：在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。

• RELEASE SAVEPOINT：删除某个保存点。

• SET TRANSACTION：设置事务的隔离级别

事务使用的两种方式：隐式事务和显示事务

隐式事务实际上就是自动提交，Oracle 默认不自动提交，需要手写 COMMIT 命令，而 MySQL 默认自动提交，当然我们可以配置 MySQL 的参数：

mysql> set autocommit =0; // 关闭自动提交

mysql> set autocommit =1; // 开启自动提交

1.3 事务隔离级别

1）事务并发处理可能会存在异常：

1）脏读：读到了其他事务还没有提交的数据。

2）不可重复读：对某数据进行读取，发现两次读取的结果不同，也就是说没有读到相同的内容。这是因为有其他事务对这个数据同时进行了修改或删除。

3）幻读：事务 A 根据条件查询得到了 N 条数据，但此时事务 B 更改或者增加了 M 条符合事务 A 查询条件的数据，这样当事务 A 再次进行查询的时候发现会有 N+M 条数据，产生了幻读。

不可重复读 VS 幻读的区别：

• 不可重复读是同一条记录的内容被修改了，重点在于UPDATE或DELETE

• 幻读是查询某一个范围的数据行变多了或者少了，重点在于INSERT

• 所以，SELECT 显示不存在，但是INSERT的时候发现已存在，说明符合条件的数据行发生了变化，也就是幻读的情况，而不可重复读指的是同一条记录的内容被修改了。

 

2）事务隔离级别：

1）读未提交：允许读到未提交的数据，这种情况下查询是不会使用锁的，可能会产生脏读、不可重复读、幻读等情况。

2）读已提交：只能读到已经提交的内容，可以避免脏读的产生，属于 RDBMS 中常见的默认隔离级别（比如说 Oracle 和 SQL Server），但如果想要避免不可重复读或者幻读，就需要我们在 SQL 查询的时候编写带加锁的 SQL 语句。

3）可重复读：保证一个事务在相同查询条件下两次查询得到的数据结果是一致的，可以避免不可重复读和脏读，但无法避免幻读。（MySQL 默认的隔离级别就是可重复读）通过MVCC解决了幻读的问题；

4）可串行化：将事务进行串行化，也就是在一个队列中按照顺序执行，可串行化是最高级别的隔离等级，可以解决事务读取中所有可能出现的异常情况，但是它牺牲了系统的并发性。

2.锁（并发控制问题）

只要有多个查询需要同一时刻修改数据，都会产生并发控制问题。

2.1 读写锁

从数据库管理的角度可以划分为读锁和写锁；

1）读锁： 读锁锁定的资源可以被其他用户读取，但不能修改。在进行SELECT的时候，会将对象进行读锁锁定，当数据读取完毕之后，就会释放读锁，这样就可以保证数据在读取时不被修改。

注意：可以多个事务对同一个表或行加读锁；

2）写锁： 写锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。

①LOCK TABLE product_comment WRITE; 加写锁命令

②当我们对数据进行更新的时候，也就是INSERT、DELETE或者UPDATE的时候，数据库也会自动使用排它锁，防止其他事务对该数据行进行操作。

意向锁：简单来说就是给更大一级别的空间示意里面是否已经上过锁。

2.2 锁粒度划分

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

1）全局锁

全局锁的典型使用场景是，做全库逻辑备份

①FTWRL加全局读锁，让整个库处于只读状态。其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

语法：Flush tables with read lock

②InnoDB引擎可以用一致性视图代替，但是对于不支持事务的，比如MyISAM这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。备份只能用全局锁。

2）表级锁

MySQL中的表级锁有两种：表锁和元数据锁（MDL）

①表锁

表锁的语法： lock tables … read/write，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

注意：lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

②元数据锁（MDL）（不需要显示加，自动的）

主要是为了表结构更改！

当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

有时候可能出现给一个小表加字段，然后导致整个库挂掉了：

上述session C会被阻塞住，这个没问题，但问题是在在它之后的所有对这个表的请求都会阻塞住（即使是不应该被阻塞的增删改查操作）！

解决办法：在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后，再重复操作

3）行锁

InnoDB支持行锁，而MyISAM 引擎就不支持行锁；

行级锁可以最大程度的支持并发处理，但同时也带来了最大的锁开销。

①两阶段锁协议

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

建议：如果我们的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

（这样可以减少其它事务的等待）

②死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，也就是死锁状态。

死锁是指两个或多个事务在同一资源上的相互占用，并请求对方占用的资源。

死锁处理办法：（两种）

a.死锁超时机制：一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。（不常用：等待时间太久，设置时间太短会造成误伤。）b.主动死锁检测（会消耗CPU性能）：另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。（一般采用这个）

引申：热点数据的更新问题？

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

解决办法：

• 如果我们能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。（有风险）

• 控制并发度

• 将一行改成逻辑上的多行来减少锁冲突