mysql sql 事务写作_一文精通MySQL事务规则

我们一起学习进步!

正文

引言:

每个程序员学习数据库离不开MySQL,而学习MySQL就必学事务,今天我们就来一起学习一下MySQL的事务;

事务的概念

在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。由多条SQL语句组成一个功能(事务).需要将多次访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败，就进行事务的回滚，所有的SQL语句全部执行失败。

事务应用场景

最通俗易懂的就是银行转账的案例,例如张三给李四转钱,张三账号减去相应数额资金,李四账号添加相应数额资金;

案例演示:

-- 创建account数据表

CREATE TABLE account (

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(10),

balance DOUBLE

);

-- 添加数据

INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);

模拟张三给李四转500元钱，一个转账的业务操作最少要执行下面的2条sql语句：

张三账号-500

-- 1. 张三账号-500

UPDATE account SET balance = balance - 500 WHERE id=1;

李四账号+500

-- 2. 李四账号+500

UPDATE account SET balance = balance + 500 WHERE id=2;

结果如下

假设当张三账号上-500元,服务器崩溃了。李四的账号并没有+500元，数据就出现问题了。我们需要保证其中一条SQL语句出现问题，整个转账就算失败。只有两条SQL都成功了转账才算成功。这个时候就需要用到事务。

事务的四大特性(A,C,I,D)

事务特性

描述

原子性(Atomicity)

事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生

一致性(Consistency)

事务前后数据的完整性必须保持一致

隔离性(Isolation)

如多个用户并发访问数据库时,一个用户的事务不可以被其它用户的事务所干扰,多个用户并发事务之间数据要相互隔离,不可以互相影响

持久性(Durability)

如一个事务一旦被提交,它对数据库中的数据改变就是永久性的,就算未来数据库发生故障也不应该对数据有所影响

手动提交事务

在MySQL数据库中有两种提交事务的规则,一种是手动提交事务,一种是自动提交事务,其中自动提交事务是默认的,下面先看一下手动提交事务的案例,MySQL中有专门用于事务的SQL,如下

SQL

描述

start transaction

开始事务

commit

提交事务

rollback

回滚事务

End Transaction

事务结束

手动提交事务步骤

手动提交事务分别有两种情况,如下图

第1种情况：开启事务 -> 执行SQL语句 -> 成功 -> 提交事务

目前数据库内容如下

使用DOS控制台进入MySQL

执行以下SQL语句：1.开启事务， 2.张三账号-500， 3.李四账号+500

START TRANSACTION;

UPDATE account SET balance = balance - 500 WHERE id=1;

UPDATE account SET balance = balance + 500 WHERE id=2;

使用Navicat查看数据库数据,发现数据库并没有变

在控制台执行commit提交任务：

使用Navicat查看数据库：发现数据改变

第2种情况：开启事务 -> 执行SQL语句 -> 失败 -> 回滚事务

模拟张三给李四转500元钱(失败)目前数据库数据如下：

在控制台执行以下SQL语句：1.开启事务， 2.张三账号-500

START TRANSACTION;

UPDATE account SET balance = balance - 500 WHERE id=1;

使用Navicat查看数据库：发现数据并没有改变

在控制台执行rollback回滚事务：

使用Navicat查看数据库：发现数据没有改变

自动提交事务

MySQL的每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，执行完毕自动提交事务，MySQL默认开始自动提交事务。

将张三和李四金额重置为1000

执行以下SQL语句

UPDATE account SET balance = balance - 500 WHERE id=1;

使用Navicat查看数据库：发现数据已经改变

我们可以使用SQL语句查看MySQL是否开启自动提交事务

show variables like '%commit%';

-- 或

SELECT @@autocommit;

通过修改MySQL全局变量"autocommit"，取消自动提交事务

0:OFF(关闭自动提交)

1:ON(开启自动提交)

取消自动提交事务，设置自动提交的参数为OFF，执行SQL语句：set autocommit = 0;

在控制台执行以下SQL语句：张三-500

UPDATE account SET balance = balance - 500 WHERE id=1;

使用Navicat查看数据库，发现数据并没有改变

在控制台执行commit提交任务

使用Navicat查看数据库，发现数据改变

MySQL事务的原理

事务开启之后, 所有的操作都会临时保存到事务日志, 事务日志只有在得到commit命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接),如下图

事务的隔离级别

事务在操作时的理想状态：多个事务之间互不影响，如果隔离级别设置不当就可能引发并发访问问题。

并发访问的问题

含义

脏读

一个事务读取到了另一个事务尚未提交的数据

不可重复读

一个事务中两次读取的数据内容不一致

幻读

一个事务读取到了别的事务插入的数据,导致前后读取记录行数不同

脏读演示

不可重复读演示

幻读演示

MySQL数据库有四种隔离级别：上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

级别

名称

隔离级别

脏读

不可重复读

幻读

数据库默认隔离级别

1

读未提交

read uncommitted

是

是

是

2

读已提交

read committed

否

是

是

Oracle和SQL Server

3

可重复读

repeatable read

否

否

是

MySQL

4

串行化

serializable

否

否

否