Start Transaction

A transaction is a sequential group of SQL statements such as select,insert,update or delete, which is performed as one single work unit.

In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

Bank transaction will be best example for explaining this. Consider a transfer between two accounts. To achieve this you have to write SQL statements that do the following

1. Check the availability of requested amount in the first account
2. Deduct requested amount from first account
3. Deposit it in second account

If anyone these process fails, the whole should be reverted to their previous state.

ACID : Properties of Transactions

Transactions have the following four standard properties

• Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
• Consistency: ensures that the database properly changes states upon a successfully committed transaction.
• Isolation: enables transactions to operate independently of and transparent to each other.
• Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

Transactions begin with the statement START TRANSACTION or BEGIN WORK and end with either a COMMIT or a ROLLBACK statement. The SQL commands between the beginning and ending statements form the bulk of the transaction.

START TRANSACTION;
SET @transAmt = '500';
SELECT @availableAmt:=ledgerAmt FROM accTable WHERE customerId=1 FOR UPDATE;
UPDATE accTable SET ledgerAmt=ledgerAmt-@transAmt WHERE customerId=1;
UPDATE accTable SET ledgerAmt=ledgerAmt+@transAmt WHERE customerId=2;
COMMIT;

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

The FOR UPDATE indicates (and locks) the row(s) for the duration of the transaction.

While the transaction remains uncommitted, this transaction will not be available for others users.

General Procedures involved in Transaction

• Begin transaction by issuing SQL command BEGIN WORK or START TRANSACTION.
• Run all your SQL statements.
• Check whether everything is executed according to your requirement.
• If yes, then issue COMMIT command, otherwise issue a ROLLBACK command to revert everything to the previous state.
• Check for errors even after COMMIT if you are using, or might eventually use, Galera/PXC.