Home » SQL & PL/SQL » SQL & PL/SQL » oracle transaction
oracle transaction [message #661359] Wed, 15 March 2017 21:29 Go to next message
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 #661360 is a reply to message #661359] Wed, 15 March 2017 21:37 Go to previous messageGo to next message
BlackSwan
Messages: 26193
Registered: January 2009
Location: SoCal
Senior Member
Correct.
With Oracle Reader do NOT block Writers & writers do not block Readers.
It is working as designed.
It appears that you have decided to not Read The Fine Manual; Concepts Manual.
Locking is a User Hostile situation in an online multi-user environment & should be kept to the least needed minimum.
Re: oracle transaction [message #661361 is a reply to message #661360] Wed, 15 March 2017 21:59 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
but 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
Re: oracle transaction [message #661362 is a reply to message #661361] Wed, 15 March 2017 22:45 Go to previous messageGo to next message
BlackSwan
Messages: 26193
Registered: January 2009
Location: SoCal
Senior Member
asliyanage wrote on Wed, 15 March 2017 19:59
but 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
above is correct.

post SQL & results that show any different results.
SELECT is not DML.
Re: oracle transaction [message #661364 is a reply to message #661359] Thu, 16 March 2017 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You still did not post your Oracle version.
And you still did not read the documentation we pointed you to:
Database Concepts
Database SQL Reference

Re: oracle transaction [message #661366 is a reply to message #661364] Thu, 16 March 2017 03:24 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
Java(TM) Platform 1.7.0_55
Oracle IDE 4.0.3.16.84

its oracle 11g .tnx Michel
Re: oracle transaction [message #661367 is a reply to message #661366] Thu, 16 March 2017 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

11g what? You gave Java with 4 figures, IDE with 5 figures, why not Oracle client and server with same precision?

Re: oracle transaction [message #661368 is a reply to message #661367] Thu, 16 March 2017 04:18 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
oracle = win64_11gR2

below details i got from sql developer Help -> About

Java(TM) Platform 1.7.0_55
Oracle IDE 4.0.3.16.84

http://i693.photobucket.com/albums/vv299/asliyanage/db.png?t=1489569566



this is localhost details

http://i693.photobucket.com/albums/vv299/asliyanage/db2.png

[Updated on: Thu, 16 March 2017 04:20]

Report message to a moderator

Re: oracle transaction [message #661369 is a reply to message #661368] Thu, 16 March 2017 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You get the server version of Oracle using the following statement:
SQL> select banner from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
You get the client version of oracle in the SQL*Plus banner:
C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Jeu. Mars 16 10:22:08 2017

Re: oracle transaction [message #661378 is a reply to message #661369] Thu, 16 March 2017 07:30 Go to previous messageGo to next message
Bill B
Messages: 1802
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 #661395 is a reply to message #661378] Thu, 16 March 2017 11:46 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
select banner from v$version where rownum=1 value is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
Re: oracle transaction [message #661397 is a reply to message #661395] Thu, 16 March 2017 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Bit per bit we have the information.
And what about SQL*Plus?
And what about formatting your post?

Re: oracle transaction [message #661404 is a reply to message #661361] Thu, 16 March 2017 13:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
asliyanage wrote on Wed, 15 March 2017 22:59
but 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.
Re: oracle transaction [message #661405 is a reply to message #661404] Thu, 16 March 2017 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... as well as if the SELECT refers a database link.
SQL> commit;

Commit complete.

SQL> select * from dual@mika;
D
-
X

1 row selected.

SQL> set transaction read only;
set transaction read only
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction

Re: oracle transaction [message #661406 is a reply to message #661405] Thu, 16 March 2017 14:04 Go to previous message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Right, I forgot about that. Any distributed/remote SQL opens transaction.

SY.
Previous Topic: ORACLE sql HELP NEEDED
Next Topic: function based index on date column
Goto Forum:
  


Current Time: Thu Oct 18 17:00:52 CDT 2018