Help on locking techniques
Hi everyone,
I'm writing to ask a suggestion about a problem that the implicit commit feature of lock and unlock statement is giving me.
I'm developing in C# and I made some code that do something that I try to explain here:
OPEN TRANSACTION
SELECT * FROM TABLE1;
foreach record in table1
{
LOCK TABLE TABLE2
oldvalue = result of SELECT FIELD1 FROM TABLE2 WHERE KEY=XXX
... some C# operation on field1 calculating newvalue
UPDATE TABLE2 SET FIELD1=newvalue WHERE KEY=XXX
UNLOCK TABLE
UPDATE TABLE3 SET FIELD1=oldvalue WHERE ID=someid
... many C# operations, SQL select and updates on several other tables
}
COMMIT or ROLLBACK
all of those operations takes several minutes to be done.
My problem is that LOCK and UNLOCK causes the commit of the transaction so, in case of errors, rollback statement doesn't restore the situation before transaction opening and the value of FIELD1 in TABLE2 is not restored to its first value.
But I need to lock the table cause I don't want that two concurrent connections can read the same value from TABLE2 generating duplicated values on TABLE3.FIELD1
What is the correct and suggested way of managing locking or semaphores in such a situation?
Subject
Views
Written By
Posted
Help on locking techniques
2755
December 29, 2008 09:37AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.