Transaction Processing

mySQL supports transaction processing but only for some table types. When you want to use transaction processing the best table type to use currently is "innodb".

With transaction processing you can run multiple updates of the database (including inserts and deletes) and if any of them fail then none of the other updates will be retained. Any updates successfully applied before the one that failed will be reversed out.

The first thing that you need when using transaction processing is to tell mySQL that you are starting a new transaction. There are two alternate commands you can use to do this:

BEGIN;
START TRANSACTION;

Note that there is another use for BEGIN in stored procedures where it has nothing to do with transaction processing.

Once your transaction has started you can then use whatever calls you need to update whichever tables need to be updated as a part of the transaction.

Once all of the updates have been run you then need to complete the transaction and permanently apply all of the updates in the transaction. There are two alternate ways to do this depending on whether you want to start another transaction immediately or not:

COMMIT;
COMMIT AND CHAIN;

Using the second of these completes the current transaction and starts another without needing either of the earlier mentioned commands for starting a transaction.

Should any of the updates in your transaction fail then you can undo all of the prior updates in the transaction in a single call (and if desired you can also start a new transaction):

ROLLBACK;
ROLLBACK AND CHAIN;

With more complex transactions you can specify SAVEPOINTs within the transaction so that the entire transaction need not be undone if one of the later updates fails. Where the failure is due to something that is intermittent (such as a row being locked by another transaction) you can ROLLBACK to a specified SAVEPOINT and rerun from there instead.

 

This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow
Donate