Resources
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
- Let’s define a table named
products
with columns:product_name
,price
, andstock_quantity
.
CREATE TABLE productsnew(
product_name TEXT,
price INT,
stock_quantity INT
);
Upon successful creation, you will get the output below.
CREATE
- 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
- 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
- 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