mysql中TCL事务控制语言

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

THE END
喜欢就支持一下吧
点赞5 分享