Home » SQL & PL/SQL » SQL & PL/SQL » How to avoid parallel updates on a table in a procedure.
How to avoid parallel updates on a table in a procedure. [message #272296] Thu, 04 October 2007 08:24 Go to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member

Hi experts,

I have a procedure which have some update and insert stements in it.
one of the update statement is below.

UPDATE SEQUENCE_ID
SET ID_VALUE = (SELECT MAX(COMPANY_TRANSACTION_ID) + 1 FROM COMPANY_TRANSACTION)
WHERE SEQUENCE_NAME = 'COMPANY_TRANSACTION.COMPANY_TRANSACTION_ID';

My concern is no parallel process(UI operations) should update this table while the above procedure is being executed..

The procedure has commit statement at the bottom.

Please suggest the appropriate modifications in my procedure to avoid parallel updates on the SEQUENCE_ID table

Thanks in advance.





Re: How to avoid parallel updates on a table in a procedure. [message #272298 is a reply to message #272296] Thu, 04 October 2007 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Lock the table. This is the only way.
And if you used Oracle sequence instead of the b... of sequence table, you won't have this problem.

Regards
Michel
Re: How to avoid parallel updates on a table in a procedure. [message #272303 is a reply to message #272296] Thu, 04 October 2007 08:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why are you only concerned about parallel updates?
Re: How to avoid parallel updates on a table in a procedure. [message #272307 is a reply to message #272296] Thu, 04 October 2007 08:46 Go to previous messageGo to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
Thanks for your quick reply,
can i lock the table using BEGIN TRANSACTION or need to use explicit LOCK TABLE statement..

Please suggest...

Thanks
Re: How to avoid parallel updates on a table in a procedure. [message #272442 is a reply to message #272307] Fri, 05 October 2007 01:28 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Mmmm..."BEGIN TRANSACTION" smells like T-SQL. Are you sure this is an Oracle question? If so, have a look at sequences like Michel suggested. What you are trying to do is far from ideal. JRowbottom hinted already at it: there's more to it than parallel updates. But perhaps you meant to say that you're trying to serialize it. If you update the record without commit, it will be locked automatically. You won't need an explicit lock. It isn't released until the transaction is ended in either a rollback or a commit.

But again:
* is this Oracle?
* if so: won't a sequence be more appropriate?

MHE
Previous Topic: Help with max and min query
Next Topic: column as a collection
Goto Forum:
  


Current Time: Thu Dec 08 20:08:52 CST 2016

Total time taken to generate the page: 0.11803 seconds