Home » SQL & PL/SQL » SQL & PL/SQL » locking (oracle 9i)
locking [message #385766] Tue, 10 February 2009 23:49 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
A table contains data like this
eno ename
1 ramesh
2 rajesh
3 ram
client A
SQL> Lock tble emp in exclusive mode;
client A performs
update emp set ename='kiran' where ename='ramesh';

client A didn't commit
select * from emp;
in that case client A gets the data like this

eno ename
1 kiran
2 rajesh
3 ram

client B performs
select * from emp;
In that case client B gets the data like this
eno ename
1 ramesh
2 rajesh
3 ram

In that case client B didn't see the changes made by client A, there is no data consistancy but what is the advantage of locking;
Re: locking [message #385770 is a reply to message #385766] Wed, 11 February 2009 00:06 Go to previous messageGo to next message
srinivasreddy777
Messages: 11
Registered: October 2007
Location: Hyderabad
Junior Member

Before posting you would have read about all the Oracle Database Transaction properties (ACID). An Oracle Database transaction is not only a consistent one, But also an Atomic.

Read more about the Dirty read and Phantom reads.

The changes are also seen by modifying the Isolation Level to READ UNCOMMITTED. Unfortunately oracle doesn't support this for you.

Regards
Srinivas
Re: locking [message #385777 is a reply to message #385766] Wed, 11 February 2009 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You should also read Database Concepts for top to bottom, 95%+ of your questions are answered.

In Oracle:
- readers do not block writers
- writers do not block readers

@srinivasreddy777, "Unfortunately oracle doesn't support this for you", why "unfortunately", HOPEFULLY! What is the meaning of a rdbms that allows you to see something that NEVER existed.

Regards
Michel
Re: locking [message #385797 is a reply to message #385766] Wed, 11 February 2009 01:34 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
ramesh55.sse wrote on Wed, 11 February 2009 06:49
In that case client B didn't see the changes made by client A, there is no data consistancy but what is the advantage of locking;

User B is unable to make any changes in EMP until user A commits its changes.
What if user A would make a change after user B reads it, but before user B would plan to make any changes on this (which is not a good approach in multiuser environment - it shall be solved differently)?

From another point of view, this is a prove of read consistency. Just imagine that user A would want to issue this:
update emp set ename='ram' where eno=1;
update emp set ename='ramesh' where eno=3;

If user B would query between those UPDATEs in the way you think, he would get this:
eno ename
1 ram
2 rajesh
3 ram
Would you call it consistent result? Me not.

Anyway, what problem are you really trying to solve?
Previous Topic: Query to get start and end of completed process
Next Topic: "wrap utility" & "function" topics merged
Goto Forum:
  


Current Time: Sun Feb 09 09:53:03 CST 2025