ansaurus

Question

MySQL: automatic rollback on transaction failure

Answer 1

+1 A:

Sorry, You are going to need to do this on your own. I am not a PHP person, but in SQL: If you create a transaction and do several MySQL operations within the transaction, if you rollback the transaction everything will be rolled back. NOTE: You need to be using a transactional storage engine and autocommit must be set to off.

If all you are concerned about is the transaction coordination traffic then you could create a stored procedure and simply call it.

The reason the database does not automatically commit or rollback is because it does not know what you are trying to do, committing some data and rolling back other data might be acceptable in an app.

Romain Hippeau 2010-05-10 21:33:05

Did I form my question so bad? I know how transactions work, I was also asking about mysql solution only, so it's good you are nonPHP person. Please tell me in which part should i make that question more clear.

praksant 2010-05-10 21:38:40

@praksant If I did not answer the question with what I wrote, then what are you asking for ?

Romain Hippeau 2010-05-10 21:48:53

I asked if there is a way to rollback AUTOMATICALLY, because it's not the way it works by default. I explained in my question how does it behave by default. I want to eliminate checking in PHP for failure, as it would mean 1-2 calls for every transaction. If it rolled back automatically i would be able to do many transactions in one call what would be a performance boost.

praksant 2010-05-11 06:51:51

Thank you for your Update. I need simple logic - if (any error occurred) rollback, else commit. It's very easy in PHP or other language to check after each transaction for error, and commit/rollback accordingly. I just wanted to boost performance. if mysql is on remote machine, every call is very expensive. Stored procedures may do it, but i want greater flexibility in the transactions and maintenance/testing of stored procedures isn't very easy.

praksant 2010-05-26 21:01:52

Answer 2

A:

I don't know of such feature, but I also don't see how checking for failure would mean more calls:

try:
     <my code>
except:
     transaction.rollback()
     raise
else:
     transaction.commit()

-- it's in Python/Django, but it should directly transpose to PHP - and it takes exactly the same amount of code to start new transaction, no matter if there is a problem (exception) or not.

Tomasz Zielinski 2010-05-11 06:25:14

First call is the transaction, second is rollback, and multiply this by number of transactions. I would like to send one file with more transactions, and I want mysql to execute all transactions and rollback any failed ones.

praksant 2010-05-11 06:45:15

ansaurus

tags:

views:

answers:

MySQL: automatic rollback on transaction failure

related questions