Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-transactions/ below:

PostgreSQL - Transactions - GeeksforGeeks

Last Updated : 23 Jul, 2025

A transaction in database terminology is not a new concept. Similar to familiar terms such as "cash transaction" in banking, a transaction in the context of databases like PostgreSQL is a unit of work that ensures data integrity and consistency. Transactions are fundamental when you need to add, delete, or update records. Without transactions, these operations could compromise data integrity due to potential anomalies or concurrent access issues.

Transactions are vital for maintaining the accuracy and reliability of a database. They ensure that even if an operation fails or the system crashes, the data remains consistent and accurate. This article explores how PostgreSQL handles transactions, emphasizing the crucial ACID properties that safeguard data integrity.

The ACID properties describe the essential characteristics that ensure transactions are processed reliably:

• Atomicity: Guarantees that all parts of a transaction are completed successfully. If any part fails, the entire transaction is rolled back (the "all or nothing" principle).
• Consistency: Ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants.
• Isolation: Provides the illusion that each transaction is the only one interacting with the database, thereby preventing transactions from interfering with each other.
• Durability: Once a transaction has been committed, it will remain so, even in the event of a system failure. This ensures that the effects of the transaction are permanently recorded in the database.

There are three main commands in a transaction block. They are:

1. BEGIN: Starts a transaction block.
2. COMMIT: Commits the current transaction block, making all changes made within the block permanent.
3. ROLLBACK: Cancels all changes made in the current transaction block, reverting to the state before the transaction began.

BEGIN;
// set of statements
[COMMIT | ROLLBACK];

Now we will understand the importance of each and every transaction control command, for that, we have to set up a table in the database first.

CREATE TABLE BankStatements (
    customer_id serial PRIMARY KEY,
    full_name VARCHAR NOT NULL,
    balance INT
);
INSERT INTO BankStatements (
    customer_id ,
    full_name,
    balance
)
VALUES
    (1, 'Sekhar rao', 1000),
    (2, 'Abishek Yadav', 500),
    (3, 'Srinivas Goud', 1000);

BEGIN command is used to initiate a transaction. To start a transaction we should give BEGIN command at first if we don't give it like that then the database cant able recognizes the transaction.

Example 1:

BEGIN;
INSERT INTO BankStatements (
     customer_id,
    full_name,
        balance
)
    VALUES(
       4, 'Priya chetri', 500
        );
COMMIT;

Output

COMMIT command is used to save changes and reflect them in the database whenever we display the required data. For suppose we updated data in the database but we didn't give COMMIT then the changes are not reflected in the database. To save the changes done in a transaction, we should COMMIT that transaction for sure.

Example 2:

BEGIN;
    UPDATE BankStatements
        SET balance = balance - 500
        WHERE 
        customer_id = 1;   
    SELECT customer_id, full_name, balance

        FROM BankStatements;
    UPDATE BankStatements
        SET balance = balance + 500
        WHERE 
        customer_id = 2;
COMMIT;
SELECT customer_id, full_name, balance

    FROM BankStatements;
    

Output:

ROLLBACK command is used to undo the changes done in transactions. As we know transactions in database languages are used for purpose of large computations, for example in banks. For suppose, the employee of the bank incremented the balance record of the wrong person mistakenly then he can simply rollback and can go to the previous state.

Example 3

BEGIN;
    DELETE FROM BankStatements
        WHERE 
        customer_id = 1;     
    SELECT customer_id, full_name, balance
        FROM BankStatements;      
ROLLBACK;
SELECT customer_id, full_name, balance
    FROM BankStatements;

Output:

Transactions are a fundamental aspect of managing data integrity in PostgreSQL. Developers can ensure that their database operations are safe, consistent, and reliable by effectively using transactions. Understanding how to implement and utilize these transaction commands within PostgreSQL can significantly enhance database management and operational stability.

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