CODEWITHSUNDEEP
sqlpostgresqltransactions
Matteo Pagliazzi
Matteo Pagliazzi

Reputation: 5270

PostgreSQL lock row before delete?

I have a transaction in postgresql where I execute the following commands:

  1. SELECT "amount", "accountId" from "transactions" where "id" = "123"
  2. DELETE from "transactions" where "id" = "123"
  3. UPDATE "accounts" set "balance" = "balance" - amountFetchedInCommand1 where "id" = accountFetchedInCommand1

Now I need to be sure that between command 1 and 2 the row fetched from transactions doesn't get modified (especially its amount and accountId fields).

Can I use a SELECT FOR UPDATE statement to lock the row fetched at command 1 even though I'm not going to update the row but only delete it?

Otherwise what's the best way to ensure this operation always run correctly?

(transactions.accountId is a foreign key linked to account.id with ON DELETE CASCADE)

Upvotes: 0

Views: 780

Answers (2)

Sergi Cruz
Sergi Cruz

Reputation: 1

Use Begin; //code Commit;

Or Start Transaction [parameters] //code Comit;

more documentation in: http://www.postgresql.org/docs/9.1/static/sql-start-transaction.html

Upvotes: -1

user330315
user330315

Reputation:

You can do step 2 and step 3 in a single statement: 

with deleted as (
   DELETE from transactions 
   where id = 123
   return amount, "accountId"
)
UPDATE accounts 
   set balance = accounts.balance - d.amount 
from deleted d
where accounts.id = d."accountId";

Upvotes: 2

Related Questions