这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/