医院网站建设公司,游戏推广一个月能拿多少钱,编程一个最简单游戏代码,竞价sem托管目录
一、事务的概念
1、事务的四种特性
2、事务的作用
3、存储引擎对事务的支持
4、事务的提交方式
二、事务的启动、回滚与提交
1、准备工作#xff1a;调整MySQL的默认隔离级别为最低/创建测试表
2、事务的启动、回滚与提交
3、启动事务后未commit#xff0c;但是… 目录
一、事务的概念
1、事务的四种特性
2、事务的作用
3、存储引擎对事务的支持
4、事务的提交方式
二、事务的启动、回滚与提交
1、准备工作调整MySQL的默认隔离级别为最低/创建测试表
2、事务的启动、回滚与提交
3、启动事务后未commit但是客户端崩了MySQL会自动回滚
三、事务的隔离级别
1、隔离性
2、隔离级别
3、隔离性的查看和设置
3.1隔离性的查看
3.2设置隔离级别
4、四种隔离级别详解
4.1读未提交【Read Uncommitted】
4.2读提交【Read Committed】
4.3可重复读【Repeatable Read】
4.4串行化【Serializable】 一、事务的概念
1、事务的四种特性
事务就是一组DML语句组成这些语句在逻辑上存在相关性这一组DML语句要么全部成功要么全部失败是一个整体。例如银行转账操作上层看来是一个单纯的转账操作但是下层却需要一条或多条SQL语句来完成转账操作这一组SQL是一个整体被称为事务。事务还规定不同的客户端看到的数据是不相同的。
一个 MySQL 数据库可不止你一个事务在运行同一时刻有大量的请求被包装成事务向 MySQL 服务器发起事务处理请求。如果多名用户都访问同样的表数据在不加保护的情况绝对会出现问题。并且事务由多条 SQL 构成也会存在执行到一半出错或者不想再执行的情况那么已经执行的怎么办呢
所以一个完整的事务绝对不是简单的 sql 集合还需要满足如下四个属性 原子性一个事务transaction中的所有操作要么全部完成要么全部不完成不会结束在中间某个环节。事务在执行过程中发生错误会被回滚Rollback到事务开始前的状态就像这个事务从来没有执行过一样。 一致性在事务开始之前和事务结束以后数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。 原子性、隔离性、持久性是因一致性是果。 隔离性数据库允许多个并发事务同时对其数据进行读写和修改的能力隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别包括读未提交 Read uncommitted 、读提交 read committed 、可重复读 repeatable read 和串行化 Serializable 持久性事务处理结束后对数据的修改就是永久的即便系统故障也不会丢失。 上面四个属性可以简称为 ACID 。 原子性Atomicity或称不可分割性 一致性Consistency 隔离性Isolation又称独立性 持久性Durability。 2、事务的作用
当程序员在编写上层代码逻辑访问数据库时事务能够简化编程时需要考虑的多种细节问题例如我们在使用事务时要么提交要么回滚不用去担心网络异常、服务器宕机等问题。
3、存储引擎对事务的支持 在 MySQL 中只有Innodb支持事务而MyISAM不支持。
4、事务的提交方式
事务的提交方式分为自动提交和手动提交。
自动提交
--查看MySQL事务的提交方式发现是自动提交
mysql show variables like autocommit;
----------------------
| Variable_name | Value |
----------------------
| autocommit | ON |
----------------------
1 row in set (0.02 sec)
使用set改变MySQL的事务提交方式
--将MySQL的事务提交方式修改为禁止自动提交
mysql set autocommit0;
Query OK, 0 rows affected (0.01 sec)mysql show variables like autocommit;
----------------------
| Variable_name | Value |
----------------------
| autocommit | OFF |
----------------------
1 row in set (0.00 sec)--将MySQL的事务提交方式修改为开启自动提交
mysql set autocommit1;
Query OK, 0 rows affected (0.00 sec)mysql show variables like autocommit;
----------------------
| Variable_name | Value |
----------------------
| autocommit | ON |
----------------------
1 row in set (0.00 sec)
二、事务的启动、回滚与提交
1、准备工作调整MySQL的默认隔离级别为最低/创建测试表
为了看到演示现象先将MySQL的默认隔离级别设置成读未提交最低后退出重启
mysql set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
--修改默认隔离级别生效
mysql select tx_isolation;
------------------
| tx_isolation |
------------------
| READ-UNCOMMITTED |
------------------
1 row in set, 1 warning (0.00 sec)
创建测试表
create table if not exists account(
id int primary key,
name varchar(50) not null default ,
blance decimal(10,2) not null default 0.0
)ENGINEInnoDB DEFAULT CHARSETUTF8;
2、事务的启动、回滚与提交
使用者jly
--1、查看当前MySQL在线用户
mysql show processlist;--开始一个事务也可以用begin
-----------------------------------------------------------------------------------
| Id | User | Host | db | Command | Time | State | Info |
-----------------------------------------------------------------------------------
| 474 | jly | localhost | transaction_data | Query | 0 | starting | show processlist |
| 475 | jly | localhost | NULL | Sleep | 132 | | NULL |
| 476 | root | localhost | NULL | Sleep | 5 | | NULL |
-----------------------------------------------------------------------------------
3 rows in set (0.00 sec)
--2、先确认事务的提交方式为自动提交
mysql show variables like autocommit;
----------------------
| Variable_name | Value |
----------------------
| autocommit | ON |
----------------------
1 row in set (0.02 sec)
--3、开始一个事务start transaction执行之后后方的SQL将处于同一个事务
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
--4、创建一个保存节点save1
mysql savepoint save1;
Query OK, 0 rows affected (0.00 sec)
--5、在当前表中插入一条数据
mysql insert into account values(1,张三,1234.5);
Query OK, 1 row affected (0.00 sec)
--6、创建一个保存节点save2
mysql savepoint save2;
Query OK, 0 rows affected (0.00 sec)
--7、在当前表中再次插入一条数据
mysql insert into account values(2,李四,998.561);
Query OK, 1 row affected, 1 warning (0.00 sec)
--8、创建一个保存节点save3
mysql savepoint save3;
Query OK, 0 rows affected (0.00 sec)
--9、在当前表中再次插入一条数据
mysql insert into account values(3,王五,653.25);
Query OK, 1 row affected (0.00 sec)
--10、回滚到保存点save3
mysql rollback to save3;--如果全部回滚可以直接rollback;
Query OK, 0 rows affected (0.00 sec)
--11、提交以结束本次事务
mysql commit;
Query OK, 0 rows affected (0.00 sec)
使用者root
--当上方用户执行完第三步时让另一个用户开始一个事务
mysql begin;
Query OK, 0 rows affected (0.00 sec)
--当上方用户执行完第五步时root用户查看表发现表中存在一条数据
mysql select* from account;
---------------------
| id | name | blance |
---------------------
| 1 | 张三 | 1234.50 |
---------------------
1 row in set (0.00 sec)
--当上方用户执行完第九步时root用户查看表发现表中存在三条数据
mysql select* from account;
---------------------
| id | name | blance |
---------------------
| 1 | 张三 | 1234.50 |
| 2 | 李四 | 998.56 |
| 3 | 王五 | 653.25 |
---------------------
3 rows in set (0.00 sec)
--当上方用户执行完第十步时root用户查看表发现王五没了
mysql select* from account;
---------------------
| id | name | blance |
---------------------
| 1 | 张三 | 1234.50 |
| 2 | 李四 | 998.56 |
---------------------
2 rows in set (0.00 sec)
--提交以结束本次事务
mysql commit;
Query OK, 0 rows affected (0.00 sec) 1、如果在一个事务中用户最终commit提交了本次事务那么本次事务发生的数据修改将被MySQL持久化想回滚已经没机会了。 2、如果从save3回滚到save1发现滚过头了是不能再从save1往前滚到save3的MySQL会提示SAVEPOINT s3 does not exist。 3、启动事务后未commit但是客户端崩了MySQL会自动回滚
使用者jly
--1、启动事务并插入数据
mysql begin;
Query OK, 0 rows affected (0.00 sec)
mysql select* from account;
Empty set (0.01 sec)
mysql insert into account values(1,张三,1234.5);
Query OK, 1 row affected (0.01 sec)
mysql insert into account values(2,李四,998.561);
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql insert into account values(3,王五,653.25);
Query OK, 1 row affected (0.00 sec)
--2、未commit使用ctrl\终止MySQL
mysql Aborted
使用者root
--1、启动事务
mysql begin;
--当上方用户执行完第一步时查看表
mysql select* from account;
---------------------
| id | name | blance |
---------------------
| 1 | 张三 | 1234.50 |
| 2 | 李四 | 998.56 |
| 3 | 王五 | 653.25 |
---------------------
3 rows in set (0.00 sec)
--当上方用户执行完第二步时查看表发现数据回退
mysql select* from account;
Empty set (0.00 sec) 启动事务后未commit但是客户端崩了或者自己强行关闭客户端MySQL会自动回滚 1、autocommit并不会影响begin后启动的事务的提交使用begin后必须输入commit才能使数据持久化。 2、单句SQL本质就是事务它的持久化和autocommit有关autocommit为ON表示单SQL无需手动commit自动提交否则需要手动commit令数据持久化。select有特殊情况因为MySQL 有 MVCC 3、事务可以手动回滚同时当操作异常MySQL会自动回滚 4、我们能看到事务本身的原子性(rollback)持久性(commit) 三、事务的隔离级别
1、隔离性 1、MySQL服务可能会同时被多个客户端进程(线程)访问访问的方式以事务方式进行。 2、一个事务可能由多条SQL构成也就意味着任何一个事务都有执行前执行中执行后的阶段。而所谓的原子性其实就是让用户层要么看到执行前要么看到执行后。执行中出现问题可以随时回滚。所以单个事务对用户表现出来的特性就是原子性。 3、但是所有事务都要有个执行过程那么在多个事务各自执行多个SQL的时候就还是有可能会出现互相影响的情况。比如多个事务同时访问同一张表甚至同一行数据。 4、数据库中为了保证事务执行过程中尽量不受干扰就有了一个重要特征隔离性 。 5、数据库中允许事务受不同程度的干扰就有了一种重要特征隔离级别。 2、隔离级别
这四点都是关于读写并发的情况 读未提交【Read Uncommitted】 在该隔离级别所有的事务都可以看到其他事务没有提交的执行结果。实际生产中不可能使用这种隔离级别的但是相当于没有任何隔离性也会有很多并发问题如脏读幻读不可重复读等我们上面为了做实验方便用的就是这个隔离性。 读提交【Read Committed】该隔离级别是大多数数据库的默认的隔离级别不是 MySQL 默认的。它满足了隔离的简单定义:一个事务只能看到其他的已经提交的事务所做的改变。这种隔离级别会引起不可重复读即一个事务执行时如果多次 select可能得到不同的结果。 可重复读【Repeatable Read】 这是 MySQL 默认的隔离级别它确保同一个事务在执行中多次读取操作数据时会看到同样的数据行。但是有的数据库会有幻读问题。MySQL不会 串行化【Serializable】: 这是事务的最高隔离级别它通过强制事务排序使之不可能相互冲突从而解决了幻读的问题。它在每个读的数据行上面加上共享锁。但是可能会导致超时和锁竞争这种隔离级别太极端实际生产基本不使用 隔离级别如何实现隔离基本都是通过锁实现的不同的隔离级别锁的使用是不同的。常见有表锁行锁读锁写锁间隙锁(GAP),Next-Key锁(GAP行锁)等。 3、隔离性的查看和设置
3.1隔离性的查看
--查看全局的隔离级别
mysql select global.tx_isolation;
-----------------------
| global.tx_isolation |
-----------------------
| READ-UNCOMMITTED |
-----------------------
1 row in set, 1 warning (0.02 sec)--查看当前会话的隔离级别
mysql select session.tx_isolation;
------------------------
| session.tx_isolation |
------------------------
| READ-UNCOMMITTED |
------------------------
1 row in set, 1 warning (0.00 sec)--默认同上
mysql select tx_isolation;
------------------
| tx_isolation |
------------------
| READ-UNCOMMITTED |
------------------
1 row in set, 1 warning (0.00 sec) 当前会话的session的隔离级别是根据全局global的隔离级别来的。 3.2设置隔离级别
语法
--设置当前会话 or 全局隔离级别语法
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ
COMMITTED | REPEATABLE READ | SERIALIZABLE}
设置会话的隔离级别为读提交
mysql set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
--修改后全局隔离级别没有变会话隔离级别被修改为读提交
mysql select global.tx_isolation;
-----------------------
| global.tx_isolation |
-----------------------
| READ-UNCOMMITTED |
-----------------------
1 row in set, 1 warning (0.00 sec)mysql select session.tx_isolation;
------------------------
| session.tx_isolation |
------------------------
| READ-COMMITTED |
------------------------
1 row in set, 1 warning (0.00 sec) 将隔离级别修改为读提交此时其他用户的隔离级别是不会被修改的这修改的仅仅是当前用户的的隔离级别。 重新登录后会话的隔离级别会按照全局隔离级别重新配置。 设置全局的隔离级别为串行化
mysql set global transaction isolation level SERIALIZABLE;
Query OK, 0 rows affected (0.01 sec)--修改全局的隔离级别只影响全局的当前会话和默认的隔离级别不变
mysql select global.tx_isolation;
-----------------------
| global.tx_isolation |
-----------------------
| SERIALIZABLE |
-----------------------
1 row in set, 1 warning (0.00 sec)mysql select session.tx_isolation;
------------------------
| session.tx_isolation |
------------------------
| READ-COMMITTED |
------------------------
1 row in set, 1 warning (0.00 sec)mysql select tx_isolation;
----------------
| tx_isolation |
----------------
| READ-COMMITTED |
----------------
1 row in set, 1 warning (0.00 sec) 当一个会话修改了全局隔离级别将会同时修改其他所有会话的全局隔离级别。 后续登录时会话和默认的隔离级别将会引用全局隔离级别的设置。 尽量保证隔离级别一致闲的没事不要改事务的隔离级别。 4、四种隔离级别详解
4.1读未提交【Read Uncommitted】
--先设置当前的全局隔离级别为读未提交再退出MySQL重新登录一下让其生效
mysql set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)
本文第二章就是用读未提交来举例的。 在多个并行的会话中启动事务一个事务在改动数据库哪怕没有commit提交其他事务也是能够实时的看到它修改的数据。一个事务在执行中读到另一个执行中事务的更新(或其他操作)但是未commit的数据这种不合理的现象叫做脏读(dirty read) 读未提交的其他不合理现象还有不可重复读、幻读 读未提交几乎没有加锁虽然效率高但是问题太多严重不建议采用。 4.2读提交【Read Committed】
使用者jly
--1、先设置当前的全局隔离级别为读提交再退出MySQL重新登录一下让其生效
mysql set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
--2、开始事务
mysql begin;
Query OK, 0 rows affected (0.00 sec)
--3、插入一条数据
mysql insert into account values (4,赵六,123);
Query OK, 1 row affected (0.01 sec)
--4、提交
mysql commit;
Query OK, 0 rows affected (0.02 sec)
使用者root
--开始事务
mysql begin;
Query OK, 0 rows affected (0.00 sec)
--当上方用户执行完第3步时查看表数据并没有看到插入的赵六的信息
mysql select* from account;
---------------------
| id | name | blance |
---------------------
| 1 | 张三 | 1234.50 |
| 2 | 李四 | 998.56 |
| 3 | 王五 | 653.25 |
---------------------
3 rows in set (0.01 sec)
--当上方用户执行完第4步时查看表数据能够看到赵六的信息
mysql select* from account;
---------------------
| id | name | blance |
---------------------
| 1 | 张三 | 1234.50 |
| 2 | 李四 | 998.56 |
| 3 | 王五 | 653.25 |
| 4 | 赵六 | 123.00 |
---------------------
4 rows in set (0.02 sec)
通过试验可以发现读提交级别事务A在commit提交事务之前所做的修改是不会被其他事务看到的一旦事务A发起commit之后其他事务就能看到事务A对数据的修改。这就造成了其他事务在不同的时间点select查看数据库时会查到不同的数据。这种现象叫不可重复读。事务中的读取不是原子的
4.3可重复读【Repeatable Read】
可重复读是MySQL默认的隔离级别。
使用者jly
--1、重启MySQL
systemctl restart mysqld
--2、重启后查看默认的隔离级别为读提交
mysql select tx_isolation;
-----------------
| tx_isolation |
-----------------
| REPEATABLE-READ |
-----------------
1 row in set, 1 warning (0.00 sec)
--3、启动事务
mysql begin;
Query OK, 0 rows affected (0.00 sec)
--4、将id为2的名字李四修改为张三
mysql update account set name张三 where id2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql select* from account;
---------------------
| id | name | blance |
---------------------
| 1 | 张三 | 1234.50 |
| 2 | 张三 | 998.56 |
| 3 | 王五 | 653.25 |
| 4 | 赵六 | 123.00 |
---------------------
4 rows in set (0.01 sec)
--5、提交事务
mysql commit;
Query OK, 0 rows affected (0.00 sec)
使用者root
--启动事务
mysql begin;
Query OK, 0 rows affected (0.00 sec)
--当上方用户执行完第4步时查看表数据李四并没有被修改为张三
mysql select *from account;
---------------------
| id | name | blance |
---------------------
| 1 | 张三 | 1234.50 |
| 2 | 李四 | 998.56 |
| 3 | 王五 | 653.25 |
| 4 | 赵六 | 123.00 |
---------------------
4 rows in set (0.00 sec)
--提交事务
mysql commit;
Query OK, 0 rows affected (0.00 sec)
--当上方用户执行完第5步时查看表数据李四被修改为张三
mysql select *from account;
---------------------
| id | name | blance |
---------------------
| 1 | 张三 | 1234.50 |
| 2 | 张三 | 998.56 |
| 3 | 王五 | 653.25 |
| 4 | 赵六 | 123.00 |
---------------------
4 rows in set (0.00 sec)
在该隔离级别下MySQL的一个事务在commit提交前不会影响到另一个事务的读取该隔离级别被称为可重复读。 一般的数据库在可重复读情况的时候无法屏蔽其他事务insert的数据因为隔离性实现是对数据加锁完成的而insert待插入的数据因为并不存在那么一般加锁无法屏蔽这类问题,这会造成大部分内容虽然是可重复读的但是insert的数据在可重复读情况被读取出来导致多次查找时会多查找出来新的记录就如同产生了幻觉。这种现象叫做幻读(phantom read主要针对插入场景)。很明显MySQL在RR级别的时候是解决了幻读问题的(解决的方式是用Next-Key锁 (GAP行锁)解决的。) 4.4串行化【Serializable】
串行化就是对所有事务进行加锁事务的执行全部挨个排队这就导致了效率低下问题。 开启事务A和事务B两个事务同时select读取将使用共享锁不会串行化事务A中有更新等操作会阻塞A直到事务B提交。如果事务A阻塞时间过长将会由于锁等待超时退出当前事务。
