Transaction Serializable Isolation Level

Question

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;

Question1:

T1(transaction 1) and T2(transaction 2)

T1: select * from tableName where status = 'A';

T2: insert into tableName(id, status) values (1, 'I');

T2: commit;

T1: select * from tableName where status = 'I'; // Why T1 can not get the record with status I committed by T2? Is this because the snapshot created by T1 and what's the scope of this snapshot, the whole table?

Question2:

T1: insert into tableName(id, status) values (1, 'I');

T2: insert into tableName(id, status) values (1, 'I'); // T2 is blocked if id is unique. Why T2 blocked? Because as I know, both transactions create a snapshot, even they have not committed yet.

Are there any locks take the participant in this scenario? Thanks.

Answer 1

1. Is this because the snapshot created by T1

yes

2. and what's the scope of this snapshot, the whole table?

SCN

Question2: Why T2 blokced?

Because you have unique or primary key constraint and it's not deffered.