Understanding PostgreSQL Transactions
Transactions in PostgreSQL allow you to group multiple SQL operations into a single logical unit. They ensure that either all the operations succeed or none of them are applied, maintaining database integrity.
1. What is a Transaction?
A transaction is a sequence of operations performed as a single unit of work. PostgreSQL adheres to the ACID properties:
2. Basic Transaction Commands
Command Description Example BEGIN Starts a transaction block. BEGIN; COMMIT Saves changes made in the transaction. COMMIT; ROLLBACK Reverts changes made in the transaction. ROLLBACK;3. Example Usage
Start, Commit, and Rollback
Code:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
If an error occurs during the updates:
Code:
ROLLBACK;
4. Savepoints for Nested Transactions
Use SAVEPOINT to partially rollback within a transaction:
Code:
BEGIN;
SAVEPOINT sp1;
INSERT INTO orders (id, amount) VALUES (1, 500);
ROLLBACK TO sp1;
COMMIT;
5. Transaction Isolation Levels
PostgreSQL supports the following isolation levels:
Isolation Level Description Read Uncommitted Least restrictive; uncommitted changes are visible. Read Committed Default level; sees only committed changes. Repeatable Read Ensures consistency in a single transaction. Serializable Most restrictive; transactions appear sequential.Set isolation level:
Code:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
6. Autocommit Mode
PostgreSQL operates in autocommit mode by default. Disable autocommit for explicit transaction control:
Code:
SET autocommit = OFF;
BEGIN;
-- Execute operations
COMMIT;
7. Error Handling in Transactions
Ensure robust transaction handling:
Code:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Check for sufficient balance
DO $$
BEGIN
IF NOT FOUND THEN
RAISE EXCEPTION 'Insufficient funds';
END IF;
END $$;
COMMIT;
Additional Tips:
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4