Home » SQL & PL/SQL » SQL & PL/SQL » How to display message using default lock in ORACLE 9i
How to display message using default lock in ORACLE 9i [message #259160] Tue, 14 August 2007 09:45 Go to next message
sivasenthil
Messages: 4
Registered: August 2007
Junior Member
I am using ORACLE 9i I am inserting a same record in a same table (which is having primary key) of same user with different sql editor (or from different machines).
with same time.
One person is able to insert the record..

But other one is in processing state, once the inserted person give commit,then only other person got the message "Primary key constraint voilated".

This is bcoz of concurrency in oracle. For this purpose Oracle uses default lock.

Is it possible to give message to other person like "One user is using this table..", after a record is inserted and before commit is get issued by the inserted user?

how can i do this?
Re: How to display message using default lock in ORACLE 9i [message #259165 is a reply to message #259160] Tue, 14 August 2007 09:52 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Issue the command:

select * from table
where column = 'value'
for update of column nowait;

It should then fail if another user has already locked the row(s).

[Updated on: Tue, 14 August 2007 09:52]

Report message to a moderator

Re: How to display message using default lock in ORACLE 9i [message #259167 is a reply to message #259160] Tue, 14 August 2007 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a "select ... for update nowait" before your update.
Then if someone is already holding the row you will get an error.
SQL> select * from t where id=0 for update nowait;
select * from t where id=0 for update nowait
              *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Of course this only works for update not for insert. You can't do anything on insert.

Regards
Michel
Re: How to display message using default lock in ORACLE 9i [message #259175 is a reply to message #259167] Tue, 14 August 2007 10:21 Go to previous messageGo to next message
sivasenthil
Messages: 4
Registered: August 2007
Junior Member
Thanks for your reply..

Is there is any way to check the locked objects from system tables and display the message?

Re: How to display message using default lock in ORACLE 9i [message #259183 is a reply to message #259175] Tue, 14 August 2007 10:42 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
select upper(c.object_name), a.username us, a.osuser os, a.sid, a.serial# 
  from v$session a, v$locked_object b, dba_objects c 
 where /*upper(c.object_name) = upper('&tbl_name') 
   and */ b.object_id = c.object_id
   and a.sid = b.session_id;
By
Vamsi
Re: How to display message using default lock in ORACLE 9i [message #259187 is a reply to message #259183] Tue, 14 August 2007 10:50 Go to previous messageGo to next message
sivasenthil
Messages: 4
Registered: August 2007
Junior Member
Hi Vamsi thanks for ur query..

Using this query i cannot avoid the deadlock.
Is it any procedure or query to display message to user while concurrent insertion of record?
Re: How to display message using default lock in ORACLE 9i [message #259188 is a reply to message #259187] Tue, 14 August 2007 10:55 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

For better understanding read the topic

Locks How to

I think you will understand after reading this.
By checking Lock you can show the message to another user.

Cheers
Sanka

[Updated on: Tue, 14 August 2007 10:56]

Report message to a moderator

Re: How to display message using default lock in ORACLE 9i [message #259190 is a reply to message #259187] Tue, 14 August 2007 10:57 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
On top of that you can put a filter on your table_name and if you get no rows, then you can UNDERSTAND that the table hasn't been locked by anybody. Right?

Edit: I prefer selecting count(*) instead of raising NO_DATA_FOUND.
By
Vamsi

[Updated on: Tue, 14 August 2007 10:58]

Report message to a moderator

Re: How to display message using default lock in ORACLE 9i [message #259195 is a reply to message #259190] Tue, 14 August 2007 11:14 Go to previous messageGo to next message
sivasenthil
Messages: 4
Registered: August 2007
Junior Member
Hi Vamsi

Can you send the sample procedure?
Re: How to display message using default lock in ORACLE 9i [message #259203 is a reply to message #259175] Tue, 14 August 2007 11:34 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there is any way to check the locked objects from system tables and display the message?

Table locks, there is (see previous post).
Row locks there is not, unless someone is waiting on a row then you can see on which row.

There are several scripts on PSOUG link posted previously.

Regards
Michel
Previous Topic: Substr Query Problem
Next Topic: Performance issues
Goto Forum:
  


Current Time: Wed Dec 07 02:59:38 CST 2016

Total time taken to generate the page: 0.21139 seconds