Q.What is a transaction in a database? Explain the terms COMMIT and ROLLBACK with an example of when each is used.

A transaction groups several changes so they behave as one; this question asks what it is and how COMMIT and ROLLBACK end it.

A transaction is a logical unit of work made up of one or more SQL statements that is treated as a single, indivisible operation — either all of its statements take effect or none of them does. The standard example is a bank transfer: money must be subtracted from one account and added to another, and both must happen or neither should.

A transaction is normally begun with START TRANSACTION; and ends in one of two ways:

• COMMIT makes all the changes of the transaction permanent. It is used once we are sure the changes are correct; after a commit they are saved and cannot be undone by a rollback. For example, after successfully raising every Sales employee's salary and checking the result, we run COMMIT;.

• ROLLBACK undoes all the changes made since the transaction began, returning the database to its earlier state. It is used when something goes wrong or a mistake is noticed. For example, if a wrong DELETE removed the Sales employees, running ROLLBACK; before committing restores them.

START TRANSACTION;
UPDATE Employee SET Salary = Salary + 2000 WHERE Dept = 'Sales';
COMMIT;   -- change kept permanently

START TRANSACTION;
DELETE FROM Employee WHERE Dept = 'Sales';
ROLLBACK; -- change undone, rows restored

Do not say COMMIT and ROLLBACK are the same kind of 'save' — COMMIT saves, ROLLBACK cancels. Also remember that once a transaction is committed you can no longer roll it back; rollback only works on uncommitted changes.