Locking

All posts relating to Oracle database administration.

Moderator: Tim...

Locking

Postby Guest » Mon Nov 01, 2004 6:09 pm

Suppose there is a table named "A" with only one column "ENO". ENO is a primary Key.
Ex:
create table a(eno number(10) primary key);

##########################
From Session A,
--------------------------
insert into a values(10);
No commit has been given.
##########################

From Session B. the same value is inserted by an other person.

############################
From Session B,
--------------------------
insert into a values(10);
############################

Now Session B Hangs. Since itz a lock.
My question is how oracle knows that the value "10" is inserted by Session A.

Thanks
Prakash
Guest
 

RE:Locking

Postby Tim... » Mon Nov 01, 2004 6:09 pm

Of course it knows you've done the insert, it's an RDBMS. Your change is logged in an undo segment and the change has been applied to the block in the cache etc.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

RE:Locking

Postby Guest » Mon Nov 01, 2004 6:09 pm

Hi Tim,

Thanks for the answer.
But i have read that for an Insert only the rowid is logged in the Rollback segment.
I need to know how internally oracle manages to get the value.
correct me if iam wrong.

thanks once again
Prakash
Guest
 

RE:Locking

Postby Tim... » Mon Nov 01, 2004 6:09 pm

Hi.

The primary key is a unique index. Once on transaction adds a row another cannot add the same value. The changes are done instantly to the block, but rolled back in the event of a rollback request. As such session A and B both try to amend the block, but session B can't.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 7 guests

cron