Understanding Transactions

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 by calling one of the syntax below.

2.2. COMMIT

Saves the changes made in a transaction to the database. It simply ends the transaction.
Call one of the syntax below.

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.

ROLLBACK;

3. Example

1. Let’s define a table named products with columns: product_name, price, and stock_quantity.

CREATE TABLE productsnew(
    product_name TEXT,
    price INT,
    stock_quantity INT
);

Upon successful creation, you will get the output below.

CREATE

2. Next, we want to insert product data into a products table.

BEGIN;
INSERT INTO productsnew(product_name, price, stock_quantity) VALUES ('Tab', 8000, 20);

- Transactions cannot include anything more than one statement.
- 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:

BEGIN
INSERT 0 1

3. View the changes by displaying the products table:

SELECT * FROM productsnew;
COMMIT;

The product data is now added to the table.

 product_name | price | stock_quantity 
--------------+-------+----------------
 Harddisk     | 12000 |             14
(1 row)

COMMIT