ITPub博客

首页 > 数据库 > Oracle > Oracle 的数据库事务

Oracle 的数据库事务

原创 Oracle 作者:moonsoft 时间:2019-03-27 07:39:06 0 删除 编辑

这3个视图比较有用,了解他们就知道了究竟数据库事务在DB里是怎么回事.

[@more@]

select *

from books

update books

set author='author'

where ISBN='ISBN123456789'

--1 row updated

select d.session_id sid, d. lock_type, d.mode_requested, d.mode_held, d.blocking_others

from dba_locks d, v$session v --都是为视图,DBA权限有关

where

v.username='SYS'

and d.session_id=v.sid

SID LOCK _TYPE MODE_REQUESTED MODE_HELD BLOCKING_OTHERS

144 DML None Row-X (SX) Not Blocking

144 Transaction None Exclusive Not Blocking

Row_x(SX): 防止结构被更改

Exclusive(唯一):防止其它Session(其它用户, 也可能一个用户用两个session)将这列数据 修改

查看被HOLDTABLE:

select dbsession.username,dblock.lock_type, dblock.mode_held, dbob.object_name, dbob.object_type

from dba_lock dblock, v$session dbsession, dba_objects dbob

where

dblock.session_id=dbsession.sid

and dbob.object_id=dblock.LOCK_ID1

and dbsession.username='SYS'

如果同时另开一个session,对同一个table , 同一rowupdate 动作,那么会被hold .直到前一个session commit or rollback 动作为止.

可以利用savepoint 进行部分倒回. 语句是:

Rollback to savepoint a;

Example:

/* Formatted on 2006/04/27 10:33 (Formatter Plus v4.8.7) */

BEGIN

UPDATE books

SET author = 'new_author1'

WHERE isbn = 'ISBN123456789';

SAVEPOINT a;

UPDATE books

SET author = 'new_author2'

WHERE isbn = 'ISBN123456789';

SAVEPOINT b;

ROLLBACK TO SAVEPOINT a;

END;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/226700/viewspace-830821/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-20

  • 博文量
    412
  • 访问量
    335039