Is it correct for oracle locking

[kknight2046]

I found one statement from

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10743/consist.htm#i5704

that is very confusing:

"A query in a transaction can see the changes made by previous DML statements in the same transaction, but cannot see the changes of other transactions begun after its own transaction."

Is it correct? I found a case that shows the statement is wrong.
I made a test by first creating a test table:

create table test (id number);

Then, I started two transactions:

Transaction 1 Transaction 2
Time1: insert into test values (1);
Time2: insert into test values (2);
Time3: commit;
Time4: select * from test;

After executing the select query at Time4, I saw the results below:
ID
1
2

That means, Transaction 1 CAN see the changes of Transaction 2, though Transaction 2 begun after transaction 1.

Any thoughts?

 

[jimbopalmer]

You seem to have confused threads and transactions.

Any block of code that attempts to change data is a transaction. It always ends in a commit; or a rollback;

What you label as Transaction 1 is not (yet) a transaction, as it neither commits nor rollsback. It is just a tread of execution so far. The insert in thread 1 is not visible globally as it has not committed. As the select does not attempt to change data, it will never be part of Transaction 1, just Thread 1.

The Thread you label Transaction 2 is a valid transaction. Data from transaction 2 is visible globally once the commit; finishes.

Had the select begun before the commit finished, then the pre-commit data would have been used throughout the select, for consistancy.

I tried to remain child-like, all I acheived was childish.

 

[jimbopalmer]

http://www.adp-gmbh.ch/ora/concepts/transaction.html

I tried to remain child-like, all I acheived was childish.

 

[kknight2046]

In fact, Transaction 1 is a transaction, because by default the setAutoCommit is set to false in my sqlplus.

To make my example less confusing, I rewrote the example:

First,
create table test (id number);

Then, I started two transactions:

Transaction 1 Transaction 2
Time1: insert into test values (1);
Time2: insert into test values (2);
Time3: commit;
Time4: select * from test;
Time5: commit;

I think this time the select query is in Transaction 1, but the results of the select are:
ID
1
2

 

[kknight2046]

One thought:

In the statement, "A query in a transaction can see the changes made by previous DML statements in the same transaction, but cannot see the changes of other transactions begun after its own transaction.", does 'its own transaction' means the query transaction or the transaction the query is in?
If it means the query transaction, then it makes sense.

 

[jimbopalmer]

A SELECT statement itself is never part of a transaction, although you can have INSERTs, UPDATEs, or DELETEs with embedded SELECTs. The embedded SELECT will behave as stated.

I tried to remain child-like, all I acheived was childish.

 

[kknight2046]

jimbopalmer, many thanks. Your reply makes things clearer to me, but I still have a confusion.

I wrote another example:

First,
create table test (id number);

Then, I started two transactions:

Transaction 1 Transaction 2
Time1: insert into test values (1);
Time2: insert into test values (2);
Time3: commit;
Time4: insert into test
(select id*2 from test where id=2);
Time5: commit;
Time6: select * from test;

Time6's select yields:
ID
1
4
2

The results shows that at Time4 in Transaction 1, the select subquery in the insert statement CAN see the value 2 inserted by Transaction 2, though Transaction 2 began after transaction 1.

Any comments?

 

[SantaMufasa]

Yes, KKnight, I have some comments...When a transaction affects rows, it locks them against others making changes (and seeing your changes) on those rows until after the tranasction ends (with either a COMMIT or a ROLLBACK). That same transaction does not FREEZE the database at the specific point in time that the transaction begins (as it appears you might be presuming)...For a given session, whatever is "true" (i.e., COMMITted) in the database, plus whatever UN-COMMITted changes from that same session exist at the time of that same session's query(ies) are visible (i.e., are available to result sets) of that session.

That is why, in your example above, you see what you see: your session's uncommitted changes, plus any other committed data, that exists at the time of your query.

Does this answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]