1.准备测试表及数据
create table TEST_SER
(
tid NUMBER,
tname VARCHAR2(10)
);
2.插入测试数据
insert into test_ser(tid,tname)
select rownum tid,'n_' || rownum tname from dual connect by level<=1000;
commit;
3.查看数据分布
select min(tid) as min_tid,max(tid) as max_tid,
dbms_rowid.rowid_object(rowid) as "object_id(数据对象号)",
dbms_rowid.rowid_relative_fno(rowid) as "file_id(相对文件号)",
dbms_rowid.rowid_block_number(rowid) as "block_id(在第几个块)"
--,dbms_rowid.rowid_row_number(rowid) as "num(在block中的行数)"
from test_ser
group by dbms_rowid.rowid_object(rowid),dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid);
MIN_TID MAX_TID object_id(对象号) file_id(文件号) block_id(在第几个块)
985 1000 73197 18 182
500 984 73197 18 181
1 499 73197 18 180
4.测试会话1
# T1 时间 设置事务级别 SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
# T2 时间
select tid,tname from test_ser where tid in(1,500);
TID TNAME
1 1 n_1
2 500 n_500
5.测试会话2
#T3 时间（可选项，因为默认是 READ COMMITTED ）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
#T4 时间
update test_ser t set t.tname='m_1' where t.tid=1;
#T5 时间
commit;
6.回到测试会话1
#T6 时间 (再次查询结果 和 T2 时刻一致）
select tid,tname from test_ser where tid in(1,500);
TID TNAME
1 1 n_1
2 500 n_500
#T7时间 （执行成功）
update test_ser t set t.tname='m_500' where t.tid=500;
#T8时间 （ORA-08117） 因为tid=499 和 会话1 中update的tid=1 的block_id 一样所以失败
update test_ser t set t.tname='m_499' where t.tid=499;
#T9时间
select tid,tname from test_ser where tid in(1,500);
TID TNAME
1 1 n_1
2 500 m_500
#T10 时间
commit;
#T11 时间
select tid,tname from test_ser where tid in(1,499,500);
TID TNAME
1 1 m_1
2 499 n_499
3 500 m_500
