一、前言1.1 如果CURD不加控制会有什么问题假设你正在开发一个火车票售票系统数据库里有一张票表记录着余票数量。当两个用户同时查询余票并购买最后一张票时可能会发生以下情况客户端A查询余票发现还剩1张。此时客户端B也查询余票同样看到还剩1张。客户端A决定买票执行更新将余票减为0但还没来得及提交或提交前。客户端B也执行更新将余票减为0并且提交。客户端A随后提交。结果一张票被卖了两次数据不一致这个问题揭示了一个重要概念事务。如果我们将“查询余票”和“更新余票”包装成一个原子操作中间不被其他客户端干扰就能避免这种问题。1.2 CURD满足什么属性能解决上述问题1. 买票的过程得是原子的吧2.买票互相应该不能影响吧3.买完票应该要永久有效吧4.买前和买后都要是确定的状态吧二、什么是事务明确实际现实编写sql的时候不只一条sql , 而是一批sql !!! 共同组合才具有意义 。事务就是一组DML语句组成这些语句在逻辑上存在相关性这一组DML语句数据操作语言语句如INSERT、UPDATE、DELETE要么全部成功要么全部失败是一个整体。MySQL提供一种机制保证我们达到这样的效果。事务还规定不同的客户端看到的 数据是不相同的。事务就是要做的或所做的事情主要用于处理操作量大复杂度高的数据。举个例子毕业时学校要删除你的所有信息包括基本信息、成绩、论坛帖子等。这些删除操作必须在一个事务中完成要么全部删除要么一条都不删否则会导致数据不完整。正如我们上面所说一个MySQL数据库可不止你一个事务在运行同一时刻甚至有大量的请求被包 装成事务在向 MySQL服务器发起事务处理请求。而每条事务至少一条SQL最多很多SQL,这样如果大家都访问同样的表数据在不加保护的情况就绝对会出现问题。甚至因为事务由多条 SQL构成那么也会存在执行到一半出错或者不想再执行的情况那么已经执行的怎么办呢所有一个完整的事务绝对不是简单的sql集合还需要满足如下四个属性一个完整的事务必须满足四个属性简称ACID属性说明原子性Atomicity事务中的所有操作要么全部完成要么全部不完成。如果执行中出错会回滚到开始前的状态。一致性Consistency事务执行前后数据库的完整性约束没有被破坏。例如转账前后两人账户总额不变。隔离性Isolation多个事务并发执行时一个事务的执行不应被其他事务干扰。通过隔离级别控制。持久性Durability事务一旦提交对数据库的修改就是永久性的即使系统故障也不会丢失。注一致性通常由应用程序逻辑保证数据库通过原子性、隔离性和持久性来辅助实现一致性。MySQL要同时帮助不同客户端处理各种各样的事务请求就意味着MySQL在运行期间存在各种各样的事务。事务需要管理对事务起来如何管理起来先描述再组织 所以事务是MySQL里面来了一堆的SQL 然后我把你这一堆SQL打包成一个事务对象最后放在我们的事务列表里面然后让MySQL帮助我们去执行三、为什么会出现事务简化上层的编程模型让上层用起来更加舒服忽略各种各样的潜在问题。本质上就是为了应用层服务而不是天生就存在的而是在使用的时候发现有并发问题原则性问题等等。备注我们后面把 MySQL 中的一行信息称为一行记录四、事务的版本支持在MySQL中只有使用了Innodb 数据库引擎的数据库或表才支持事务MyISAM不支持。五、事务提交方式事务的提交方式常见的有两种自动提交手动提交查看事务提交方式show variables like autocommit;ON自动提交默认OFF手动提交用SET来改变MySQL的自动提交模式-- 关闭自动提交需要手动commit SET AUTOCOMMIT0; -- 开启自动提交默认 SET AUTOCOMMIT1;六、事务常见操作方式mysql服务一般不要暴露在公网上暴露在公网上别人可以联网但是不一定能登陆。后续还需要学用户管理允许哪些用户从哪里登录学完之后再mysql的用户表里去配置才能让别人从远端进行登录。Mysql不仅本地主机可以连接也可以让远端的多态主机也可以连接所以打造一个mysql的服务器可能会存在多个客户端同时访问的一个情况~6.1 基础命令命令作用BEGIN/START TRANSACTION开启事务COMMIT提交事务永久生效ROLLBACK回滚事务撤销所有操作SAVEPOINT 名称创建保存点ROLLBACK TO 保存点回滚到指定保存点6.2 完整案例演示Centos 7云服务器默认开启3306 mysqld服务sudo netstat -nltp使用win cmd远程访问Centos 7云服务器mysqld服务(需要win上也安装了MySQL这里看到结果即可) 注意使用本地mysql客户端可能看不到链接效果本地可能使用域间套接字查不到链接使用netstat查看链接情况可知mysql本质是一个客户端进程1. 为了便于演示我们将mysql的默认隔离级别设置成读未提交。2. 创建测试表create table if not exists account( id int primary key, -- 主键用户ID name varchar(50) not null default , -- 用户名 blance decimal(10,2) not null default 0.0 -- 余额 )ENGINEInnoDB DEFAULT CHARSETUTF8; -- 必须用InnoDB6.2.1 正常演示 - 证明事务的开始与回滚mysql BEGIN; -- 开启事务 Query OK, 0 rows affected (0.00 sec) mysql SAVEPOINT save1; -- 创建保存点 save1 Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO account VALUES (1, 张三, 100); Query OK, 1 row affected (0.05 sec) mysql SAVEPOINT save2; -- 创建保存点 save2 Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO account VALUES (2, 李四, 10000); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM account; ---------------------- | id | name | balance | ---------------------- | 1 | 张三 | 100.00 | | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) mysql ROLLBACK TO save2; -- 回滚到 save2即撤销第二条插入 Query OK, 0 rows affected (0.03 sec) mysql SELECT * FROM account; --------------------- | id | name | balance | --------------------- | 1 | 张三 | 100.00 | --------------------- 1 row in set (0.00 sec) mysql ROLLBACK; -- 回滚整个事务撤销所有操作 Query OK, 0 rows affected (0.00 sec) mysql SELECT * FROM account; Empty set (0.00 sec)我们这里的事务的隔离级别设置了读未提交(read uncommitted) 方便我们研究两个并发的事务的读写情况rollback to 保存点即回滚到设置保存点的地方rollback 的时候没有保存点 意味着不可以定向的去进行我们的一个回滚操作会把事务开启之后的所有的操作都清除掉数据正常插入并且提交后rollback没什么用了数据被持久化的保存了逐行解释BEGIN显式开启事务后续操作处于事务中。SAVEPOINT save1在事务中标记一个点名为save1。插入第一条记录数据进入内存未持久化。SAVEPOINT save2再标记一个点。插入第二条记录。此时查看表两条记录都在但在当前事务的视图里其他事务可能看不到。ROLLBACK TO save2回滚到save2状态即撤销save2之后的所有操作。这里撤销了第二条插入。再次查看只剩第一条。ROLLBACK回滚整个事务到BEGIN之前所有操作撤销表变为空。注意 如果事务已经提交COMMIT则不能再回滚。6.2.2 非正常演示1 -证明未commit客户端崩溃MySQL自动会回滚隔离级别设置为读未提交没有COMMIT的事务如果客户端异常终止MySQL会自动回滚保证原子性。6.2.3 非正常演示2 -证明commit了客户端崩溃MySQL数据不会在受影响已经持久化结论 COMMIT后数据持久化即使崩溃也不会丢失。6.2.4 非正常演示3 -对比试验。证明begin操作会自动更改提交方式不会受MySQL是否自动提交影响如果我们将autocommit关闭后直接执行INSERT而不使用BEGIN那么这条INSERT不会自动提交需要手动COMMIT。但这里使用了BEGIN事务行为与autocommit无关必须COMMIT才会持久化。结论一旦使用BEGIN或START TRANSACTION事务进入手动模式必须显式COMMIT才会持久化与autocommit设置无关。6.2.5 非正常演示4 -证明单条SQL与事务的关系如果autocommitOFF单条INSERT不会自动提交需要手动COMMIT。当autocommitON时每条SQL都是独立事务自动提交。即使不写BEGIN执行delete 后立即持久化。结论只要输入begin或者start transaction事务便必须要通过commit提交才会持久化与是否设置set autocommit无关。事务可以手动回滚同时当操作异常MySQL会自动回滚对于 InnoDB 每一条 SQL 语言都默认封装成事务自动提交。select有特殊情况因为MySQL 有 MVCC 从上面的例子我们能看到事务本身的原子性(回滚)持久性(commit)事务操作注意事项如果没有设置保存点也可以回滚只能回滚到事务的开始。直接使用 rollback(前提是事务还没有提交)如果一个事务被提交了commit则不可以回退rollback可以选择回退到哪个保存点InnoDB 支持事务 MyISAM 不支持事务开始事务可以使 start transaction 或者 begin MySQL【事务上】
一、前言1.1 如果CURD不加控制会有什么问题假设你正在开发一个火车票售票系统数据库里有一张票表记录着余票数量。当两个用户同时查询余票并购买最后一张票时可能会发生以下情况客户端A查询余票发现还剩1张。此时客户端B也查询余票同样看到还剩1张。客户端A决定买票执行更新将余票减为0但还没来得及提交或提交前。客户端B也执行更新将余票减为0并且提交。客户端A随后提交。结果一张票被卖了两次数据不一致这个问题揭示了一个重要概念事务。如果我们将“查询余票”和“更新余票”包装成一个原子操作中间不被其他客户端干扰就能避免这种问题。1.2 CURD满足什么属性能解决上述问题1. 买票的过程得是原子的吧2.买票互相应该不能影响吧3.买完票应该要永久有效吧4.买前和买后都要是确定的状态吧二、什么是事务明确实际现实编写sql的时候不只一条sql , 而是一批sql !!! 共同组合才具有意义 。事务就是一组DML语句组成这些语句在逻辑上存在相关性这一组DML语句数据操作语言语句如INSERT、UPDATE、DELETE要么全部成功要么全部失败是一个整体。MySQL提供一种机制保证我们达到这样的效果。事务还规定不同的客户端看到的 数据是不相同的。事务就是要做的或所做的事情主要用于处理操作量大复杂度高的数据。举个例子毕业时学校要删除你的所有信息包括基本信息、成绩、论坛帖子等。这些删除操作必须在一个事务中完成要么全部删除要么一条都不删否则会导致数据不完整。正如我们上面所说一个MySQL数据库可不止你一个事务在运行同一时刻甚至有大量的请求被包 装成事务在向 MySQL服务器发起事务处理请求。而每条事务至少一条SQL最多很多SQL,这样如果大家都访问同样的表数据在不加保护的情况就绝对会出现问题。甚至因为事务由多条 SQL构成那么也会存在执行到一半出错或者不想再执行的情况那么已经执行的怎么办呢所有一个完整的事务绝对不是简单的sql集合还需要满足如下四个属性一个完整的事务必须满足四个属性简称ACID属性说明原子性Atomicity事务中的所有操作要么全部完成要么全部不完成。如果执行中出错会回滚到开始前的状态。一致性Consistency事务执行前后数据库的完整性约束没有被破坏。例如转账前后两人账户总额不变。隔离性Isolation多个事务并发执行时一个事务的执行不应被其他事务干扰。通过隔离级别控制。持久性Durability事务一旦提交对数据库的修改就是永久性的即使系统故障也不会丢失。注一致性通常由应用程序逻辑保证数据库通过原子性、隔离性和持久性来辅助实现一致性。MySQL要同时帮助不同客户端处理各种各样的事务请求就意味着MySQL在运行期间存在各种各样的事务。事务需要管理对事务起来如何管理起来先描述再组织 所以事务是MySQL里面来了一堆的SQL 然后我把你这一堆SQL打包成一个事务对象最后放在我们的事务列表里面然后让MySQL帮助我们去执行三、为什么会出现事务简化上层的编程模型让上层用起来更加舒服忽略各种各样的潜在问题。本质上就是为了应用层服务而不是天生就存在的而是在使用的时候发现有并发问题原则性问题等等。备注我们后面把 MySQL 中的一行信息称为一行记录四、事务的版本支持在MySQL中只有使用了Innodb 数据库引擎的数据库或表才支持事务MyISAM不支持。五、事务提交方式事务的提交方式常见的有两种自动提交手动提交查看事务提交方式show variables like autocommit;ON自动提交默认OFF手动提交用SET来改变MySQL的自动提交模式-- 关闭自动提交需要手动commit SET AUTOCOMMIT0; -- 开启自动提交默认 SET AUTOCOMMIT1;六、事务常见操作方式mysql服务一般不要暴露在公网上暴露在公网上别人可以联网但是不一定能登陆。后续还需要学用户管理允许哪些用户从哪里登录学完之后再mysql的用户表里去配置才能让别人从远端进行登录。Mysql不仅本地主机可以连接也可以让远端的多态主机也可以连接所以打造一个mysql的服务器可能会存在多个客户端同时访问的一个情况~6.1 基础命令命令作用BEGIN/START TRANSACTION开启事务COMMIT提交事务永久生效ROLLBACK回滚事务撤销所有操作SAVEPOINT 名称创建保存点ROLLBACK TO 保存点回滚到指定保存点6.2 完整案例演示Centos 7云服务器默认开启3306 mysqld服务sudo netstat -nltp使用win cmd远程访问Centos 7云服务器mysqld服务(需要win上也安装了MySQL这里看到结果即可) 注意使用本地mysql客户端可能看不到链接效果本地可能使用域间套接字查不到链接使用netstat查看链接情况可知mysql本质是一个客户端进程1. 为了便于演示我们将mysql的默认隔离级别设置成读未提交。2. 创建测试表create table if not exists account( id int primary key, -- 主键用户ID name varchar(50) not null default , -- 用户名 blance decimal(10,2) not null default 0.0 -- 余额 )ENGINEInnoDB DEFAULT CHARSETUTF8; -- 必须用InnoDB6.2.1 正常演示 - 证明事务的开始与回滚mysql BEGIN; -- 开启事务 Query OK, 0 rows affected (0.00 sec) mysql SAVEPOINT save1; -- 创建保存点 save1 Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO account VALUES (1, 张三, 100); Query OK, 1 row affected (0.05 sec) mysql SAVEPOINT save2; -- 创建保存点 save2 Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO account VALUES (2, 李四, 10000); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM account; ---------------------- | id | name | balance | ---------------------- | 1 | 张三 | 100.00 | | 2 | 李四 | 10000.00 | ---------------------- 2 rows in set (0.00 sec) mysql ROLLBACK TO save2; -- 回滚到 save2即撤销第二条插入 Query OK, 0 rows affected (0.03 sec) mysql SELECT * FROM account; --------------------- | id | name | balance | --------------------- | 1 | 张三 | 100.00 | --------------------- 1 row in set (0.00 sec) mysql ROLLBACK; -- 回滚整个事务撤销所有操作 Query OK, 0 rows affected (0.00 sec) mysql SELECT * FROM account; Empty set (0.00 sec)我们这里的事务的隔离级别设置了读未提交(read uncommitted) 方便我们研究两个并发的事务的读写情况rollback to 保存点即回滚到设置保存点的地方rollback 的时候没有保存点 意味着不可以定向的去进行我们的一个回滚操作会把事务开启之后的所有的操作都清除掉数据正常插入并且提交后rollback没什么用了数据被持久化的保存了逐行解释BEGIN显式开启事务后续操作处于事务中。SAVEPOINT save1在事务中标记一个点名为save1。插入第一条记录数据进入内存未持久化。SAVEPOINT save2再标记一个点。插入第二条记录。此时查看表两条记录都在但在当前事务的视图里其他事务可能看不到。ROLLBACK TO save2回滚到save2状态即撤销save2之后的所有操作。这里撤销了第二条插入。再次查看只剩第一条。ROLLBACK回滚整个事务到BEGIN之前所有操作撤销表变为空。注意 如果事务已经提交COMMIT则不能再回滚。6.2.2 非正常演示1 -证明未commit客户端崩溃MySQL自动会回滚隔离级别设置为读未提交没有COMMIT的事务如果客户端异常终止MySQL会自动回滚保证原子性。6.2.3 非正常演示2 -证明commit了客户端崩溃MySQL数据不会在受影响已经持久化结论 COMMIT后数据持久化即使崩溃也不会丢失。6.2.4 非正常演示3 -对比试验。证明begin操作会自动更改提交方式不会受MySQL是否自动提交影响如果我们将autocommit关闭后直接执行INSERT而不使用BEGIN那么这条INSERT不会自动提交需要手动COMMIT。但这里使用了BEGIN事务行为与autocommit无关必须COMMIT才会持久化。结论一旦使用BEGIN或START TRANSACTION事务进入手动模式必须显式COMMIT才会持久化与autocommit设置无关。6.2.5 非正常演示4 -证明单条SQL与事务的关系如果autocommitOFF单条INSERT不会自动提交需要手动COMMIT。当autocommitON时每条SQL都是独立事务自动提交。即使不写BEGIN执行delete 后立即持久化。结论只要输入begin或者start transaction事务便必须要通过commit提交才会持久化与是否设置set autocommit无关。事务可以手动回滚同时当操作异常MySQL会自动回滚对于 InnoDB 每一条 SQL 语言都默认封装成事务自动提交。select有特殊情况因为MySQL 有 MVCC 从上面的例子我们能看到事务本身的原子性(回滚)持久性(commit)事务操作注意事项如果没有设置保存点也可以回滚只能回滚到事务的开始。直接使用 rollback(前提是事务还没有提交)如果一个事务被提交了commit则不可以回退rollback可以选择回退到哪个保存点InnoDB 支持事务 MyISAM 不支持事务开始事务可以使 start transaction 或者 begin