Transaction with SQL

Transaction statements in SQL, or Structured Query Language, are used to manage transactions in a database. They provide a way to group together a set of related operations into a single unit of work, ensuring that either all the operations succeed or none do. This forms the basis for maintaining the Atomicity and Consistency properties of transactions.

Most relational database management systems (RDBMS) implement these SQL transaction statements. Here's a brief description of the key SQL transaction statements:

BEGIN TRANSACTION: This statement indicates the start of a transaction. After this statement, the related operations are grouped together as part of a single transaction.
COMMIT: This statement is used to save all the modifications made in the transaction to the database. Once the COMMIT statement is executed, all changes are permanent and can't be rolled back. It marks the successful end of a transaction.
ROLLBACK: This statement is used to undo all the modifications made in the transaction. If something goes wrong with one of the operations in the transaction, a ROLLBACK command can be issued, and all operations in the transaction are undone, bringing the database back to its state before the BEGIN TRANSACTION statement.
SAVEPOINT: This statement is used to set a savepoint within a transaction, which can be used to roll back part of a transaction, providing a kind of nested transaction functionality. It's a way to create points within a transaction that you can roll back to without rolling back the entire transaction.
SET TRANSACTION: This statement allows you to specify characteristics for the transaction, such as the isolation level. This affects how the transaction is executed in relation to other concurrent transactions.

By using these transaction statements, SQL provides a powerful and flexible way to ensure data integrity and consistency in the face of concurrent access and potential system failures. Because SQL is a standardized language, these transaction statements are implemented in a similar way across different RDBMS, making the skills and concepts portable across different database systems.

Example SQL

Simple Transaction: The following example shows a simple transaction in which two UPDATE operations are enclosed in a transaction:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

In this example, $100 is transferred from one account to another. The two UPDATE operations are part of a single transaction. If any of the operations fail (e.g., if the first account does not have sufficient balance), the changes can be rolled back using ROLLBACK; instead of COMMIT;. If all operations are successful, COMMIT; is called to make the changes permanent.

Using SAVEPOINT: A SAVEPOINT allows you to define a point within your transaction which you can roll back to, without affecting any work done in the transaction before the savepoint. Here's an example:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
SAVEPOINT sp1;

UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
SAVEPOINT sp2;

-- In case of an error, you can roll back to a specific savepoint.
ROLLBACK TO SAVEPOINT sp1;

COMMIT;

In this example, if something goes wrong after the second UPDATE, you can roll back to sp1, undoing the second UPDATE but not the first.

Setting a Transaction Isolation Level: The SET TRANSACTION statement can be used to set the isolation level for a transaction, like so:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;

-- Transaction operations go here

COMMIT;

In this example, the transaction isolation level is set to READ COMMITTED, which means that a transaction will only see data that has been committed at the start of the transaction.