MySQL Transactions

MySQL Transactions

What are transactions?

# Sequential group of DML statements, which is performed as if it were one single work unit.


# Will never complete unless each individual operation within the group is sucessful.If any operation within the transaction fails, the entire transaction will fail.

# Begins with the first executable SQL statement.

# Ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement
is issued.

Sample Scenario

Suppose a bank customer transfers money from his savings account (SB a/c) to his current account (CA a/c), the statement will be divided into four blocks:

# Debit SB a/c
# Credit CA a/c
# Record in Transaction Journal.
# End Transaction



The SQL statement to debit SB a/c is as follows:

UPDATE sb_accounts SET balance = balance - 500 WHERE account_no = 932656;



The SQL statement to credit CA a/c is as follows:

UPDATE ca_accounts SET balance = balance + 500 WHERE account_no = 933456;



The SQL statement for recording in the transaction journal is as follows:

INSERT INTO journal VALUES (100896, 'Transaction on Kumar Atul Jaiswal a/c', '28-NOV-11' 932656, 933456, 500);


The SQL statement for End Transaction is as follows:

COMMIT WORK;

 

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.

Disclaimer

All tutorials are for informational and educational purposes only and have been made using our own routers, servers, websites and other vulnerable free resources. we do not contain any illegal activity. We believe that ethical hacking, information security and cyber security should be familiar subjects to anyone using digital information and computers. Hacking Truth is against misuse of the information and we strongly suggest against it. Please regard the word hacking as ethical hacking or penetration testing every time this word is used. We do not promote, encourage, support or excite any illegal activity or hacking.