|
|
|
|
| Re: read only transaction [message #148153 is a reply to message #147963] |
Tue, 22 November 2005 23:17   |
contactkeval
Messages: 23
Registered: October 2005
|
Junior Member |
|
|
|
yes, i agree that commit / rollback will end the transaction but long running readonly transaction has got nothing to do with error like ORA-1555 i.e. snapshot too old error unless you have *transaction-level read consistency* the default is *statement-level read consistency*.
|
|
|
|
|
|
| Re: read only transaction [message #148188 is a reply to message #148154] |
Wed, 23 November 2005 00:51 |
contactkeval
Messages: 23
Registered: October 2005
|
Junior Member |
|
|
| cmerry wrote on Wed, 23 November 2005 11:03 |
Umm...that's what SET TRANSACTION READ ONLY does...it creates the transaction-level read consistency.
And yes, snapshot too old is an issue here. How else would Oracle be able to see old versions of the data? If the rollback segments (or undo) are needed for transactions, the read consistency data will no longer be available, hence the snapshot too old exception.
|
read only transaction will not allow DML operations like insert, update, delete will let users fire select queries.
where as read consistency is defined by *isolation level*
go thru following webpage:
http://www.ss64.com/orasyntax/10locking.html
|
|
|
|
|
|
| Re: read only transaction [message #148267 is a reply to message #148198] |
Wed, 23 November 2005 05:56 |
contactkeval
Messages: 23
Registered: October 2005
|
Junior Member |
|
|
Point 1 : both [ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
& SET TRANSACTION READ ONLY ] the statements are not similar
try following
time session1 session2
1 SET TRANSACTION READ ONLY
2 SET TRANSACTION ISOLATION LEVEL
[B]READ COMMITTED[/B]
3 select sal from emp
where empno = 7788
SAL = [B]3000[/B] --output
4 update emp set sal = 4000
where empno = 7788
5 commit
6 select sal from emp
where empno = 7788
SAL = [B]4000[/B] --output
as the other transaction has commited it will show the first user updated value.
but if isolation level is at transaction level, like below
time session1 session2
1 SET TRANSACTION READ ONLY
2 SET TRANSACTION ISOLATION LEVEL
[B]SERIALIZABLE[/B]
3 select sal from emp
where empno = 7788
SAL = [B]4000[/B] --output
4 update emp set sal = 3000
where empno = 7788
5 commit
6 select sal from emp
where empno = 7788
SAL = [B]4000[/B] --output
it will have to fetch old image which was at the start of the transaction. here is probability of snapshot too old error as old image might have been overwritten.
but in the first instance just because transaction has been very long does not necessarily cause any ORA 1555 error.
only point i am trying to make is ORA 1555 occurs if overwritten rollback data is referred. and it will not be the case with long running read only transaction unless it has *transaction-level read consistency*
|
|
|
|
| Re: read only transaction [message #148336 is a reply to message #147962] |
Wed, 23 November 2005 13:20 |
 |
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
|
Senior Member |
|
|
From chapter 7 of the Application Developer's Guide - Fundementals book:
| Quote: |
Ensuring Repeatable Reads with Read-Only Transactions
By default, the consistency model for Oracle Database guarantees statement-level read consistency, but does not guarantee transaction-level read consistency (repeatable reads). If you want transaction-level read consistency, and if your transaction does not require updates, then you can specify a read-only transaction. After indicating that your transaction is read-only, you can execute as many queries as you like against any database table, knowing that the results of each query in the read-only transaction are consistent with respect to a single point in time.
A read-only transaction does not acquire any additional data locks to provide transaction-level read consistency. The multi-version consistency model used for statement-level read consistency is used to provide transaction-level read consistency; all queries return information with respect to the system change number (SCN) determined when the read-only transaction begins. Because no data locks are acquired, other transactions can query and update data being queried concurrently by a read-only transaction.
Long-running queries sometimes fail because undo information required for consistent read operations is no longer available. This happens when committed undo blocks are overwritten by active transactions. Automatic undo management provides a way to explicitly control when undo space can be reused; that is, how long undo information is retained. Your database administrator can specify a retention period by using the parameter UNDO_RETENTION.
|
Here's the problem with your example.
SET TRANSACTION READ ONLY
is a statement all by itself. You immediately overrode that statement with
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
If you look back at my post above, I specified
SET TRANSACTION READ ONLY
which is similar to
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
in the fact that they both provide read-consistent views of the data. The latter statement does allow for DML.
Since you must have an example, try this:
SESSION 1 SESSION 2
-------------------------- ----------------------------------
SQL> CREATE TABLE t
2 (c NUMBER);
Table created.
SQL> INSERT INTO t
2 SELECT LEVEL
3 FROM dual
4 CONNECT BY LEVEL <= 10;
10 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t;
C
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> SELECT * FROM t;
C
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> SET TRANSACTION READ ONLY;
Transaction set.
SQL> UPDATE t
2 SET c = 1;
10 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t;
C
----------
1
1
1
1
1
1
1
1
1
1
10 rows selected.
SQL> SELECT * FROM t;
C
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t;
C
----------
1
1
1
1
1
1
1
1
1
1
10 rows selected.
I think we've killed this topic may we move on now??? Please!!!
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
