PostgreSQL - Transactions

In PostgreSQL, like in many relational database systems, a transaction is a unit of work that is treated in a coherent and reliable way independent of other transactions. Transactions ensure that the database remains in a consistent state even if some operations fail.

Here are the main concepts and operations related to transactions in PostgreSQL:

Properties of Transactions:
Transactions are designed to maintain the following ACID properties:
- Atomicity: All operations within the transaction are completed successfully; otherwise, the transaction is aborted.
- Consistency: A transaction ensures that the database moves from one consistent state to another.
- Isolation: Each transaction is isolated from others, meaning the operations in one transaction are hidden from other simultaneous transactions until they're completed.
- Durability: Once a transaction is committed, its effects are permanent in the database.
Beginning a Transaction:
Use the BEGIN command to start a transaction:
```
BEGIN;
```
Committing a Transaction:
Once you're sure you want to save the changes made in a transaction, you use the COMMIT command:
```
COMMIT;
```
Rolling Back a Transaction:
If you decide not to save the changes, or if there's an error and you need to revert to the state before the transaction began, you use the ROLLBACK command:
```
ROLLBACK;
```
Savepoints:
Within a transaction, you can set one or more savepoints. If you decide you want to rollback part of a transaction, you can rollback to a specific savepoint without affecting the earlier commands in the transaction.
```
SAVEPOINT savepoint_name;
...
ROLLBACK TO savepoint_name;
```
Isolation Levels:
PostgreSQL supports several transaction isolation levels that determine the visibility of uncommitted changes from one transaction in other transactions. The levels are:
- Read Committed: This is the default. Each SQL statement within a transaction sees a snapshot of data as of the beginning of that statement.
- Repeatable Read: All statements in a transaction see a snapshot of data as of the beginning of the transaction.
- Serializable: This is the strictest level, ensuring full serializability of transactions.
- Read Uncommitted: Not supported in PostgreSQL. Treating it as Read Committed.
Set the isolation level using:
```
SET TRANSACTION ISOLATION LEVEL level_name;
```
Concurrency Control:
PostgreSQL uses Multi-Version Concurrency Control (MVCC) to manage concurrent access to the database. This means that when one transaction is making changes to a piece of data, it doesn't lock out other transactions. Instead, each transaction sees a "snapshot" of the database, ensuring high concurrency and performance.
Locking:
While PostgreSQL's MVCC reduces the need for locking, there are still situations where explicit locks are required. PostgreSQL provides various lock modes and commands, like LOCK TABLE, to manage explicit locking.

In summary, transactions are a fundamental aspect of ensuring data integrity and consistency in PostgreSQL. Properly utilizing transactions can safeguard against data anomalies and provide mechanisms for error recovery, making them indispensable in many database operations.

How to begin a transaction in PostgreSQL: Begin a transaction in PostgreSQL using the BEGIN statement:
```
BEGIN;
```
PostgreSQL commit and rollback commands: Commit a transaction to make changes permanent or rollback to undo changes:
```
COMMIT; -- to save changes
ROLLBACK; -- to undo changes
```

Savepoint in PostgreSQL transactions: Create a savepoint within a transaction for partial rollback:

SAVEPOINT my_savepoint;
-- Perform some operations
ROLLBACK TO my_savepoint; -- Partial rollback

Isolation levels in PostgreSQL transactions: Set transaction isolation levels using SET TRANSACTION ISOLATION LEVEL:
```
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
Handling errors in PostgreSQL transactions: Use exception handling or condition checks to handle errors within a transaction:
```
BEGIN;
-- Your operations
EXCEPTION
   WHEN others THEN
      ROLLBACK;
END;
```
View active transactions in PostgreSQL: View active transactions using the pg_stat_activity system view:
```
SELECT * FROM pg_stat_activity WHERE state = 'active';
```