PostgreSQL concurrency in transactions

Question

I want to understand how transactions work in SQL, specifically in PostgreSQL

Imagine I have a very large table (first_table) and the query below lasts 2 seconds and I execute the query below via psql.

sudo -u postgres psql -f database/query.sql

This is the query:

TRUNCATE TABLE second_table;

INSERT INTO second_table (
    foo1
    ,foo2 
) 
SELECT foo1
       , foo2
FROM first_table;

What can happen if I execute another query selecting from second_table at the same time the previous query is executing. Notice the truncate table at the start of the previous query.

example:

SELECT * FROM second_table;

EDIT: I mean I would get zero or non-zero records in the second query?

`TRUNCATE` is often not part of transactions (Postgresql does support it, though). Are you interested in TRUNCATE specifically or can you change the question to `DELETE FROM second_table;` ? — Thilo, Apr 03 '21 at 08:14

Thilo · Accepted Answer · 2021-04-03T08:20:43.803

I mean I would get zero or non-zero records in the second query?

Under reasonable transaction isolation levels, the database does not allow dirty reads, meaning no transaction can see changes from other transactions that have not yet been committed. (In Postgresql, it is not even an option to turn that off, a very sensible choice in my book).

That means that the second query will either see the contents of the table before the TRUNCATE, or it will see the new records added after the TRUNCATE. But it will not see something in between, i.e. it will not get an empty table (assuming there have been records in the table before the TRUNCATE) and it will not see an incomplete half of the new records (or even a weird mix).

If you say that the second query returns before the first query has committed, then it will have seen the state of the table before any changes from the first query have been applied.

As a side note: Postgres never allows dirty reads - regardless of the chosen isolation level — , Apr 03 '21 at 08:20

PostgreSQL concurrency in transactions

1 Answers1