BEGIN TRANSACTION, COMMIT, ROLLBACK

Transactions in SQL signify a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. In this chapter, we delve into the concepts of BEGIN TRANSACTION, COMMIT, and ROLLBACK which are instrumental in maintaining data integrity while controlling transactions in SQL.

Begin Transaction

The BEGIN TRANSACTION in SQL signifies the start of a transaction. Once this statement is executed, all the tasks or statements in the SQL operation become a part of a single transaction. This ensures that either all statements are executed successfully, or none of them are. It brings atomicity to transactions.

Here is a basic example:

BEGIN TRANSACTION;

UPDATE Account SET balance = balance - 100 WHERE name = 'Jim';
UPDATE Account SET balance = balance + 100 WHERE name = 'John';

COMMIT;

In the above SQL code snippet, the two update operations are part of the same transaction. If one fails, then both will be aborted to preserve data consistency.

Commit

The COMMIT command is used to save all the transactions to the database since the last COMMIT or ROLLBACK command. The changes made by the transaction become permanent in the database and visible to other sessions executing transactions.

Here is a simple example:

BEGIN TRANSACTION;
UPDATE Account SET balance = balance - 100 WHERE name = 'Jim';
UPDATE Account SET balance = balance + 100 WHERE name = 'John';

COMMIT;

In the above example, the COMMIT command ensures that the changes made by the two UPDATE commands are saved and made permanent.

Rollback

The ROLLBACK command is used to undo transactions that have not already been saved to the database. If something goes wrong with one of the operations in a transaction, you can issue a rollback to revert all changes made in the transaction up to the point of the error.

Here is an example to illustrate this:

BEGIN TRANSACTION;

UPDATE Account SET balance = balance - 100 WHERE name = 'Jim';
UPDATE Account SET balance = balance + 100 WHERE name = 'John';

IF @@ERROR != 0 
   ROLLBACK;

In the above example, if either UPDATE statement fails, the transaction is rolled back and the changes are not made in the database.

Thus, the BEGIN TRANSACTION, COMMIT, and ROLLBACK commands in SQL are very useful for controlling the transactions and ensuring the integrity of the data in a database. Understanding these commands is essential for anyone who is dealing with data manipulation in SQL. They add an extra layer of security to data management and can save from unexpected data corruption. In the next chapter, we will continue the discussion on more advanced SQL commands.