Transaction Rollback logic ?

[veledrom]

Hi,

I have a cookie with 5 products. There will be a loop to read one by one and reduce from stock in database for each product. Now, If something goes wrong half way through i want to cancel whole transaction. For example; everything went ok for first 3 products and reduced the stocks for those products in database. If something goes wrong for product 4 then cancel transactions for first 3 products as well. Is this possible?

I am trying to understand the logic behind transaction is made in a loop.

Thanks

[dickersonka]

wrap your code with something like this

SET AUTOCOMMIT=0;
START TRANSACTION;

-- do your work here

-- if failed do this
ROLLBACK;
-- if successful do this
COMMIT;

[veledrom]

Hi dickersonka,

How would you apply your solution to code below? I couldn't do it.

Thanks again

BEGIN
DECLARE add INT;
INSERT INTO Table1 (col1, col2)	VALUES (1,2);
SELECT col1 INTO add FROM Table1 WHERE col1=1;
INSERT INTO Table2 (col3, col4)	VALUES (add, add);
END

[dickersonka]

what part are you having trouble with?

are the inserts or selects failing?

[veledrom]

For example,
First INSERT and SELECT run successfuly.
Second INSERT doesn't run.
In this case first INSERT must be canceled too. This is in a stored procedure.
Thanks

I am just trying to understand how rollback and commit is used in multi query transactions.

[dickersonka]

you need to query the values that are incorrect
i don't know if you're variable is incorrect, or the insert is blowing up
you then check to make sure you have the required values, ande decide to commit or rollback based upon them

SET AUTOCOMMIT=0;
START TRANSACTION;
BEGIN
DECLARE addVar INT;
INSERT INTO Table1 (col1, col2)	VALUES (1,2);
SELECT col1 INTO add FROM Table1 WHERE col1=1;

IF (addVar = 0) THEN ROLLBACK;
ELSE
INSERT INTO Table2 (col3, col4)	VALUES (addVar , addVar );
COMMIT;
ENDIF;
END

here's a sample
http://db4free.blogspot.com/2005/09/atm-transfer-simulation.html

[veledrom]

Thanks for information