半夏微凉

半夏微凉

十一、事务和锁——确保数据安全

1.   事务概述

      事务管理是数据库处理的核心。数据库既要保证用户能并发地执行事务,还要保证数据库的一致性。

      当第一条可执行的SQL开始执行,就隐形地开始了一个事务,直到遇到下面的几种情况:

      1.Commit:提交事务。

      2.RollBack:回滚。

      3.DDL语句:执行和提交DDL语句结果前首先提交当前所有DML语句,成为隐式提交。

      4.程序正常退出:自动提交。

      5.不正常的程序失败:隐式回滚。

2.事务的类型:

(1)显式事务。得用命令完成的事务为显式事务。

新事务开始

sql statement

...

commit|rollback;

 

(2)隐性事务

无须描述事务的开始,只需提交或回滚每个事务。隐性事务模式生成连续的事务链。

默认情况下,隐性事务模式设置为关闭。

开启隐性事务模式语法:

SET IMPLICIT_TRANSACTIONS ON;

3.事务的特性(ACID)

A (Atomicity)   原子性

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

C (Consistency) 一致性 

事务在完成时,必须使所有的数据都保持一致状态。在相关数据库中,所有规则都必须应用于事务的修改,以保持所有数据的完整性。事务结束时,所有的内部数据结构都必须是正确的。

 I (Isolation)   隔离性

由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务识别数据时数据所处的状态,要么是另一并发事务修改它之前的状态,要么是第二个事务修改它之后的状态,事务不会识别中间状态的数据。这称为可串行性,因为它能够重新装载起始数据,并且重播一系列事务,以使数据结束时的状态与原始事务执行的状态相同。

 D (Durability)  持久性

事务完成之后,它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

附:事务的原子性说明:

oracle的事务与SQL server以及sybase有很大的不同,举个例子,假如表a有以下记录:

field1

1

3

0

4

    其中对field1字段的约束条件是field1>0,那么当我们执行update a set field1=field1-1这条SQL语句时,将会出现一个错误,因为其中的第三条记录的field1等于0,减去1之后就会违反约束条件。

    那么在SQL server或sybase中,出现这种错误之后,必须使用回滚来使表a回到修改之前的情况,否则第一二条记录的修改将会被保留;而在ORACLE中,表a还是会保持原样,所有的记录不会有任何变化。

    这就是ORACLE事务的一个特性:原子性,SQL语句的效果是要么全部提交,要么全部回滚。如果语句出现错误,那么这条语句在错误之前的已经发生全部修改都回滚。

    在ORACLE的事务处理中,存储过程的调用也是作为一个语句来看待的。也就是说,在调用ORACLE中调用存储过程,如果存储过程中出现了错误,ORACLE会自动回滚到存储过程被调用前的情况。

    在SQL server和sybase中,事务是必须显示地开始的,否则每个语句本身就是一个事务,而在ORACLE中,事务是隐式的,没有办法自动提交,除非客户端的专门实现,例如ODBC和JDBC默认是会自动提交的(当然你也可以使用设置连接的属性来把事务的控制权取回来)。

    对于有些情况下,我们可能需要保存已经进行的修改,那就需要when others来处理了,在exception处理中加上when others then null就会忽略语句中的错误,继续下去。

    在ORACLE中,如下的语句:

    begin

      savepoint sp;

      update a set field1=field1-1;

    exception

      when others then

      rollback to sp;

    end;

    这种写法是完全没有必要的,因为这实际上是ORACLE自动事务的模仿,是一种重复。对于ORACLE事务的原子性和一致性已经实现的东西,实在没有必要再去重复。

 

4.事务的保存点

savepoint point_name; 保存点

rollback to point_name; 回滚到保存点

5、锁

(1)锁的分类

排他锁(写锁)。X

共享锁(读锁)。S

(2)锁的类型:

DML锁:数据锁,用于保护数据。

DDL锁:保护作用的对象的结构。

内部锁和闩锁:保持数据库的内部结构,由oracle完全自动调用。

(3)DML锁分为

TX锁:行级锁,或者事务锁。

TM锁:表级锁,主要用于在修改数据时防止表结构变化。

(4)TM锁有以下几种模式:

RS(ROW SHARE) 行级共享锁。

RX(ROW EXCLUSIVE)行级排他锁。

S(SHARE)共享锁。

SRX(SHARE ROW EXCLUSIVE)共享行级排他锁。不能对同一张表进行DML操作,也不能添加S锁。

X(EXCLUSIVE)排他锁。

6.锁等待和死锁。

update t_avg set jiage=45 where name='苹果';  ---事务1

update t_avg set jiage=56 where name='苹果';  ---事务2

如果事务1执行后没有提交,再执行事务2就会出现锁等待。

SQL窗口1:

update t_avg set jiage=45 where name='苹果';  ---事务1

update t_avg set jiage=16 where name='荔枝';  ---事务2

 

SQL窗口2:

update t_avg set jiage=45 where name='荔枝';  ---事务3

update t_avg set jiage=16 where name='苹果';  ---事务4

 

sql窗口1执行事务1并锁定名称为苹果的对象,

sql窗口2执行事务3并锁定名称为荔枝的对象,

 

此时sql窗口1再执行事务2就会出现死锁,因为它修改的对象已经被sql窗口2锁定。

同理,sql窗口2执行事务4也会出现死锁。因为修改的对象已经被sql窗口1锁定。

如何避免死锁:

1、良好的开发习惯,偶尔忘记提交事务会导致长时间占用资源。

2、操作的记录过多。尽可能分成几级提交事务。

3、逻辑错误。两个会话都想得到想要的资源。


评论回复


·