Oracle 的数据库事务

这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)将这列数据 修改

查看被HOLD的TABLE:

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 , 同一row做update 动作,那么会被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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/226700/viewspace-830821/