MySQL_事务

---

版权声明：感谢博主 https://blog.csdn.net/WYpersist/article/details/80151865

---

删除一行记录

事物简介和事物四个特性

1、事务概念引入：

现实生活中，我们往往经常会进行转账操作，转账操作可以分为两部分来完成，转入和转出。只有这两部分都完成了才可以认为是转账成功。在数据库中，这个过程是使用两条语句来完成的，如果其中任意一条语句出现了异常没有执行，则会导致两个账号的金额不同步，造成错误。

为了防止上面可能出现的情况，MySQL引入了事务，所谓事务就是针对数据库的一组操作，它可以由一条或者多条SQL语句组成，同一个事务的操作具备同步的特点，如果其中有一条语句不能执行的话，那么所有的语句都不会执行，也就是说，事务中的语句要么都执行，要么都不执行。

开启事务
在使用数据库时候需要使用事务，必须先开启事务，开启事务的语句具体如下：

start transaction；

提交事务
上面语句是用来开启事务，事务开启之后就可以执行SQL语句，SQL语句执行成功之后，需要使用相应语句提交事务，提交事务的语句如下：

commit；

需要注意的是，在MySQL中直接书写的SQL语句都是自动提交的，而事务中的操作语句需要使用【commit语句手动提交】，只有事务提交后其中的操作才会生效。

如果不想提交事务，我们还可以使用相关语句【取消事务】（也称回滚），具体语句如下：

rollback；

需要注意的是，rollback语句只能针对【未提交的事务】执行的回滚操作，已经提交的事务是不能回滚的。

2、事务操作具有严格的定义，它必须满足ACID：

ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

（1）原子性： 原子性是指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

（2）一致性：一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

（3）隔离性：隔离性是指并发的事务是相互隔离的。即一个事务内部的操作及正在操作的数据必须封锁起来，不被企图进行修改的事务看到 。

（4）持久性：持久性是指在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。 即使出现了任何事故比如断电等，事务一旦提交，则持久化保存在数据库中。

3、通过一个转账的案例演示如何使用事务

（1）演示之前准备工作：

具体SQL语句如下：

create database chapter06;
use chapter06;
create table account(
    id int primary key auto_increment,
    name varchar(40),
    money float
);
insert into account(name,money) values('a',1000);
insert into account(name,money) values('b',1000);

（2）接下来我们使用事务来演示如何实现转账功能：

首先开启一个事务，然后通过update语句将 a账户的100元 转给 b 账户，然后最后提交事务，具体语句如下：

start transaction;
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
commit;

事务提交

1、现实生活中，许多操作都是需要用户确认的，例如用户删除一个文档，删除时候会弹出一个提示对话框，包含"确认"和"取消"。同样的道理，在数据库中有些命令在使用的时候需要被确认的，例如事务中的操作就需要用户的确认，当用户确认提交了，事务操作才能被执行，这个过程就是手动提交的过程。

接下来我们针对事务提交进行详细讲解：

在上一个笔记的案例基础上，现在此时a账户有900元，b账户有1100元，开启一个事务，让a账户转100元给b账户，具体语句如下：

start transaction;
update account set money=money+100 where name='a';
update account set money=money-100 where name='b';

从上面结果来看，事务成功实现了转账。退出数据库，重新进入查询，如下：

从上面结果来看，说明我们转账是没有成功的。

这里就需要进行事务提交：

start transaction;
update account set money=money+100 where name='a';
update account set money=money-100 where name='b';
commit;

运行成功之后，退出数据库，重新登陆查询，如下：

事务滚动

1. 在操作事务的时候，如果发现当前事务操作是不合理的，此时只要还没有提交事务，就可以通过回滚取消当前事务，接下来就针对事务的回滚进行详细讲解。

2. 为了演示回滚操作，在上一个笔记案例基础之上，此时a账户有1000元，b账户也有1000元，开启一个事务，通过update语句将a账户的100元转给b账户，具体语句，如下：

start transaction;
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';

上述语句执行成功之后，使用select语句查询a和b账户，如下：

此时a账户如果不想给b账户转账了，由于事务还没有提交，还是可以事务回滚的，如下：

事物隔离级别

1、事务的隔离级别引入：

数据库是多线程并发访问的，所以很容易出现多个线程同时开启事务的情况，这样的就会出现脏读、重复读以及幻读的情况。在数据库操作中，为了有效保证并发读取数据的正确性，需要为事务设置隔离级别。在MySQL中，事务有4种隔离级别，如下：

（1）Read Uncommitted

Read UnCommitted（读未提交）是事务中最低的级别，该级别下的事务可以读取到另一个事务中未提交的数据，也被称为脏读（Dirty Read），这是相当危险的。由于该级别较低，在实际开发中避免不了任何情况，所以一般很少使用。

（2）Read Committed

大多数的数据库管理系统的默认隔离级别都是Read Committed（读提交），该级别下的事务只能读取其他事务中已经提交的内容，可以避免脏读，但是不能避免重复读和幻读的情况。

重复读：在事务内读取了别的线程已经提交的数据，但是两次查询读取结果不一样，原因是查询的过程中其他事务做了更新操作

幻读：在事务内两次查询的数据条数不一样，原因是查询的过程中其他事务做了添加操作

（3）Repeatable Read

Repeatable Read（可重复读）是MySQL默认的事务隔离级别，它可以避免脏读、不可重复读的问题，确保同一个事务的多个实例在并发操作数据的时候，会看到相同的数据行。但是理论上，该级别会出现幻读情况，不过MySQL的存储引擎通过多版本并发控制机制解决了该问题，因此该级别是可以避免幻读的。

（4）Serializable

Serializable（可串行化）是事务的最高隔离级别，它会强制对事务进行排序，使它们彼此之间不会发生冲突，从而解决脏读、幻读、重复读的问题。实际上，就是在每个读的数据行上加上锁。这个级别，可能导致大量超时现象和锁竞争，实际应用中很少使用。

事物隔离级别之脏读

1、 脏读

所谓的脏读就是指一个事务读取了另一个事务未提取的数据。

试想一下：a账户要给b账户100元购买商品，如果a账户开启一个事务，执行下面的update语句做了如下转账的工作：

update account set money=money-100 where name=‘a’;

update account set money=money+100 where name=‘b’;

如果a账户先不提交事务，通知b账户来查询，由于b的隔离级别比较低，此时就会读取a事务中未提交的数据，发现a确实给自己转了100元，然后给a发货，等b发货成功之后，a再将事务回滚，此时b就会受到损伤，这就是脏读造成的。

为了演示上面的情况，这里我们开启了两个命令行窗口（相当于开启两个线程），分别模拟a账户和b账户，如下：


（1）设置b账户中事务的隔离级别

大家都知道MySQL的默认隔离级别是Repeatable Read（可重复读），该级别是可以避免脏读的，因此需要将b账户中事务的隔离级别设置为Read Uncommitted（读未提交），具体语句如下：

set session transaction isolation level read uncommitted;

上述语句之中，session表示当前会话，transaction就表示事务，isolation表示隔离，level表示级别，read uncommitted表示当前的隔离级别，该语句执行成功之后，使用select语句查询事务的隔离级别，结果如下：

select @@tx_isolation;

从上述结果可以看出，b账户的事务隔离级别以及修改为Read Uncommitted，接下来就是演示脏读的情况

（2）演示脏读
b账户：为了证明出现了脏读的情况，首先在b账户中开户一个事务，并在该事务中查询当前账户的余额信息，查询结果如下：

start transaction;
select * from account;

a账户：在a账户中开启一个事务，并在当前窗口中执行转账功能，具体语句如下：

start transaction;
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';

需要注意的是：此时不要提交事务，如果提交事务就无法演示出现脏读的情况。

b账户：a账户执行完转账语句后，b账户查询当前账户，如下：

从上面的查询结果来看，a账户已经成功给b账户转账了100元，这是由于b账户的事务隔离级别比较低，因此才读取了a账户还没有提交的数据内容，出现了脏读的情况，这时候，b误以为a账户以及转账成功，便会给a发货，当b发货之后a如果不提交事务将事务回滚，b就会受到损失。

上面演示完毕了，需要将a账户中的事务回滚，b账户中的事务提交。

（3）设置b账户的事务隔离级别

为了防止脏读发生，可以将b账户中的事务隔离级别设置为Read Committed（读提交），该级别会避免脏读，具体语句如下：

set session transaction isolation level read committed;

上述的语句执行成功之后，b账户的隔离级别已经设置成Read Committed

（4）验证是否出现脏读

b账户：为了说明没有出现脏读的情况，首先要在b账户中开启一个事务，并在该事务中查询各账户的余额情况，查询结果如下：

a账户：在a账户中重新开启一个事务，实现了转账功能，如下：

start transaction;
update account set money=money-100 where name=‘a’;
update account set money=money+100 where name=‘b’;
b账户：当a 账户转账成功之后，可以在b账户中再次查询各账户的余额信息，查询结果如下：

通过上面的对比两次查询结果可以发现，b账户在同一个事务中的查询结果是一致的，并没有查询到a账户中未提交的内容，因此可以说明Read Committed 隔离级别可以避免脏读，最后分别将a账户和b账户中的事务回滚。

事物隔离级别之不可重复读

1. 不可重复读

所谓的不可重复读（Non-Repeatable Read）是指事务中两次查询的结果不一致，原因是在查询的过程中其他事务做了更新的操作。

例如，银行在做统计报表的时候，第一次查询a账户有1000元，第二次查询a账户有900元，原因是统计期间a账户取出了100元，这样导致多次统计报表的结果不一致。

不可重复读和脏读有点类似，但是脏读是读取了另一个事务未提交的脏数据，不可重复读是在事务内重复读取了别的线程已提交的数据。

2. 演示不可重复读

（1）b账户：首先在b账户中开启一个事务，然后在当前事务中查询各个账户的余额信息，查询结果如下：

（2）a账户：在a账户中不用开启事务，直接使用update语句执行更新操作即可，具体语句如下：

update account set money=money-100 where name=‘a’;

由于a账户只需要执行修改的操作，不需要保证同步性，因此直接执行SQL语句就可以，执行结果如下所示：
接下来使用select语句查询a账户的余额

（3）再次回到b账户，如下：

b账户：当a账户中的更新操作执行完毕之后，在b账户之中再次查询各账户的余额，发现a账户变成900元。

两次b账户查询的结果不一致，实际上这种操作是没有错的

3. 如何解决这个问题？

设置b账户的事务的隔离级别

b账户：为了防止重复读的情况发生，可以将该事务的隔离级别设置为Repeatable Read（可重复读），具体如下：

set session transaction isolation level repeatable read;

事物隔离级别之幻读

1. 幻读

幻读（Phantom Read）又称为虚读，是指在一个事务内两次查询中数据条数不一致，幻读和不重复读有些类型，同样是在两次查询过程中，不同的是，幻读是由于其他事务做了插入记录的操作，导致记录数有所增加。

例如：银行在做统计报表时统计account表中所有用户的总金额时候，此时总共有三个账户，总共金额为3000元，这时候新增了一个用户账户，并且存入1000元，这时候银行再次统计就会发现账户总金额为4000，造成了幻读情况

2. 演示幻读：

（1）首先我们设置b账户的隔离级别

b账户：由于前面将事务个隔离级别设置为可重复读，这种隔离级别是可以避免幻读的出现，因此需要将事务的隔离级别设置为更低，下面将事务的隔离级别设置为

Read Committed，具体语句如下：

set session transaction isolation level read committed;
select @@tx_isolation;

上述语句执行完成之后，b账户事务的隔离级别为Read Committed

（2）演示幻读：

b账户：首先在b账户中开启一个事务，然后在当前事务中查询账户的余额信息，查询结果如下：

a账户：在对a账户进行添加操作之前，使用select语句查看当前a账户中的信息，执行结果如下：

接下来对a账户进行添加操作，a账户不用开启事务，直接执行添加操作即可，具体语句如下：

insert into account(name,money) values('c',1000);

b账户：当a账户添加记录完成之后，可以在b账户中再次查询场合余额信息，如下：

通过对比b账户设置Read Committed隔离级别前后，发现第二次查询数据时比第一查询数据时候多一条记录，这种情况并不是错误的，但可能不符合实际需求。需要注意的是，上述情况演示完成之后，将b账户中的事务提交。

3. 解决问题：

（1）重新设置b账户的隔离级别

b账户：为了防止出现幻读，可以将b账户的隔离级别设置为Repeatable Read，具体语句如下：

set session transaction isolation level repeatable read;

上述的语句执行完毕之后，事务隔离级别设置为Repeatable Read

（2）重新验证是否出现幻读

b账户：在b账户中重新开启一个事务，并在该事务中查询当前账户的余额信息，查询结果如下

a账户：对a账户进行添加操作之前，使用select语句查看当前的a账户的余额信息，执行语句结果如下：

接下来对账户a进行添加操作，在a账户中不开启事务，直接执行添加操作，具体语句如下：

insert into account(name,money) values('d',1000);

b账户：当a账户的添加操作执行成功之后，再次查询当前账户的余额信息，查询结果如下：

对比b账户两次查询的结果，发现两者都是一致的。并没有出现重复读取的情况，因此可以说明当前事务的隔离级别可以避免幻读，最后还有使用commit语句提交当前事务，提交之后，b账户查询结果如下：

注意：

Repeatable Read从理论的角度是会出现幻读的，这也就是限制了Repeatable Read这个事务隔离级别使用。一个事务隔离级别推出使用发现其自身带有缺陷，开发者自然会想到完善的方法，所以MySQL内部通过多版本控制机制【实际上就是对读取到的数据加锁】解决这个问题。
最后，用户才可以放心大胆使用Repeatable Read这个事务隔离级别。

附加：Serializable 和 Repeatable Read都可以防止幻读。但是Serializable 事务隔离级别效率低下，比较耗数据库性能，一般不使用。

锁类型

1. 首先说一下：行锁 和 表锁

主要是针对锁粒度划分的。

一般分为：行锁、表锁、库锁

（1）行锁：访问数据库的时候，锁定整个行数据，防止并发错误。

（2）表锁：访问数据库的时候，锁定整个表数据，防止并发错误。

行锁 和 表锁 的区别：

表锁： 开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

行锁： 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

2. 悲观锁 和 乐观锁

（1）悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

（2）乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

（3）悲观锁 和 乐观锁的区别：

两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。