Home » SQL & PL/SQL » SQL & PL/SQL » Performant Locking of Multiple Rows with FOR UPDATE (10g2)
Performant Locking of Multiple Rows with FOR UPDATE [message #323167] Tue, 27 May 2008 09:12 Go to next message
Mike Muldoon
Messages: 24
Registered: March 2003
Location: Edinburgh
Junior Member
Hi

Quick question... I may be being dumb here... Anyway, I have a process that goes off to update column values on tables that are also being updated by other processes.

I therefore wish my process to (upfront) lock the 'parent' record of the 'child' rows I eventually update, then release the parent record once it has finished. However, there may be more than one 'parent' record, I need to lock them all, initially I thought this is easily done by the SQL below..

SELECT max(account_num)
INTO accNumSQL
FROM accountControl
WHERE account_num = vAccountNum
FOR UPDATE;

However, is this use of some grouping function really the best way? I don't really care what is returned into the variable accNumSQL, I never use it.. I just want to lock the one or more records.

Thanks in advance.
Re: Performant Locking of Multiple Rows with FOR UPDATE [message #323168 is a reply to message #323167] Tue, 27 May 2008 09:21 Go to previous messageGo to next message
Mike Muldoon
Messages: 24
Registered: March 2003
Location: Edinburgh
Junior Member

Actually,

I've just realised that the SQL...

SELECT max(account_num)
INTO accNumSQL
FROM accountControl
WHERE account_num = vAccountNum
FOR UPDATE;

won't work since the MAX(***) is incompatible with the FOR UPDATE clause. So, what should my strategy be here?
Re: Performant Locking of Multiple Rows with FOR UPDATE [message #323183 is a reply to message #323167] Tue, 27 May 2008 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT max(account_num)
INTO accNumSQL
FROM accountControl
WHERE account_num = vAccountNum
FOR UPDATE;

(Note the use of code tags to format)
Is the same as
SELECT account_num
INTO accNumSQL
FROM accountControl
WHERE account_num = vAccountNum
FOR UPDATE;

So maybe your query does not mean what you want it means.
Precise the definition of "parent" you want to lock.

Regards
Michel
Re: Performant Locking of Multiple Rows with FOR UPDATE [message #323187 is a reply to message #323183] Tue, 27 May 2008 11:04 Go to previous messageGo to next message
Mike Muldoon
Messages: 24
Registered: March 2003
Location: Edinburgh
Junior Member
Hi Michael

Ok, to try to explain better... the ACCOUNTCONTROL table is an associated table of the ACCOUNTBALANCE table. My process is adjusting values in the ACCOUNTBALANCE table, but I need to lock the ACCOUNTCONTROL table so that I fall in line with the locking mechanisms of other processes that also update ACCOUNTBALANCE ( they lock this table first as obviously we need to ensure that only one process is updating this table at any one time and each is basing its calculations on the 'latest' data).

The problem is that there may be more than 1 ACCOUNTCONTROL record, I need to lock all records for each vAccountNum. This means the below will not work (as it is expecting a single value back).

SELECT account_num
INTO accNumSQL
FROM accountControl
WHERE account_num = vAccountNum
FOR UPDATE;


Using the below in order to try to lock multiple records but only return a single value will also not work as this is an invalid PL/SQL statement:

SELECT max(account_num)
INTO accNumSQL
FROM accountControl
WHERE account_num = vAccountNum
FOR UPDATE;


I don't really care what value I bring back from ACCOUNTCONTROL, I don't use it.. I just need to lock ANY / ALL records found. I think I may have to put the above SELECT into an explicit cursor declaration and loop around to lock each row found.
Re: Performant Locking of Multiple Rows with FOR UPDATE [message #323191 is a reply to message #323187] Tue, 27 May 2008 11:32 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just use:
cursor c is 
select account_num
FROM accountControl
WHERE account_num = vAccountNum
FOR UPDATE;
...
open c;
close c;

Then you have locked the rows until commit or rollback.

Regards
Michel

[Updated on: Tue, 27 May 2008 11:32]

Report message to a moderator

Previous Topic: how to calculate sales for a week
Next Topic: Number of columns in a table
Goto Forum:
  


Current Time: Sun Dec 11 08:15:25 CST 2016

Total time taken to generate the page: 0.07712 seconds