mysql中TCL事务控制语言

#TCL
/*
Transaction Control Language 事务控制语言

事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

案例：转账

令狐冲  1000
任盈盈    1000

update 表 set 令狐冲的余额=500 where name='令狐冲'
意外
update 表 set 任盈盈的余额=1500 where name='任盈盈'

事务的特性：
ACID
原子性：一个事务不可再分割，要么都执行要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据.

 

事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句

delete from 表 where id =1;

显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用

set autocommit=0;

步骤1：开启事务
set autocommit=0;
start transaction;可选的，但是我个人强烈不建议省略
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...

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

savepoint 节点名;设置保存点

 

事务的隔离级别：
                                        脏读        不可重复读    幻读
read uncommitted：           √                 √               √
read committed：               ×                 √               √
repeatable read：              ×                 ×               √
serializable                          ×                 ×               ×

mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;

 

开启事务的语句;
update 表 set 令狐冲的余额=500 where name='令狐冲'

update 表 set 任盈盈的余额=1500 where name='任盈盈'
结束事务的语句;

 

*/

#查看事务自动提交是否开启(on表示开启，off表示关闭)
SHOW VARIABLES LIKE 'autocommit';
#查看数据库支持的存储引擎
SHOW ENGINES;

/*
在mysql中用的最多的存储引擎有：innodb，myisam ,memory等
其中innodb支持事务，而myisam、memory等不支持事务
*/
DROP TABLE IF EXISTS account;
CREATE TABLE account(
    id INT PRIMARY KEY,
    aName VARCHAR(40),
    balance DOUBLE
);
DESC account;
INSERT INTO account VALUES(1, '令狐冲', 1000), (2, '任盈盈', 1000);
SELECT * FROM account;

#演示事务的使用步骤
SET autocommit=0; #开启事务
#下面这句话可省略，但是我个人强烈不建议省略
START TRANSACTION;#可省略，但是我个人强烈不建议省略
#编写一组事务的语句
UPDATE account SET balance = balance - 500 WHERE aName = '令狐冲';
UPDATE account SET balance = balance + 500 WHERE aName = '任盈盈';
#结束事务
#commit;#提交事务
ROLLBACK;#回滚事务
#
SELECT * FROM account;
/*

Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ
Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔
离级别为: READ COMMITTED

*/
#
SHOW VARIABLES LIKE "%char%";

#查看当前的隔离级别
SELECT @@tx_isolation;

#事务的隔离级别
#设置当前 mySQL 连接的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
#设置当前 mySQL 连接的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
#设置数据库系统的全局的隔离级别(需要重启一下mysql服务)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

#
#查看事务自动提交是否开启(on表示开启，off表示关闭)
SHOW VARIABLES LIKE 'autocommit';
#查看数据库支持的存储引擎
SHOW ENGINES;

#
SET autocommit=0;
#SET autocommit=1;
INSERT INTO account VALUES(9, '韦小宝', 1600);
SELECT * FROM account;
ROLLBACK;

SELECT * FROM account;

 

#演示savepoint 的使用，savepoint只能和ROLLBACK TO搭配使用，不能和commit搭配使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id = 2;
SAVEPOINT sp1;#设置保存点
DELETE FROM account WHERE id = 8;
SAVEPOINT sp2;#设置保存点
DELETE FROM account WHERE id = 7;
ROLLBACK TO sp2;#回滚到保存点
#
SELECT * FROM account;

#delete和truncate在事务使用时的区别

#演示delete
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

#演示truncate
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

/*
truncate不支持事务，所以对于truncate，ROLLBACK关键字是无效的，
TRUNCATE TABLE 表名，这是DDL(数据定义语言)，如果是DDL语句，会产生一个隐
式的提交(即自动提交)操作，所以，ROLLBACK对于DDL语句是无效的，这也就解
释了为什么truncate不支持ROLLBACK回滚

*/

#
SELECT * FROM account;

/*
MYSQL中的事务是默认自动提交的
SET AUTOCOMMIT语句可以禁用或启用默认的autocommit模式，用于当前连接
可以用SET来改变mysql的自动提交模式
SET autocommit=0   禁止自动提交
SET autocommit=1 开启自动提交

START TRANSACTION 开始一个事务
ROLLBACK   事务回滚
COMMIT       事务确认

注意当你用 set autocommit=0 的时候，你以后所有的SQL都将做为事务处理，直
到你用commit确认或rollback结束，注意当你结束这个事务的同时也开启了个新的事务！

事务提交有两种方式：显式提交和自动提交。
1.显式提交：使用commit。
2.自动提交：执行DDL或DCL，或者程序正常退出。

隐式提交的SQL语句
以下这些SQL语句会产生一个隐式的提交操作，即执行完这些语句后，会有一个隐式的COMMIT操作。
(1)DDL语句：ALTER DATABASE...UPGRADE DATA DIRECTORY NAME、....

(2)用来隐式的修改mysql架构的操作：CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD。

(3)管理语句：ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE 、REPAIR TABLE。

数据库的事务语句组成
(1)一组DML（Data Manipulate Language，即数据操作语言）经过这组DML修改后数据将保持较好的一致性。

(2)一个DDL（Data Definition Language，即数据定义语言）语句。

(3)一个DCL（Data control Language，即数据控制语言）语句。

DDL和DCL语句最多只能有一个，因为DDL和DCL语句都会导致事务立即提交。

当事务所包含的全部数据库操作都成功执行后，应该提交（commit）事务，使这些修改永久生效。

#
以下情况的其中之一，可以作为事务的结束:
1.COMMIT 或 ROLLBACK 语句
2.DDL 或 DCL 语句(自动提交或者叫隐式提交)
3.用户会话正常结束
4.系统异常终了

*/

#
SELECT * FROM account;
INSERT INTO account VALUES(1, '令狐冲', 2600), (2, '韦小宝', 16800);
#
SHOW VARIABLES LIKE 'autocommit';

#(执行完后，效果是，account表中数据没有删掉了，ROLLBACK回滚有效，是因为设置了autocommit=0;)
SET autocommit=0;
START TRANSACTION;
#ALTER TABLE是DDL(数据定义语言)，会默认提交/隐式提交
ALTER TABLE emp5 MODIFY COLUMN Last_name VARCHAR(60);
DELETE FROM account;
ROLLBACK;
#

 

#(执行完后，效果是，account表中数据删掉了，ROLLBACK回滚无效，是因为设置了autocommit=1;)
SET autocommit=1;
START TRANSACTION;
#ALTER TABLE是DDL(数据定义语言)，会默认提交/隐式提交
ALTER TABLE emp5 MODIFY COLUMN Last_name VARCHAR(60);
DELETE FROM account;
ROLLBACK;
#

#(执行完后，效果是，account表中数据没有删掉了，ROLLBACK回滚有效，是因为后面设置了autocommit=0;)
SET autocommit=1;
START TRANSACTION;
#ALTER TABLE是DDL(数据定义语言)，会默认提交/隐式提交
ALTER TABLE emp5 MODIFY COLUMN Last_name VARCHAR(60);
SET autocommit=0;
DELETE FROM account;
ROLLBACK;

 

#ROLLBACK回滚有效
SET autocommit=0;
DELETE FROM account;
ROLLBACK;
#

#ROLLBACK回滚有效
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
#

#
SELECT * FROM account;

#ROLLBACK回滚无效
SET autocommit=1;
DELETE FROM account;
ROLLBACK;
#

#ROLLBACK回滚有效
SET autocommit=1;
START TRANSACTION;#开启事务
DELETE FROM account;#语句
ROLLBACK;#结束事务
#

#
SHOW VARIABLES LIKE 'autocommit';

/*
不管把autocommit设置成0还是1，无所谓。只要是START TRANSACTION;语句开头，就表示事
务的开始，直到遇到ROLLBACK;或COMMIT;才表示该事务的结束
*/
#ROLLBACK回滚有效
START TRANSACTION; #开启事务
DELETE FROM account; #语句
DELETE FROM author; #语句
ROLLBACK; #结束事务
#

SELECT * FROM account;
SELECT * FROM author;

 

本文地址：https://blog.csdn.net/czh500/article/details/85965237