How to work with PostgreSQL transactions

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

Transactions

A transaction is a logical unit of work that contains one or more than one SQL statements where either all statements will succeed or all will fail. The SQL statements are NOT visible to other user sessions, and if something goes wrong, it won’t affect the database.

BEGIN

The BEGIN keyword is used to start a transaction block.

Example


   -bash-4.2$ ./psql  postgres -p 9033
psql (11.5)
Type "help" for help.
postgres=# begin;
BEGIN
postgres=#

All the SQL statements that follow BEGIN will be executed as a single transaction unit. The transaction block will end after it reaches the COMMIT or ROLLBACK keywords, which we need to provide explicitly.

COMMIT

The COMMIT keyword saves changes to the database.

Example


   postgres=# begin;
BEGIN
postgres=# create table test(n int);
CREATE TABLE
postgres=# insert into test values(1);
INSERT 0 1
postgres=# end transaction; 
COMMIT

In this example, a table and row has been created in the database, and now other users who have connected to another session/terminal can access this table, provided if they have permission to access it.

Please note: The COMMIT and END TRANSACTION keywords are same.

ROLLBACK

As the name suggests, ROLLBACK undoes the changes that were issued in the transaction block before it.

Example


   postgres=# begin;
BEGIN
postgres=# delete from test;
DELETE 1
postgres=# drop table test;
DROP TABLE
postgres=# rollback;
ROLLBACK
postgres=#

After firing the ROLLBACK command, despite the DROP TABLE statement the table “test” remains in the database and also keeps the data as it is, because we had issued the ROLLBACK statement at the end of the transaction block.

Please note: In PostgreSQL, we can rollback DDL objects as well.

SAVEPOINT

SAVEPOINT is a boundary defined within a transaction that allows for a partial rollback.

It gives the user the ability to roll the transaction back to a certain point without rolling back the entire transaction.

Example


   postgres=# begin;
BEGIN
postgres=# create table my_table(n int);
CREATE TABLE
postgres=# insert into my_table values (1);
INSERT 0 1
postgres=# savepoint my_savepoint;
SAVEPOINT
postgres=# insert into my_table values(2);
INSERT 0 1
postgres=# insert into my_table values(3);
INSERT 0 1
postgres=# select * from my_table;
(3 rows)
postgres=# rollback to my_savepoint; 
ROLLBACK
postgres=# select * from my_table;   
(1 row)
postgres=# commit;
COMMIT
postgres=#

In this example:

1. First, the user creates a table and inserts 1 row. A SAVEPOINT with the name “my_savepoint” is created after that.

2. Then, 2 more rows inserted in the table, so a total or 3 rows are visible to the user now.

3. After firing ROLLBACK to “my_savepoint,” it will undo all the changes that have been done after the point when the SAVEPOINT was created.

4. As a result of step c, only 1 row is now visible to the user.

5. The transaction block is still active, so to finish we need to fire either COMMIT or a second ROLLBACK command.

Please note: We can have multiple SAVEPOINTs within a transaction block.

SQL query error

If there is an error in the SQL query then entire transaction block will be aborted immediately and any SQL statement that was written before the error will automatically roll back.

Example


   postgres=# begin;
BEGIN
postgres=# create table test123(n int);
CREATE TABLE
postgres=# select * from does_not_exist_table;
ERROR:  relation "does_not_exist_table" does not exist
LINE 1: select * from does_not_exist_table;
postgres=# select 'Hi';
ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres=# rollback;
ROLLBACK
postgres=# \d test123
Did not find any relation named "test123".
postgres=#

As we can see, table “test123” has been rolled back and cannot be found.

To avoid such a scenario where work is lost, using SAVEPOINT is a must.

Example


   postgres=# begin;
BEGIN
postgres=# create table edb(n int);
CREATE TABLE
postgres=# insert into edb values (1);
INSERT 0 1
postgres=# savepoint my_point;
SAVEPOINT
postgres=# insert into edb values (2);
INSERT 0 1
postgres=# insert into edb values (3);
INSERT 0 1
postgres=# insert into does_not_exist values (1);
ERROR:  relation "does_not_exist" does not exist
LINE 1: insert into does_not_exist values (1);
postgres=# insert into edb values (4);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres=# rollback to my_point;
ROLLBACK
postgres=# select * from edb;
(1 row)
postgres=# commit;
COMMIT
postgres=#

As we can see, only the data entered into the table “edb” before the SAVEPOINT has been preserved.

Benefits of transactions

Recover from user error: We can ROLLBACK (erase) a mistake. For instance, I want to delete a particular row from a table. I fired a DELETE statement without a WHERE condition (by mistake). Unfortunately that would delete all records from my table, but I just wanted to delete only a few rows. However, I can easily recover from my mistake if I am inside a transaction block — by firing the ROLLBACK command.

As mentioned earlier, other concurrent user sessions would not get affected by a transaction unless it gets locked. In the example below, I have inserted 1 row into a table inside a transaction (following a BEGIN command), and am trying to drop the same table from another session. This will not work, because a lock is held on the table until the end of the transaction.

The session on the Y terminal will be in hung state until we end the transaction block in X terminal.

Please refer to the PostgreSQL community documentation for locking: https://www.postgresql.org/docs/11/explicit-locking.html .

Data is not visible to other users until the transaction block is finished.

In the Y terminal, we won’t be able to see the second row until the transaction block gets committed.

Transaction parameters

The characteristics of transactions can be set within the BEGIN keyword:


   postgres=# \h begin
Command:     BEGIN
Description: start a transaction block
Syntax:
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]
where transaction_mode is one of:
    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY
    [ NOT ] DEFERRABLE

The WORK and TRANSACTION keywords are optional in a BEGIN command.

Example


   postgres=# begin transaction;
BEGIN
postgres=# \c 
You are now connected to database "postgres" as user "postgres".
postgres=# begin work;
BEGIN
postgres=#

In transaction_mode, we have the following options:

1. READ WRITE – User can perform R/W operations.

2. READ ONLY – User can perform Read operation.

Example


   postgres=# begin  read only;
BEGIN
postgres=# create table rty(n int);
ERROR:  cannot execute CREATE TABLE in a read-only transaction

3. ISOLATION LEVEL – User can define what data the transaction can see when other transactions are running concurrently. There are 4 different levels:

i) READ COMMITTED – This is the default isolation level in Postgres. At this level, a query within a transaction will pick up changes made by another committed transaction.

Example

psql (X terminal)


   postgres=# begin isolation level read committed;  
BEGIN
postgres=# select * from test1;
(0 rows)

psql (Y terminal)


   postgres=# insert into test1 values (8);
INSERT 0 1
postgres=#

psql (X terminal)


   postgres=# select * from test1;     <--Data is visible
(1 row)

ii) READ UNCOMMITTED is not supported in PostgreSQL. Even if we specify it, it will be treated the same as READ COMMITTED.

iii) In SERIALIZABLE and iv) REPEATABLE READ – Committed rows in other sessions are only visible to the transaction block when it ends.

Example

psql (X terminal)


   [tushar@localhost bin]$ ./psql  postgres -U postgres
psql (11.5)
Type "help" for help.
postgres=# begin isolation level serializable ;
BEGIN
postgres=# select * from test1;
(0 rows)

psql (Y terminal)


   postgres=# insert into test1 values (13);
INSERT 0 1
postgres=#

If we go back again to the X terminal, the row added in the Y terminal is not visible until we end the transaction block.


   postgres=# select * from test1;
(0 rows)
postgres=#

The difference between these two isolation levels is that REPEATABLE READ allows Serialization Anomaly whereas SERIALIZABLE does not.

Please refer to the Postgres community documentation for more on transaction_mode parameters: https://www.postgresql.org/docs/11/sql-set-transaction.html .

Hope this helps!

Connecting PostgreSQL using psql and pgAdmin

How to use PostgreSQL with Django

10 Examples of PostgreSQL Stored Procedures

How to use PostgreSQL with Laravel

How to use tables and column aliases...

PostgreSQL vs. SQL Server (MSSQL)...

The Complete Oracle to PostgreSQL Migration...

PostgreSQL vs. MySQL: A 360-degree Comparison...

PostgreSQL Replication and Automatic Failover...

Postgres on Kubernetes or VMs: A Guide...