SQL | TRANSACTIONS

Transactions are essential components in the world of relational databases, ensuring that the operations performed on the data are consistent, isolated, and durable. They are a series of one or more SQL operations considered as a single logical unit of work.

When using transactions, you're essentially saying that if all the operations within the transaction succeed, then the changes should be saved (committed). If any operation within the transaction fails, all the operations within the transaction should be undone (rolled back).

Key Concepts:

ACID properties - Transactions maintain the ACID properties:
- Atomicity: Ensures that all operations within the transaction are completed successfully; otherwise, the transaction is aborted.
- Consistency: Ensures that the database properly transitions from one consistent state to another.
- Isolation: Ensures that concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially.
- Durability: Ensures that once a transaction has been committed, it will remain committed even in the case of a system failure.
Commit: This saves all the transactions.
Rollback: This undoes the transactions.

SQL Transaction Commands:

BEGIN TRANSACTION: This starts a new transaction.
COMMIT: If everything is in order, this will finalize all changes.
ROLLBACK: If something goes wrong, this will undo all changes made since the last BEGIN TRANSACTION.
SAVEPOINT: Sets a point within the transaction to which you can later roll back.
SET TRANSACTION: Configures the properties of a transaction.

Examples:

Starting a transaction, making changes, and committing those changes:

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

Starting a transaction, making changes, and rolling back (undoing) those changes:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
ROLLBACK;

Using SAVEPOINT:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
ROLLBACK TO sp1;
COMMIT;

In the above example, the second update is rolled back, but the first update remains.

Considerations:

Deadlocks: When two or more transactions are waiting indefinitely for each other to release resources, it's a deadlock situation. Databases usually have mechanisms to detect and handle deadlocks.
Concurrency: Without proper isolation levels, multiple transactions running concurrently can lead to problems like dirty reads, non-repeatable reads, and phantom reads.
Isolation Levels: Databases offer different levels of isolation, each with its own trade-offs. The levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The chosen level affects how locks are managed during transactions.

It's essential to understand and use transactions wisely in your applications, as they play a pivotal role in maintaining the integrity of your data.

How to use transactions in SQL:
- Transactions in SQL are used to group one or more SQL statements into a single unit of work.
```
BEGIN TRANSACTION;
-- SQL statements here
COMMIT;
```
BEGIN TRANSACTION and COMMIT in SQL:
- BEGIN TRANSACTION marks the start of a transaction, and COMMIT commits the transaction's changes to the database.
```
BEGIN TRANSACTION;
-- SQL statements here
COMMIT;
```
ROLLBACK statement in SQL transactions:
- ROLLBACK is used to undo the changes made in the current transaction.
```
BEGIN TRANSACTION;
-- SQL statements here
ROLLBACK;
```

Savepoints in SQL transactions:

Savepoints allow you to create points within a transaction to which you can later roll back.

BEGIN TRANSACTION;
-- SQL statements here
SAVE TRANSACTION SavepointName;
-- More SQL statements
ROLLBACK TO SavepointName;

Nested transactions in SQL:
- Some databases support nested transactions, where a new transaction can be started within an existing one.
```
BEGIN TRANSACTION;
-- SQL statements here
BEGIN TRANSACTION; -- Nested transaction
-- More SQL statements
COMMIT;
COMMIT;
```
Implicit vs. Explicit transactions in SQL:
- Implicit transactions are automatically started, whereas explicit transactions use BEGIN TRANSACTION and COMMIT or ROLLBACK.
```
-- Implicit transaction
-- SQL statements here

-- Explicit transaction
BEGIN TRANSACTION;
-- SQL statements here
COMMIT;
```
Isolation levels in SQL transactions:
- Isolation levels define the degree to which one transaction must be isolated from the effects of other concurrent transactions.
```
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
-- SQL statements here
COMMIT;
```
ACID properties in SQL transactions:
- ACID (Atomicity, Consistency, Isolation, Durability) properties ensure reliable and predictable transactions.
```
SET XACT_ABORT ON;
BEGIN TRANSACTION;
-- SQL statements here
COMMIT;
```
Managing errors and exceptions in transactions:
- Handle errors within a transaction and decide whether to commit or roll back.
```
BEGIN TRY
   BEGIN TRANSACTION;
   -- SQL statements here
   COMMIT;
END TRY
BEGIN CATCH
   ROLLBACK;
END CATCH;
```
Using COMMIT and ROLLBACK with stored procedures:
- Stored procedures can include transactions with COMMIT and ROLLBACK statements.
```
CREATE PROCEDURE ExampleProcedure
AS
BEGIN
  BEGIN TRANSACTION;
  -- SQL statements here
  COMMIT;
END;
```

Transaction control in stored procedures in SQL:

Control the flow of transactions within stored procedures using IF statements.

CREATE PROCEDURE ExampleProcedure
AS
BEGIN
  IF SomeCondition
  BEGIN
     BEGIN TRANSACTION;
     -- SQL statements here
     COMMIT;
  END
  ELSE
  BEGIN
     -- Other SQL statements
  END;
END;

Transaction locks and concurrency control in SQL:
- Locks control access to data to ensure consistency in a multi-user environment.
```
BEGIN TRANSACTION;
-- SQL statements here
COMMIT;
```
Distributed transactions in SQL:
- Some databases support distributed transactions that involve multiple databases.
```
BEGIN DISTRIBUTED TRANSACTION;
-- SQL statements here
COMMIT;
```