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
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
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;
START TRANSACTION, autocommit remains disabled until you end the transaction with
ROLLBACK. The autocommit mode then reverts to its previous state.
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
COMMITcommand, otherwise issue a
ROLLBACKcommand to revert everything to the previous state.
COMMITif you are using, or might eventually use, Galera/PXC.