Understanding Transactions

10min

1. Overview
The transactions are supported only on the syntax level to allow integration with tools that requires it. While the syntax is accepted, all the queries are executed immediately and with no transactional guarantees.
2. Commands
These commands are used to manage transactions:
2.1. BEGIN
Initiates a new transaction.
Syntax
Pgsql
BEGIN;
BEGIN;
﻿
or
Pgsql
BEGIN TRANSACTION;
BEGIN TRANSACTION;
﻿
2.2. COMMIT
Saves the changes made in a transaction to the database. It simply ends the transaction.
Syntax
Pgsql
COMMIT;
COMMIT;
﻿
or
Pgsql
END TRANSACTION;
END TRANSACTION;
﻿
2.3. ROLLBACK
In Oxla, when you issue a ROLLBACK command, it doesn't undo changes made in the current transaction. It simply finishes the transaction without any rollback action.
Syntax
Pgsql
ROLLBACK;
ROLLBACK;
﻿
3. Example
1) Let’s define a table named products with columns: product_name, price, and stock_quantity.
Pgsql
CREATE TABLE productsnew(
    product_name TEXT,
    price INT,
    stock_quantity INT
);
CREATE TABLE productsnew(
    product_name TEXT,
    price INT,
    stock_quantity INT
);
﻿
Pgsql
COMPLETE
COMPLETE
﻿
2) Next, we want to insert product data into a products table. 
Pgsql
BEGIN;

INSERT INTO productsnew(product_name, price, stock_quantity) VALUES ('Tab', 8000, 20);
INSERT INTO productsnew(product_name, price, stock_quantity) VALUES ('Laptop', 10000, 32);
INSERT INTO productsnew(product_name, price, stock_quantity) VALUES ('Harddisk', 12000, 14);
BEGIN;

INSERT INTO productsnew(product_name, price, stock_quantity) VALUES ('Tab', 8000, 20);
INSERT INTO productsnew(product_name, price, stock_quantity) VALUES ('Laptop', 10000, 32);
INSERT INTO productsnew(product_name, price, stock_quantity) VALUES ('Harddisk', 12000, 14);
﻿
The INSERT statement is executed immediately without waiting for the end of the transaction or until a COMMIT is issued.
You will get the following output:
Pgsql
BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
﻿
3) View the changes by displaying the products table:
Pgsql
SELECT * FROM productsnew;
COMMIT; -- or ROLLBACK;
SELECT * FROM productsnew;
COMMIT; -- or ROLLBACK;
﻿
The product data is now added to the table.
Pgsql
 product_name | price | stock_quantity 
--------------+-------+----------------
 Harddisk     | 12000 |             14
 Tab          |  8000 |             20
 Laptop       | 10000 |             32
(3 rows)

COMMIT
 product_name | price | stock_quantity 
--------------+-------+----------------
 Harddisk     | 12000 |             14
 Tab          |  8000 |             20
 Laptop       | 10000 |             32
(3 rows)

COMMIT
﻿
﻿

Updated 22 Nov 2023

Did this page help you?

Differences Between Oxla vs. PostgreSQL

Public Metrics

Docs powered by Archbee

TABLE OF CONTENTS