事务基本概念
对于事务而言，它需要满足ACID特性，下面就简要的说说事务的ACID特性。
A，表示原子性；原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个sql语句执行失败，那么已经执行成功的sql语句也必须撤销，数据库状态应该退回到执行事务前的状态；
C，表示一致性；也就是说一致性指事务将数据库从一种状态转变为另一种一致的状态，在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏；
I，表示隔离性；隔离性也叫做并发控制、可串行化或者锁。事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离，即该事务提交前对其它事务都不可见，这通常使用锁来实现；
D，持久性，表示事务一旦提交了，其结果就是永久性的，也就是数据就已经写入到数据库了，如果发生了宕机等事故，数据库也能将数据恢复。
MySQL中使用事务
理论总结的再好，终归都要通过实践来进行理解。下面就来说说MySQL中是如何使用事务的。
在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。因此要显示地开启一个事务须使用命令BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
来看看我们可以使用哪些事务控制语句。
BEGIN或START TRANSACTION；显示地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier；把事务回滚到标记点；
SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
事务的隔离级别
设置隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
注意：默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。
如果使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。你需要SUPER权限来做这个。
使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。 任何客户端都能自由改变会话隔离级别（甚至在事务的中间），或者为下一个事务设置隔离级别。
隐式提交
有的时候有些SQL语句会产生一个隐式的提交操作，即执行完成这些语句后，会有一个隐式的COMMIT操作。有以下SQL语句，不用你去“管”：
DDL语句，ALTER DATABASE、ALTER EVENT、ALTER PROCEDURE、ALTER TABLE、ALTER VIEW、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE等；
修改MYSQL架构的语句，CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD；
管理语句，ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE等。
以上的这些SQL操作都是隐式的提交操作，不需要手动显式提交。
1、查询
-- 查询全局事务
SELECT @@global.tx_isolation;
-- 查询会话事务
SELECT @@session.tx_isolation;
-- 或者使用
SELECT @@tx_isolation;
2、设置
-- 设置全局事务
set global transaction isolation level read committed;
-- 设置会话事务
set session transaction isolation level read committed;
-- 设置下一个操作的事务
set transaction isolation level repeatable read;
3、查询事务提交设置
SELECT @@autocommit;
-- 默认为1 即自动提交，0手动提交
SET autocommit = 1;
4、操作
-- 如果没有设置事务提交方式为手动提交，那么需要显示声明一个开启事务的操作
START TRANSACTION; -- 或者 BEGIN;
-- 执行操作语句
INSERT INTO t_hwp_test(user_id,user_name)VALUES(6,'superAdmin');
-- 设置一个保存点，事务可以回滚到指定保存点处，那么在此节点之前的操作将会有效
SAVEPOINT tx;
-- 执行操作（如果这里这条语句发生错误，那么脚本执行就结束了，下面的语句不会继续执行，数据库也不会给你自动回滚，该事务也还没有提交，只能执行脚本手动回滚事务）
INSERT INTO t_hwp_test(user_id,user_name)VALUES(4,'superAdmin');
-- 手动回滚到指定保存点处
ROLLBACK TO tx;
-- 最后提交，否则有可能其他连接看不到你未提交的值
COMMIT;
5、注意
如果在上面的操作语句中发生了错误，不要期盼数据库会给你自动回滚，而是在那儿就会停止执行，事务如果还没有提交，需要手动回滚。
6、存储过程提交设置事务
1）内部使用事务支持
也就是在存储过程内部声明事务开启与提交，这样的坏处就是，你无法回滚这些已经提交了的事务！
2）外部使用事务支持
也就是在调用存储过程的前后加一个事务控制，好处就是存储过程执行的操作并没有真正提交，等到执行了 COMMIT才会提交。
-- 如果存在删除调用存储过程
DROP PROCEDURE IF EXISTS sp_call_jobs;
-- 创建调用存储过程
CREATE PROCEDURE sp_call_jobs()
BEGIN
DECLARE
_row,
_err,
_count INT DEFAULT 0;
-- 遇到错误继续往下执行，类似于 try-catch
DECLARE
CONTINUE HANDLER FOR SQLEXCEPTION,
SQLWARNING,
NOT FOUND
SET _err = 1;
WHILE _row < 3 DO
-- START TRANSACTION; 在里面开启事务
INSERT INTO t_hwp_test(user_id,user_name,user_status,user_type,relative_id,account_id,user_unit,user_update_time)
VALUES(19,'测试1112',1,7,2,2,'系统','2016-01-07 16:39:36');
--COMMIT; 提交事务
IF _err = 1 THEN
SET _count = _count + 1;
END IF;
SET _row = _row + 1;
END WHILE;
SELECT
_count;
-- 使用外部事务的方式调用存储过程
START TRANSACTION;
CALL sp_call_jobs();
COMMIT;
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2018-8-28 14:07:30 |
赞(
操作
