Home » SQL & PL/SQL » SQL & PL/SQL » Locking a table before doing an Insert.....Pls help (Oracle 10g)
Locking a table before doing an Insert.....Pls help [message #328826] Mon, 23 June 2008 00:11 Go to next message
gangulyz
Messages: 6
Registered: April 2008
Junior Member
Hi
Here's my problem ,

We have 4 processes running at the same time that inserts data into a common table. Currently we are facing concurrency problem regarding the data insert as duplicate records are being inserted.

We want to implement a locking mechanism such that only one process can insert the data into the table any given time.

Should I go for an explicit locking ? or Is there a better way to achieve the goal through PL/SQL?

any help is greatly appreciated.

Regards
Som
Re: Locking a table before doing an Insert.....Pls help [message #328829 is a reply to message #328826] Mon, 23 June 2008 00:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above
Re: Locking a table before doing an Insert.....Pls help [message #328832 is a reply to message #328826] Mon, 23 June 2008 00:31 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member

useful link regarding locking methods

http://download-east.oracle.com/docs/cd/B10500_01/appdev.920/a96624/06_ora.htm


regards,
Re: Locking a table before doing an Insert.....Pls help [message #328837 is a reply to message #328826] Mon, 23 June 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you use a unique key constraint you can't insert duplicates.

Regards
Michel
Re: Locking a table before doing an Insert.....Pls help [message #328998 is a reply to message #328826] Mon, 23 June 2008 12:34 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, unique constraint would be my first choice if stopping duplicates is the problem. Now we just have to wait to find out what the "special circumstances" are that explain why there was no unique key in the first place (hehe).

Kevin
Re: Locking a table before doing an Insert.....Pls help [message #329003 is a reply to message #328826] Mon, 23 June 2008 12:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
gangulyz wrote on Sun, 22 June 2008 22:11
Hi
Here's my problem ,

We have 4 processes running at the same time that inserts data into a common table. Currently we are facing concurrency problem regarding the data insert as duplicate records are being inserted.

We want to implement a locking mechanism such that only one process can insert the data into the table any given time.

Should I go for an explicit locking ? or Is there a better way to achieve the goal through PL/SQL?

any help is greatly appreciated.

Regards
Som



>Locking a table before doing an Insert
Doing the above is a GREAT way to have a non-scalable application!

This problem results directly from a very flawed design.
Rather than attack the symptom, you should change the design to avoid this problem entirely.

[Updated on: Mon, 23 June 2008 16:09] by Moderator

Report message to a moderator

Re: Locking a table before doing an Insert.....Pls help [message #329074 is a reply to message #329003] Tue, 24 June 2008 00:19 Go to previous message
gangulyz
Messages: 6
Registered: April 2008
Junior Member
Thanks a lot Guys , We've to use the unique key to eliminate the duplicates.
I guess it was a flawed design in the first place.

Thanks again.

-
Som
Previous Topic: insert next 5 records
Next Topic: paramertised cursor
Goto Forum:
  


Current Time: Tue Feb 11 17:02:05 CST 2025