Member Avatar for 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

  • 2 Contributors
  • 6 Replies
  • 140 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by veledrom

Recommended Answers

All 6 Replies

Member Avatar for 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;
Member Avatar for 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
Member Avatar for dickersonka

what part are you having trouble with?

are the inserts or selects failing?

Member Avatar for 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.

Member Avatar for 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

Member Avatar for veledrom

Thanks for information

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.