oracle事务和锁(转)

If you use a SET TRANSACTION statement, then it must be the first statement in your transaction. However, a transaction need not have a SET TRANSACTIONstatement.

Syntax

set_transaction::=

 

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10005.htm

数据库事务概括

1. 说明

　　一组SQL,一个逻辑工作单位，执行时整体修改或者整体回退。

 

2.事务相关概念

　　1）事务的提交和回滚：COMMIT/ROLLBACK

　　2）事务的开始和结束

　　　　开始事务：连接到数据库，执行DML、DCL、DDL语句

　　　　结束事务： 1. 执行DDL(例如CREATE TABLE),DCL(例如GRANT),系统自动执行COMMIT语句

　　　　　　　　　 2. 执行COMMIT/ROLLBACK

　　　　　　　　　 3. 退出/断开数据库的连接自动执行COMMIT语句

　　　　　　　　　 4. 进程意外终止，事务自动rollback

　　　　　　　　　 5. 事务COMMIT时会生成一个唯一的系统变化号（SCN）保存到事务表

　　3）保存点（savepoint）： 可以在事务的任何地方设置保存点，以便ROLLBACK

　　4）事务的四个特性ACID :

　　　　1. Atomicity（原子性）: 事务中sql语句不可分割，要么都做，要么都不做

　　　　2. Consistency(一致性) ： 指事务操作前后，数据库中数据是一致的，数据满足业务规则约束（例如账户金额的转出和转入），与原子性对应。

　　　　3. Isolation（隔离性）：多个并发事务可以独立运行，而不能相互干扰，一个事务修改数据未提交前，其他事务看不到它所做的更改。

　　　　4. Durability（持久性）：事务提交后，数据的修改是永久的。

　　5） 死锁：当两个事务相互等待对方释放资源时，就会形成死锁，下面章节详细分析

 

oracle事务隔离级别

 

1 .两个事务并发访问数据库数据时可能存在的问题

　　1. 幻想读：

　　　　事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一行新记录并commit，恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这条新纪录就是幻想。

　　2. 不可重复读取：

　　　　事务T1读取一行记录，紧接着事务T2修改了T1刚刚读取的记录并commit，然后T1再次查询，发现与第一次读取的记录不同，这称为不可重复读。

　　3. 脏读：

　　　　事务T1更新了一行记录，还未提交所做的修改，这个T2读取了更新后的数据，然后T1执行回滚操作，取消刚才的修改，所以T2所读取的行就无效，也就是脏数据。

 

2.oracle事务隔离级别

oracle支持的隔离级别：（不支持脏读）

READ COMMITTED--不允许脏读，允许幻想读和不可重复读

SERIALIZABLE--以上三种都不允许

sql标准还支持READ UNCOMMITTED (三种都允许)和 REPEATABLE READ（不允许不可重复读和脏读，只允许幻想读）

以上区别在下面章节事务建立，隔离级别分析中说明

 

事务相关语句

 

1. 事务相关语句概括

SET TRANSACTION----设置事务属性
SET CONSTRAINT -----设置约束
SAVEPOINT ------------建立存储点
RELEASE SAVEPOINT --释放存储点
ROLLBACK---------------回滚
COMMIT------------------提交

 

2. 建立事务、隔离级别分析

1）建立事务

SET TRANSACTION READ ONLY--事务中不能有任何修改数据库中数据的操作语句，这包括 insert、update、delete、create语句

SET TRANSACTION READ WRITE--默认设置,该选项表示在事务中可以有访问语句、修改语句

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE--serialzable可以执行DML操作

注意：这些语句是互斥的，不能够同时设置两个或者两个以上的选项

 

2）read only

eg：

set transaction read only;
select * from student;

结果：

执行：

update student set name='小丸子' where id='ccc';

结果：

在行 3 上开始执行命令时出错:
update student set name='小丸子' where id='ccc'
错误报告:
SQL 错误: ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作
01456. 00000 -  "may not perform insert/delete/update operation inside a READ ONLY transaction"
*Cause:    A non-DDL insert/delete/update or select for update operation
           was attempted
*Action:   commit (or rollback) transaction, and re-execute

 

 

3） read write

eg：

set transaction read write;
select * from student;

update student set name='小丸子' where id='ccc';
select * from student;

结果：

transaction READ 成功。
1 行已更新。

结论：允许读写

 

 

4）isolation level read committed（可幻读和重复读）

　　1.建立两个事务如下：

事务1：

set transaction  read write;
select * from student;

事务2：

set transaction isolation level read committed;
select * from student;

　

　2. 在事务1中修改某行数据并commit

update student set score=88 where id='fff';
commit;

　3. 在事务2中查询结果如下：

select * from student;

结果：

结论：事务2的隔离级别为 isolation level read committed, 支持不可重复读

　　4. 在事务1中插入一行数据，并提交

insert into student values('iii','小梦','1',3,4,10);

　　5. 在事务2中查看：

select * from student;

结论：事务2隔离级别为 isolation level read committed, 允许幻想读

 

 

5） isolation level  serializable

1.建立两个事务如下：

事务1：

set transaction  read write;
select * from student;

事务2：

set transaction isolation level serializable;
select * from student;

　

　2. 在事务1中修改某行数据并commit

update student set score=88 where id='fff';
commit;

　3. 在事务2中查询结果如下：

select * from student;

结果：

结论：事务2的隔离级别为 isolation level serializable, 不支持不可重复读

　　4. 在事务1中插入一行数据，并提交

insert into student values('iii','小梦','1',3,4,10);

　　5. 在事务2中查看：

select * from student;

结论：事务2的隔离级别为 isolation level serializable, 不支持幻想读

 

ORACLE锁机制

1.  概括

　　1）说明

　　　　锁是一种机制，多个事务同时访问一个数据库对象时，该机制可以实现对并发的控制

　 　2） oracle中锁的类别

　　　　1.DDL锁： oracle自动的施加和释放

　　　　2.DML锁：事务开始时施加，使用Commit后者Rollback被释放、

　　　　3.内部锁： 由oracle自己管理以保护内部数据库结构

　　3）oracle锁的粒度

　　　　1. 行级锁（TX）：阻止该行上的DML操作，直到Commit或者Rollback

　　　　2. 表级锁（TM）：

　　　　3. 数据库级锁: eg： 将数据库锁定为只读模式 alter database open read only;

　　　　　　　　　　　eg: 将数据库设置为限制模式（导入导出数据库时使用）：alter system enable restricted session;

 

2.锁的模式

　　1)概括

　　说明：

　　　　1. 数字越大，级别越高

　　2）eg：

lock table student in row share mode;

lock table student in row exclusive mode;  --用于行的修改

lock table student in share mode;   --阻止其他DML操作

lock table student in share row exclusive mode; 　--阻止其他事务操作

lock table student in exclusive mode;  --独立访问使用

 

3.锁查看

1）概括

SELECT * FROM V$SESSION;--查看会话和锁的信息

SELECT * FROM V$SESSION_WAIT;--查看等待的会话信息

SELECT * FROM V$LOCK;--系统中所有锁

SELECT * FROM V$LOCKED_OBJECT;--系统中DML锁

2)eg:

set transaction read write;
insert into student values ('jjj','小欣','1',3,4,90);
SELECT * FROM V$LOCked_object;

分析：1. locked_mode:3  ,因为我们执行的是insert， 因而是行级排他锁 row exclusive mode

　　

死锁

 

1. 说明

　　1. 当两个用户希望持有对方的资源时就会发生死锁。即两个用户互相等待对方释放资源，oracle认定为产生了死锁，在这种情况下，将以牺牲一个用户作为代价，另一个用户继续执行，牺牲的用户的事务将回滚。

　　2. lORA-00060的错误并记录在数据库的日志文件alertSID.log中。同时在user_dump_dest下产生了一个跟踪文件，详细描述死锁的相关信息。 

 

2.死锁产生条件

　　1. Mutual exclusion（互斥）：资源不能被共享，只能由一个进程使用。
　　2. Hold and wait（请求并保持）：已经得到资源的进程可以再次申请新的资源。
　　3. No pre-emption（不可剥夺）：已经分配的资源不能从相应的进程中被强制地剥夺。
　　4. Circular wait（循环等待条件）：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。

 

3.死锁模拟

　　student table如下

　　1. 开两个进程（此处使用sqldeveloper模拟）建立两个事务

事务1：

set transaction read write;

事务2：

set transaction read write;

　

 

　　2. t1 时刻 事务1和事务2中分别执行如下语句

事务1：

update student set classno=8 where id='iii';

事务2：

update student set score=score+10 where id='jjj';

　结果如下：

1行已更新
1行已更新

 

 

　3. t2时刻事务1和事务2中分别执行如下语句

事务1：

update student set score=score+20 where id='jjj';

事务2：

update student set classno=9 where id='iii';

结果如下：

错误报告:
SQL 错误: ORA-00060: 等待资源时检测到死锁
00060. 00000 -  "deadlock detected while waiting for resource"
*Cause:    Transactions deadlocked one another while waiting for resources.
*Action:   Look at the trace file to see the transactions and resources
           involved. Retry if necessary.

注意：ScriptRunner处红色的叉，出于等待状态

 

　　4. commit 事务2，事务1结果如下：

 

 

注意：用时38.048s， 事务2commit前等待的时间

 

 4.解决死锁冲突

1）执行commit或者rollback结束事务

2）终止会话

还是借用3中死锁的例子

在等待资源时执行，查找阻塞会话

select sid,serial#,username from v$session where sid in (select blocking_session from v$session);

执行：

alter system kill session '423,896';

结果：

在行 10 上开始执行命令时出错:
alter system kill session '423,896'
错误报告:
SQL 错误: ORA-00027: 无法终止当前会话
00027. 00000 -  "cannot kill current session"
*Cause:    Attempted to use ALTER SYSTEM KILL SESSION to kill the current
           session.
*Action:   None.

解决方法：另起一个session，关闭当前session（If it is necessary to kill the current session, do so from another session.）

另起线程执行上面的语句：

alter system kill session '423,896';

结果如下：（session kill成功，死锁解除，事务1更新成功）

 

 

5. 事务和死锁预防总结

　　1. 避免应用不运行长事务。

　　2. 经常提交以避免长时间锁定行。

　　3. 避免使用LOCK命令锁定表。

　　4. 在非高峰期间执行DDL操作，在非高峰期间执行长时间运行的查询或事务。

 

　　另外需注意，需监测系统中死锁，调查为什么这些锁正被保持，频率；当死锁发生通过回滚事务rollback或者终止会话来解决它。

http://www.cnblogs.com/wishyouhappy/p/3698152.html

 

时间： 2024-10-24 18:40:48

oracle事务和锁(转)的相关文章

SQL Server和Oracle中的锁和死锁

1.锁的作用 ORACLE : 锁是一种机制,当某个数据库对象正被其他进程或用户修改时,这种机制可以保护它不被修改. 为了防止对象被两个不同的用户同时修改,数据库系统创建了锁这种复杂的数据结构,来实现锁定机制,这种机制使用在oracle中称为排队的队列结构,以串行的方式执行锁. 锁的作用如下: 1) 他们坚持一致性和完整性,在事务处理期间,数据和对象保证它们的一致性和完整性. 2) 但对象不是立即可用时,它们提供一种队列结构,这种结构允许所有会话都加入到等候对象的队列中, 3) 自动处理锁机制

oracle事务管理相关问题总结

1.oracle对一条sql语句的执行是怎么管理并发和恢复控制的? 一条符合语法的sql语句,定义了对数据库的操作.此操作执行的时刻,对应了数据库的一个数据状态.可以这样理解这个状态:到此执行时刻为止,没有任何数据库语句级操作正在并发执行;就是说实际上正在并发执行的多个语句级操作可以假定在此语句操作之后执行.这里强调语句级操作,是指如果一个事务包含多个操作语句,在此时刻实际已经执行了其中几个,此时刻也正在执行某一个语句,那么不能简单地认为前面几个执行的操作语句也还没发生,这是要看事务的隔离级别的

MySQL中的事务和锁简单用法测试

一直以来,对于MySQL中的事务和锁的内容是浅尝辄止,没有花时间了解过,在一次看同事排查的故障中有个问题引起了我的兴趣,虽然过去了很久,但是现在简单总结一下还是有一些收获. 首先我们初始化数据,事务的隔离级别还是MySQL默认的RR,存储引擎为InnoDB > create table test(id int,name varchar(30)); > insert into test values(1,'aa'); 开启一个会话,开启事务.会话1: [test]>start transa

浅谈sqlserver中的事务和锁

昨日"拜读"<sqlserver2005高级程序设计>和<SQL Server 2008编程入门经典(第3版)>这两本翻译后的中文版书籍.竟然发现目录结构大致一样,其讲解的内容几乎差不多.有抄袭的嫌疑.看到"事务和锁"那一张中,发现连举的小例子.表格都一模一样.哈哈...对这类书籍,真不想做太多评论了.国内那些翻译版的书籍嘛.说真的,大部分翻译得有点生硬.而那些"原创著作"嘛.大多是相互抄袭,空谈.就微软技术体系而言,如果

c#+oracle数据库行锁写法问题

问题描述 c#+oracle数据库行锁写法问题 请教各位高手: 我在c#+oracle,里面,想如此操作.当修改某一数据的前,先执行select行锁定,待修改完毕后再解锁.请问在不用存储过程的情况下,程序该如何写呢? 解决方案 最简单的是使用事务.http://www.cnblogs.com/yanghucheng/archive/2013/01/25/2876492.htmlhttp://happypigs.iteye.com/blog/1576282 解决方案二: 谢谢,能举个实际例子吗 解

Oracle数据完整性和锁机制简析_oracle

本课内容属于Oracle高级课程范畴,内容略微偏向理论性,但是与数据库程序开发和管理.优化密切相关:另外本课的部分内容在前面章节已经涉及,请注意理论联系实际. 事务 事务(Transaction)从 通讯的角度看:是用户定义的数据库操作序列,这些操作要么全做.要么全不做,是不可分割的一个工作单元.事务控制语句称为TCL,一般包括Commit和Rollback. 事务不是程序,事务和程序分属两个概念.在RDBMS中,一个事务可以有一条SQL语句.一组SQL语句或者整个程序:一个应用程序又通常包含多

Oracle行级锁的特殊用法简析_oracle

Oracle有许多的锁,各种锁的效用是不一样的.下面重点介绍Oracle行级锁,Oracle行级锁只对用户正在访问的行进行锁定.可以更好的保证数据的安全性. 如果该用户正在修改某行,那么其他用户就可以更新同一表中该行之外的数据. Oracle行级锁是一种排他锁,防止其他事务修改此行,但是不会阻止读取此行的操作. 在使用INSERT.UPDATE.DELETE 和SELECT-FOR UPDATE 等 语句时,Oracle会自动应用Oracle行级锁行级锁锁定.SELECT...FOR UPDAT

MySQL中的事务和锁简单测试

一直以来,对于MySQL中的事务和锁的内容是浅尝辄止,没有花时间了解过,在一次看同事排查的故障中有个问题引起了我的兴趣,虽然过去了很久,但是现在简单总结一下还是有一些收获. 首先我们初始化数据,事务的隔离级别还是MySQL默认的RR,存储引擎为InnoDB >create table test(id int,name varchar(30)); >insert into test values(1,'aa'); 开启一个会话,开启事务.会话1: [test]>start transact

Oracle事务控制总结

一.Oracle事务控制:     1.组成单个逻辑工作单元的一系列操作被称为事务.     2.数据库事务组成:          1.0~多条DML语句          2.1条DDL(Data Define Language)语句          3.1条DCL(Data Control Language)语句     3.事务特性(ACID):          -原子性(Atornicity)          -一致性(Consistency)          -隔离性(Iso