OraFAQ Forum: SQL & PL/SQL » oracle transaction

Home » SQL & PL/SQL » SQL & PL/SQL » oracle transaction

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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 #661360 is a reply to message #661359]

Wed, 15 March 2017 21:37

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: oracle transaction [message #661361 is a reply to message #661360]

Wed, 15 March 2017 21:59

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

Report message to a moderator

Re: oracle transaction [message #661362 is a reply to message #661361]

Wed, 15 March 2017 22:45

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: oracle transaction [message #661364 is a reply to message #661359]

Thu, 16 March 2017 01:33

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: oracle transaction [message #661366 is a reply to message #661364]

Thu, 16 March 2017 03:24

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

Report message to a moderator

Re: oracle transaction [message #661367 is a reply to message #661366]

Thu, 16 March 2017 04:03

Michel Cadot
Messages: 68641
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?

Report message to a moderator

Re: oracle transaction [message #661368 is a reply to message #661367]

Thu, 16 March 2017 04:18

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

Michel Cadot
Messages: 68641
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

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.

Report message to a moderator

Re: oracle transaction [message #661395 is a reply to message #661378]

Thu, 16 March 2017 11:46

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

Report message to a moderator

Re: oracle transaction [message #661397 is a reply to message #661395]

Thu, 16 March 2017 12:00

Michel Cadot
Messages: 68641
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?

Report message to a moderator

Re: oracle transaction [message #661404 is a reply to message #661361]

Thu, 16 March 2017 13:52

Solomon Yakobson
Messages: 3273
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.

Report message to a moderator

Re: oracle transaction [message #661405 is a reply to message #661404]

Thu, 16 March 2017 13:59

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: oracle transaction [message #661406 is a reply to message #661405]

Thu, 16 March 2017 14:04

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Right, I forgot about that. Any distributed/remote SQL opens transaction.

SY.

Report message to a moderator

Previous Topic:	ORACLE sql HELP NEEDED
Next Topic:	function based index on date column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 15:56:15 CDT 2024