oracle transaction [message #661359] |
Wed, 15 March 2017 21:29 |
|
asliyanage
Messages: 60 Registered: January 2017
|
Member |
|
|
I need to check the locking in oracle. SO i use sql developer and sqlplus. first i update all rows in salary column in employees table. then before committing them the i check the values from sqlplus. i can see the old data. still not updated, but the problem is those data is not locked yet. even the commit not finished first DML operation , i can access old data from sqlplus ? why the lock released before commit happens ?
[Updated on: Wed, 15 March 2017 21:34] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Re: oracle transaction [message #661378 is a reply to message #661369] |
Thu, 16 March 2017 07:30 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
When you change a row in a session, it locks the row from being updated by a different session until you commit. It never blocks someone reading the row and the other session will see the old data until you commit and then they will see the new data.
|
|
|
|
|
Re: oracle transaction [message #661404 is a reply to message #661361] |
Thu, 16 March 2017 13:52 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
asliyanage wrote on Wed, 15 March 2017 22:59but i read below
when DML operations are performed on existing rows, the affected rows are locked by
Oracle, and hence no other user can perform a DML operation on those rows
A lot of documents (included ones by Oracle) treat term DML loosely (in some cases it includes SELECT and in others it doesn't). For example, Oracle uses term DML triggers even though triggers on SELECT aren't allowed. Same applies to transactions. IN fact, Oracle doesn't even open transaction when SELECT is issued as first statement after previous transaction ends:
SQL> select count(*) from gv$transaction;
COUNT(*)
----------
0
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select count(*) from gv$transaction;
COUNT(*)
----------
0
SQL> set transaction read only;
Transaction set.
SQL>
As you can see, no transaction record was created and I was able to issue set transaction even though it wasn't first statement. Compare with:
SQL> select count(*) from gv$transaction;
COUNT(*)
----------
0
SQL> update emp set sal = sal;
14 rows updated.
SQL> select count(*) from gv$transaction;
COUNT(*)
----------
1
SQL> set transaction read only;
set transaction read only
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
SQL>
But there is a caveat - SELECT FOR UPDATE which does lock selected row. In any case, read Oracle docs Data Concurrency and Consistency
SY.
|
|
|
|
|