MySQL - 聊聊数据库事务（1）

shadow__
456 阅读11分钟

认识事务

理解： 我的理解是获取一个Connection（会话）进行一系列操作的过程，包括我们所说的DML操作，增、删、改、查。如果我们获取多个Connection（会话），那就是多个事务。

前提： MySQL实现数据库的前提是基于InnoDB存储引擎，默认的存储引擎MyISAM不支持事务。

目的： 保证数据的安全性。

事务提交方式

（1）自动提交：每执行一条SQL语句，就同步到数据库中（MySQL默认的事务提交方式）。
（2）手动提交：执行一系列的SQL语句后一块儿同步到数据库中。

MySQL手动提交事务设置

1.查询事务是否为自动提交
SHOW VARIABLES like '%autocommit%'    -- ON为自动提交  OFF为手动提交

2.临时开启手动提交（注意：这种方式开启的手动提交只在一个会话中有效，会话关闭或重启MySQL事务提交方式将恢复默认。以Navicat为例，一个SQL编辑窗口为一个会话）
set @@autocommit=0;

3.永久开启手动提交
https://www.cnblogs.com/CanBlog/p/10711785.html

事务操作

# 事务的开启
start transaction;
sql语句  涉及删除 更新 插入 等操作

# 保存节点
savepoint 节点名;

# 回滚到某个节点
rollback to 节点名; 

# 回滚（取消全部事务）
rollback; 

# 提交
commit;

需要注意的是（1）事务提交之后回滚是无效的，数据不会发生改变。（2）一些客户端工具例如Navicat在未提交事务前关闭了会话（编辑窗口），事务将回滚。

事务的四大特性(ACID)

A(atomic)：原子性，一个事务中的所有操作,要么都成功，要么都失败；（比如A向B转账100，涉及到两个操作，A账户减100，B账户加100，但在A账户减100这个操作后停电了，则此时这个操作就要回滚）
C(consistency)：一致性，一个事务在执行前后的数据状态必须满足完整性约束。数据库完整性主要体现在：
实体完整性（如行的主键存在且唯一）
列完整性（如字段的类型，大小，长度符合要求）
外键约束（外键约束还存在）
用户自定义完整性（如转账前后，两个账户的和应该是不变的）
（比如A,B转账的总量为100元，即A+B=100,如果A变了，B也要随之改变。）
I(Isolation)：隔离性，多个事务操作之间是相互隔离,互不影响的；（比如A在取钱的过程当中，B不能向A转账）
D(Durability):持久性，事务一旦提交,数据表就发生永久改变.安全。（比如B收到A转账100,B账户加100，事务结束后，数据从内存同步到磁盘不会因为系统故障造成数据丢失）

其实“一致性”是事务追求的终极目标，上面的原子性，隔离性，持久性都是为了保证数据库状态的一致性。

不考虑事务的隔离性引发的问题

说明下事务的隔离性，当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，在介绍事务的隔离级别之前，我们先看看如果不考虑事务的隔离性会引发几种问题

（1）脏读: 指在一个事务处理过程里读取了另一个未提交的事务中的数据。
当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下:

 update account set money=money + 100 where name=’B’;  (此时A通知B)
 update account set money=money - 100 where name=’A’;

当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

（2）不可重复读: 指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据内容（侧重 【某一行数据】发生了变化）。
这是由于在查询间隔，被另一个事务修改并提交了。
在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数据A和B依次查询就可能不同，A和B就可能打起来了……　
注：不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据.

MySQL演示“不可重复读现象”\color{#FF0000} MySQL演示“不可重复读现象”

创建会话窗口A,会话窗口B,用来模拟两个并发事务

  1. 首先往数据库表中插入一条数据

image.png

  1. 会话窗口A,我们设置了事务级别为“读已提交”，这样会引起不可重复读问题，设置手动提交，开启事务，然后查询该条数据，但是未提交事务！选中执行：

image.png

会话窗口A在开启事务后第一次查询id为1的数据，但未提交。

  1. 跳转到会话窗口B,设置事务级别为“读已提交”,设置手动提交，开启事务，修改id为1的数据，提交事务：

image.png

  1. 跳转到会话窗口A，在事务中执行第二次查询，选中执行：

image.png

会话窗口A在事务开启后第二次查询：id为1的数据内容出现变化，说明出现了不可重复读现象。

（3）幻读（读取结果集条数的对比）: 一个事务按相同的查询条件查询之前检索过的数据，发现检索出来的结果集条数变多或者减少（由其他事务插入、删除的），类似产生幻觉。
例如两个事务T1、T2。T1事务过程中从表中读取数据，然后T2事务进行了INSERT操作并提交，当T1再次读取的时候，结果不一致的情况发生。
注：幻读和不可重复读的区别是，不可重复读指： 同一行数据（数据内容）发生了变化；幻读指：行数量发生了变化； 其实“幻读”也可作为“不可重复读”的一种特殊情况。

MySQL的四种隔离级别

针对不考虑事务隔离性引发的问题，SQL标准定义了4类隔离级别，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

Read Uncommitted（读未提交）：最低级别，任何情况都无法保证。

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed（读已提交）：可避免脏读的发生。

这是大多数数据库系统的默认隔离级别（不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read（可重复读）：可避免脏读、不可重复读的发生。

MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（串行化）：最高级别，可避免脏读、不可重复读、幻读的发生。

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

MySQL常用存储引擎的锁机制

数据库使用锁是为了支持更好的并发，提供数据的完整性和一致性。

  1. InnoDB：支持行级锁和表级锁，默认是行级锁
  2. BDB：支持页级锁和表级锁，默认是页级锁
  3. MyISAM,Memory：这两个存储引擎都是采用表级锁
    这里主要针对InnoDB讲一些常见的锁。

一. 共享锁和排它锁

共享锁： 又叫读锁，如果事务T对A数据加上共享锁，则其它事务只能对A数据只能加共享锁，不能加其它锁。已获取共享锁的事务只能读数据不能写数据。

用法：SELECT ... LOCK IN SHARE MODE;

情景分析：
（1）事务T1开启事务，对A数据加上共享锁，选中执行，然后事务T2对数据A加上排他锁。

1.事务T1加上共享锁后正常查询出结果
image.png

2.然后事务T2加上排它锁后执行，却出现阻塞，超时后报错。 image.png

3.如果事务T2加上共享锁后执行，查询正常。
image.png

（2）事务T1开启事务，对A数据加上共享锁，选中执行，然后事务T2对数据A加上排他锁，再事务T1对数据A做更新操作（即去获取排它锁）。

1.事务T1加上共享锁后正常查询出结果

image.png

2.然后事务T2加上排它锁后执行，却出现阻塞状态，，，

image.png

3.在事务T2等待期间，立马事务T1对数据A做更新操作，结果出现事务T2出现死锁，系统则通知事务T2释放锁，T1事务执行更新操作成功。
T2:
image.png
T1:
image.png

（3）验证“已获取共享锁的事务只能读数据不能写数据”，这里要模拟两个事务并发场景，否则还是可以写数据。

  1. 事务T1加上共享锁
    image.png

2.然后事务T2也加上共享锁
image.png

3.然后事务T1再执行更新操作，结果阻塞。
image.png

排它锁： 又叫写锁，如果事务T对A数据加上排它锁，则其它事务都不能对A数据加任何类型的锁。已获取排它锁的事务既能读数据又能写数据。
注意: 排他锁不是说锁住一行数据后，其他事务就不能读取和修改该行数据。而是指的是一个事务在一行数据加上排他锁后,其他事务不能再在其上加其他的锁。MySQL InnoDB引擎默认的修改数据语句,update,delete,insert都会自动给涉及到的数据加上排他锁,select语句默认不会加任何锁类型,如果加排他锁可以使用select ...for update语句，加共享锁可以使用select ... lock in share mode语句。

用法：SELECT ... FOR UPDATE

情景分析：
（1）事务T1开启事务，对A数据加上排它锁,事务T2对A数据无论加排他锁或共享锁，都将出现等待，最后超时。
T1:

image.png

T2:

image.png

image.png

二. 行级锁、表级锁和页级锁

行级锁：行级锁分为共享锁和排它锁。行级锁是Mysql中锁定粒度最细的锁。InnoDB存储引擎支持行级锁和表级锁，只有在通过索引条件检索数据的时候，才使用行级锁，否就使用表级锁。行级锁开销大，加锁慢，锁定粒度最小，发生锁冲突概率最低，并发度最高。

表级锁：表级锁分为表共享锁和表独占锁。表级锁开销小，加锁快，锁定粒度大、发生锁冲突最高，并发度最低。

页级锁：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁。
开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

avatar
文章
阅读
粉丝