Transaction

Transaction

A common online definition of a database transaction reads “A transaction symbolizes a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions.” In simpler terms, think about taking a two-legged flight from Mumbai to New York. The first airline takes you from Mumbai to Dubai and the second takes you from Dubai to New York. The two flight legs represent portions of a transaction. You must travel both legs to reach your destination, but if immigration forbids you from taking the second leg of the flight you’d want to return back to Mumbai and not remain stranded at Dubai. A database transaction is similar. You either want all the actions within the transaction to complete or none at all. You don’t want a transaction to complete halfway through and then abort.

Need for Transactions

One may wonder why we need transactions. Well, whenever multiple users are interacting with a database it is possible for the actions of one user to interleave with another user and bring the data in an inconsistent state. The classic example is transferring funds from one bank account to another. Say the bank application wants to add 100 dollars to your bank account with a balance of 500 dollars. The application reads your balance, adds 100 dollars to it and then updates the new amount 600 dollars in the database against your username. It could happen that between the time the application reads and updates the new amount, you make an ATM withdrawal of 200 dollars. The application has already read your balance and doesn’t know that 200 dollars has been withdrawn and mistakenly writes your balance as 600 dollars to the database.

The application layer could avoid this mistake if it performed the two tasks of reading and then updating your balance atomically, or in other words as a transaction. Transactions allow you to batch together SQL statements as an indivisible set that either succeeds or has no effect on the database.

Syntax to Start & Commit a Transaction

START TRANSACTION;

** – SQL statements

COMMIT;

Syntax to Start & Rollback a Transaction

START TRANSACTION;

** – SQL statements

ROLLBACK;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/39lesson.sh and wait for the MySQL prompt to start-up.