hello , i am reading <<Database Concepts>> ,
i can not understand "Transaction set consistency" clearly , will you explain this to me pls ? can you show me some simple examples to show what is "Transaction set consistency" ?
thanks .
the link is
https://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm#CIHHDFDH ;
A useful way to view the read committed and serializable isolation levels in Oracle Database is to consider the following scenario: Assume you have a collection of database tables (or any set of data), a particular sequence of reads of rows in those tables, and the set of transactions committed at any particular time. An operation (a query or a transaction) is transaction set consistent if all its reads return data written by the same set of committed transactions. An operation is not transaction set consistent if some reads reflect the changes of one set of transactions and other reads reflect changes made by other transactions. An operation that is not transaction set consistent in effect sees the database in a state that reflects no single set of committed transactions.thank u .
What it's getting at is "are query results consistent to a point in time?"
For example, say you have the following set of two transactions:
create table t (
x int
);
/* Tran 1 */
insert into t values (1);
commit;
/* Tran 2 */
update t set x = 2;
commit;
A query or transaction is "set consistent" if its queries on t return the same results, regardless of which of these transactions have completed.
All queries are set consistent in Oracle Database - the results are fixed at the time it starts.
But in the default mode, read committed, transactions are not. If you run transaction 1 (the insert), then in a separate session you run:
select * from t;
X
1
-- run tran 2 in the other session
select * from t;
X
2
The results are different. So each query sees the outcome of a different set of transactions. Thus they are not "set consistent".
But if you repeat the test in serializable mode, they are:
alter session set isolation_level = serializable;
select * from t;
X
1
-- run tran 2 in the other session
select * from t;
X
1
So the results are consistent to the same set of transactions; just tran 1.
You can read more about read consistency and isolation levels from Tom at:
http://www.oracle.com/technetwork/testcontent/o65asktom-082389.html