MySql的回顾十:TCL与事务的展示

　　山随宴坐图画出，水作夜窗风雨来。观水观山皆得妙，更将何物污灵台。经过风雨的洗礼，对面山上的树，路两旁的花草更加的鲜艳动人，路边的树又抽出新的枝芽，树下还有几朵菌类上的雨露显得熠熠动人，就是天气有几许丝凉。各位看官注意添加衣物。

　　今天继续有我带领读者们继续学习MySql的TCL语言，废话不多说直接上概念！冲呀...

Transaction Control Language 事物控制语言
事物:一个或一组SQL语句中组成一个执行单元,这个执行单元要么全部执行，要么全部不执行。每个SQL语句是相互依赖的。
而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。
所有受到影响的数据将返回到事物开始以前的状态；如果单元中所有的SQL语句均执行成功，则事物被顺利执行。
存储引擎(表类型)引入
1.概念:在MySql中的数据用各种不同的技术存储在文件(或内存)中。
2.通过SHOW ENGINES;嗯争斯来查看MySql支持的存储引擎。
3.在MySql中用的最多的存储引擎有INNODB,MYISAM,MEMORY等，其中INNODB支持事物，而MYISAM,MEMORY等不支持事物。

事物的ACID属性
1.原子性(Atomicity)原子性是指事物是一个不可分割的工作单位，事物中的操作要么都发生，要么都不发生。
2.一致性(Consistency)事物必须使数据库从一个一致性状态变换到另一个一致性状态。
3.隔离性(Isolation)事物的隔离性是指一个事物的执行不能被其他事物干扰,即一个事物
内部的操作及使用的数据对并发的其他事物是隔离的，并发执行的各个之间不能互相干扰。
4.持久性(Durability)持久性是指一个事物一旦被提交，它对数据库中数据改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

案例:转账,伪代码。喜羊羊准备给灰太狼转500让灰太狼协助羊村的围栏修复。
喜羊羊   1000
灰太狼   1000
UPDATE 表 SET 喜羊羊的余额=500 WHERE name='喜羊羊'
意外断开了，喜羊羊少了500，灰太狼该正常走以下代码。
UPDATE 表 SET 灰太狼的余额=1500 WHERE name='灰太狼'

 

查看引擎
SHOW ENGINES;

事务的创建

隐式事务: 事务没有明显的开启和结束的标记
比如INSERT,UPDATE,DALETE语句
DELETE FROM 表 WHERE id=1;

显示事务:事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用
SET autocommit=0;

步骤1:开启事物
SET autocommit=0;
start transcommit=0;
start transaction;可选的
步骤2:
编写事务中的SQL语句(SELECT INSERT UPDATE DELETE)
语句1;
语句2;
...

步骤3:结束事物
commit;提交事务
rollback;回滚事务

删除表
DROP TABLE IF EXISTS accoun_t;
创建表
CREATE TABLE accoun_t(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);
插入测试数据
INSERT INTO accoun_t(username,balance)
VALUES('喜羊羊',1000),('灰太狼',1000);
#演示事务的使用步骤:提交
#开启事务
SET autocommit=0;
START TRANSACTION;
#模拟编写一组事务
UPDATE accoun_t SET balance = 500 WHERE username="喜羊羊";
UPDATE accoun_t SET balance = 1500 WHERE username="灰太狼";
#结束事务:提交
COMMIT;
#查看表数据
SELECT * FROM accoun_t;

#演示事务的使用步骤:回滚
#开启事务
SET autocommit=0;
START TRANSACTION;
#模拟编写一组事务
UPDATE accoun_t SET balance = 1000 WHERE username="喜羊羊";
UPDATE accoun_t SET balance = 1000 WHERE username="灰太狼";

#结束事务回滚
ROLLBACK;
#查看表数据
SELECT * FROM accoun_t;

#没有变回1000

事务的原理

#事务的并发介绍
#对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题:
脏读:一个事物读取到另一个事物中尚未提交的数据。(没有被提交)
例:对于两个事务T1,T2.T1读取了已经被T2更新但还没有被提交的字段，之后若T2回滚，T1读取的内容就是临时且无效的。

不可重复读:一个事物中两次读取数据内容不一致，要求的是一个事务中多次读取数据是一致的，这是事物UPDATE时引发的问题。(更新)
例:对于两个事物T1,T2。T1读取了一个字段，然后T2更新了该字段之后,T1再次读取同一个字段,值就不同了。

幻读:一个事务中两次读取的数据的数量不一致,要求在一个事务多次读取的数据的数量是一致的，这是INSERT或者DELETE时引发的问题。(插入)
例:对于两个事物T1，T2。T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行。之后如果T1再次读取同一个表，就会多出几行。

事务的隔离级别

读未提交:允许事物读取未被其他事务提交的变更,脏读，不可重复读和幻读都会出现。
读已提交:只允许事务读取已经被其他事物提交的变更，可避免脏读，但不可重复读和幻读问题任然可能出现。
可重复读:确保事务可以多次从一个字段中读取相同的值,在这个事务持续期间,禁止其他事务对这个字段进行更新。
串行化:确保事务可以从一个表读取相同的行，在这个事务持续期间,禁止其他事务对该表执行插入,更新和删除操作,所有并发问题都可以避免,但性能十分低下。
每启动一个mysql程序，就会获得一个单独的数据库连接，每个数据库连接都有一个。
全局变量@@tx_isolation,表示当前事务隔离级别。
查看当前的隔离级别:SELECT @@tx_isolation;
设置当前MySql连接的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
设置数据库系统的全局的隔离级别:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

#数据库事务的隔离性:数据库系统必须具有隔离性并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题。
#一个事务与其他事务隔离的程度称为隔离级别。
#ORACLE:支持的2种事务隔离级别:READ COMMITED,SERIALIZABLE。ORACLE默认的事物隔离级别为:READ COMMITED。
#MySql支持4种事务隔离级别,Mysql默认的事物隔离级别为:REPEATABLE READ

脏读的演示

以下操作转入CMD或者LINUX下操作
查看默认的隔离级别
SELECT @@tx_isolation;

T1切换库
设置隔离级别
开启事务
查看表数据
USE girls;
set global transaction isolation level read uncommitted;
start transaction;

T2切换库
设置隔离级别
开启事务
查看表数据
USE girls;
set global transaction isolation level read uncommitted;
start transaction;
SELECT * FROM accoun_t;

T1修改数据
回滚
UPDATE accoun_t SET username='xiyangyang' WHERE id=1;
rollback;

脏读问题解决:
先把数据恢复
UPDATE accoun_t SET balance=500 WHERE id=1;
UPDATE accoun_t SET balance=1500 WHERE id=2;

 

T1设置隔离级别
set global transaction isolation level read committed;
start transaction;

T2退出重新进入
开启事务
切换库
mysql -u RemoteLogin -p
start transaction;
USE girls;

T1修改数据，不提交
UPDATE accoun_t SET balance=500 WHERE id=1;
UPDATE accoun_t SET balance=1500 WHERE id=2;

T2查询，未查到已修改的数据

T1进行提交

T2查到数据

不可重复读演示

T1先恢复数据
设置隔离级别
UPDATE accoun_t SET balance=1000 WHERE id=1;
SET global transaction isolation level read committed;

T2登陆数据库
切换库
开启事物
查看数据
mysql -u RemoteLogin -p
USE girls;
start transaction;
SELECT * FROM accoun_t;

T1开启事物
修改数据
提交数据
start transaction;
UPDATE accoun_t SET balance=1500 WHERE id=1;
commit;

T2查询发现与第一次查询到的数据不一致

解决方法
T1提升隔离级别
set global transaction isolation level repeatable READ;

T2重新登陆
开启事务
查看数据
mysql -u RemoteLogin -p
start transaction;
SELECT * FROM girls.accoun_t;

T1开启事务
修改数据
提交数据
start transaction;
UPDATE accoun_t SET balance=1300 WHERE id=1;
commit;

T2查看还是原来数据

如果想看最新数据，先提交再查看。

幻读演示
T1关闭事务
切换库
查看数据
SET autocommit=0;
USE girls;
SELECT * FROM accoun_t;

T2关闭事物
切换库
查看数据
新插入一行
提交
SET autocommit=0;
USE girls;
SELECT * FROM accoun_t;
INSERT INTO accoun_t values(NULL,'shiyuan',2000);
commit;

T1修改数据，结果显示改了四行。

解决方法串行化
T1提高事务等级串行化
关闭事物
查看数据
更新数据但不执行
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET autocommit=0;
SELECT * FROM accoun_t;
UPDATE accoun_t SET username='dayitong';   #先不执行

问题解决
T2开启事物串行化
关闭事物
插入一行数据
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET autocommit=0;
INSERT INTO accoun_t VALUES(NULL,'yazhou',5000);

T1执行修改数据
提交
UPDATE accoun_t SET username='dayitong';
commit;

T2等待超时被踢出

回滚点
关闭事务
切换库
查看数据
SET autocommit=0;
USE girls;
SELECT * FROM accoun_t;

把username全部改成喜羊羊
把username全部改成灰太狼
设置回滚点
再把username全部改成光头强
数据回档至存档
查看数据
UPDATE accoun_t SET username='喜羊羊';
UPDATE accoun_t SET username='灰太狼';
SAVEPOINT cundang;
UPDATE accoun_t SET username='光头强';
ROLLBACK TO cundang;
SELECT * FROM accoun_t;

DELETE和TRUNCATE在事物中的区别

#演示DELETE
关闭事务自动提交
开启事务
删除表
回滚
查看
SET autocommit=0;
START TRANSACTION;
DELETE FROM major2;
ROLLBACK;
SELECT * FROM major2;

#演示TRUNCATE
关闭事务的自动提交
开启事物
删除表
回滚
查看
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE major2;
ROLLBACK;
SELECT * FROM major2;

　　事务控制到此结束，希望看到这里的读者们有收获。咱们不见不散下次再见！o(^▽^)o