MOCKSTACKS
EN
Questions And Answers

More Tutorials









MYSQL Transaction

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.


Conclusion

In this page (written and validated by ) you learned about MYSQL Transaction . What's Next? If you are interested in completing MYSQL tutorial, your next topic will be learning about: MYSQL Log files.



Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.


Share On:


Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.