MySQL Forums
Forum List  »  InnoDB

Help on locking techniques
Posted by: Stefano Sapienti
Date: December 29, 2008 09:37AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Help on locking techniques
2753
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.