SQL基础-->数据库事务(TRANSACTION)

小白的学习笔记

于 2016-03-15 23:25:24 发布

阅读量488

点赞数

分类专栏：面试/其他数据库/Mysql

数据库/Mysql 同时被 2 个专栏收录

16 篇文章 0 订阅

订阅专栏

面试/其他

12 篇文章 0 订阅

订阅专栏

--======================

--SQL基础-->数据库事务

--======================

一、数据库事务

数据库事务是指作为单个逻辑工作单元执行的一系列操作，可以认为事务就是一组不可分割的SQL语句

二、数据库事务的ACID属性

原子性（atomic）

　　事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

一致性（consistent）

　　事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务

的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如B 树索引或双向链

表）都必须是正确的。

从一个正确状态转移到另一个正确状态。

隔离性（Isolation）

　　由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状

态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中

间状态的数据。(注:更正@20131218)

持久性（Duration）

　　事务完成之后，它对于系统的影响是永久性的。

三、事务的组成

一个数据库事务可由：

一个或多个DML语句组成

一个DDL语句组成

一个DCL语句组成

一个数据事务由：

以第一个DML语句的执行作为开始

以下面的其中之一作为结束

commit （提交）

rollback （回滚）

ddl或dcl语句（自动提交）

用户会话正常结束（自动提交）

系统异常终止（自动回滚）

系统崩溃（自动回滚）

四、事务的提交或回滚(COMMIT、ROLLBACK)

--使用COMMIT事务，robinson记录被插入到表

SQL> INSERT INTO scott.emp(empno,ename,job,salary)

2 VALUES(9999,'Robinson','DBA',3500);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> UPDATE scott.emp SET salary = 4000 WHERE ename = 'Robinson';

1 row updated.

--使用ROLLBACK回滚，更新将失效，最终结果如下面的查询

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT * FROM scott.emp WHERE ename = 'Robinson';

EMPNO ENAME JOB MGR HIREDATE SALARY DEPTNO

---------- ------------------------------ --------- ---------- --------- ---------- ----------

9999 Robinson DBA 3500

五、保存点

设置保存点：

SAVEPOINT NAME

恢复至保存点：

ROLLBACK TO NAME

--查看empno为，的记录

SQL> SELECT * FROM scott.emp WHERE empno IN (1235,1236);

EMPNO ENAME JOB MGR HIREDATE SALARY DEPTNO

---------- ------------------------------ --------- ---------- --------- ---------- ----------

1235 Tony boy 28-JUN-10 7100

1236 Ben IT 28-JUN-10 3100 20

--首先更新empno 为的salary ，并设置了一个保存点tran1

SQL> UPDATE scott.emp SET salary = salary * 1.5 WHERE empno = 1236;

1 row updated.

SQL> SAVEPOINT tran1;

Savepoint created.

--更新empno 为的salary

SQL> UPDATE scott.emp SET salary = salary * 0.8 WHERE empno = 1235;

1 row updated.

--查看刚刚更新的两条记录

SQL> SELECT * FROM scott.emp WHERE empno IN (1235,1236);

EMPNO ENAME JOB MGR HIREDATE SALARY DEPTNO

---------- ------------------------------ --------- ---------- --------- ---------- ----------

1235 Tony boy 28-JUN-10 5680

1236 Ben IT 28-JUN-10 4650 20

--将事务回滚到保存点tran1

SQL> ROLLBACK TO SAVEPOINT tran1;

Rollback complete.

--可以看到保存点之后的修改被回滚，而保存点之前的修改则不受影响

SQL> SELECT * FROM scott.emp WHERE empno IN (1235,1236);

EMPNO ENAME JOB MGR HIREDATE SALARY DEPTNO

---------- ------------------------------ --------- ---------- --------- ---------- ----------

1235 Tony boy 28-JUN-10 7100

1236 Ben IT 28-JUN-10 4650 20

--对所作的修改全部ROLLBACK，此时仅仅包含了empno 为的记录,因为已经被回滚

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT * FROM scott.emp WHERE empno IN (1235,1236);

EMPNO ENAME JOB MGR HIREDATE SALARY DEPTNO

---------- ------------------------------ --------- ---------- --------- ---------- ----------

1235 Tony boy 28-JUN-10 7100

1236 Ben IT 28-JUN-10 3100 20

六、事务的开始与结束及不同时刻的状态

事务的开始

连接到数据并执行了一条DML语句(INSERT ,UPDATE,DELETE)

前一条事务结束后，又执行了另外一条DML语句

事务的结束

执行COMMIT 或ROLLBACK

执行DDL语句，则自动提交并结束事务

执行DCL语句，则自动提交并结束事务

断开与数据库的连接，如退出SQL Plus。

Windows下SQL Plus正常退出将执行COMMIT，如点击关闭窗口直接关闭则回滚

X-Window下SQL Plus正常退出将执行COMMIT，如点击关闭窗口直接关闭则回滚(笔者在RHEL5下测试如此)

DML语句执行失败则自动回滚

提交或回滚前的数据状态

改变前的数据状态是可以恢复的

执行DML 操作的用户可以通过SELECT 语句查询之前的修正

其他用户不能看到当前用户所做的改变，直到当前用户结束事务。

DML语句所涉及到的行被锁定，其他用户不能操作

提交后的数据状态

数据的改变已经被保存到数据库中。

改变前的数据已经丢失。

所有用户可以看到结果。

锁被释放，其他用户可以操作涉及到的数据。

所有保存点被释放。

七、并发事务

多个用户同时与数据库交互，且每个用户都可以同时访问自己的事物，这种事务称为并发事务

对于同一个对象上运行的多个事务，仅当执行commit时才对彼此的查询产生影响

下表中演示了并发事务的处理：

	Trans1	Trans2
T1	SQL> SELECT COUNT(1) FROM emp; COUNT(1) ---------- 16	SQL> SELECT COUNT(1) FROM scott.emp; COUNT(1) ---------- 16
T2	SQL> INSERT INTO emp(empno,ename,salary) 2 SELECT 6666,'Jenney',3000 FROM DUAL;
T3	SQL> UPDATE emp 2 SET salary = salary + 200 WHERE ename = 'SCOTT';
T4	SQL> SELECT COUNT(1) FROM emp; COUNT(1) ---------- 17	SQL> SELECT COUNT(1) FROM scott.emp; COUNT(1) ---------- 16
T5	SQL> COMMIT;
T6		SQL> SELECT COUNT(1) FROM scott.emp; COUNT(1) ---------- 17