CODEWITHSUNDEEP
sqlpostgresqltransactions
user2216697
user2216697

Reputation: 303

SQL Concurrent transactions with SELECT

i have transaction which looks like this:

BEGIN;
SELECT last_table_row;
...some long queries with last_table_row data...
DELETE selected_last_table_row;
COMMIT;

if i run this transaction twice at same time second one will use same last_table_row item as first transaction (tried from psql console) which is not desired. How should i handle this type of transaction to be safe and not having interfering transactions?

Upvotes: 0

Views: 88

Answers (2)

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4572

As well as SELECT FOR UPDATE, considering you'll remove that row at the end of the transaction, you may as well remove it at the beginning copying into a temporary table:

BEGIN;
CREATE TEMP TABLE last_table_row ON COMMIT DROP AS
  WITH ltr AS (
    DELETE FROM yourtable
    WHERE id = (SELECT MAX(id) FROM yourtable)
    RETURNING *)
  SELECT * FROM ltr;

...some long queries with last_table_row data...
COMMIT;

Upvotes: 1

Adam
Adam

Reputation: 5589

Use SELECT FOR UPDATE.

If FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE or FOR KEY SHARE is specified, the SELECT statement locks the selected rows against concurrent updates.

Here is the documentation on The Locking Clause.

Upvotes: 1

Related Questions