CODEWITHSUNDEEP
postgresqltransactions
machinery
machinery

Reputation: 6290

PostgreSQL for update statement

PostgreSQL has read committed isolation level. Now I have a transaction which consists of a single DELETE statement and this delete statement has a subquery consisting of a SELECT statement for selection the rows to delete.

Is it true that I have to use FOR UPDATE in the select statement to get no conflicts with other transaction?

My thinking is the following: First the corresponding rows are read out from the table and in a second step these rows are deleted, so another transaction could interfere.

And what about a simple DELETE FROM myTable WHERE id = 4 statement? Do I also have to use FOR UPDATE?

Upvotes: 0

Views: 7261

Answers (2)

Daniel Vérité
Daniel Vérité

Reputation: 61506

Short version: the FOR UPDATE in a sub-select is not necessary because the DELETE implementation already does the necessary locking. It would be redundant.

Ideally you should read and digest Concurrency Control to learn how the concurrency issues are dealt with by the SQL engine.

Specifically for the case you're mentioning, I think these couple of excerpts are the most relevant, in Read Committed Isolation Level:

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time.

However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress).

So one of your two concurrent DELETE will be put to wait, as soon as it tries to delete a row that the other one already processed just before. This wait will only end when the other one commits or roll backs. In a way, that means that the engine "detected the conflict" and serialized the two DELETE in order to deal with that conflict.

If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row.

In your scenario, after the first DELETE has committed and the second one is waked up, the second one will be unable to delete the row that it was put to wait for, because it's no longer current, it's gone. That's not an error in this isolation level. The execution will just go on with the other rows, some of which may also have disappeared. Eventually it will report the actual number of rows that were deleted by this statement, that may be different from the number that the sub-select initially found, before the statement was put to wait.

Upvotes: 2

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95512

Is it true that I have to use FOR UPDATE in the select statement to get no conflicts with other transaction?

What does "no conflicts with other transaction" mean to you? You can test this by opening two terminals, and executing statements in each of them. Interleaved correctly, the DELETE statement will make the "other transaction" (the one that has its isolation level set to READ COMMITTED) wait until it commits or rolls back.

                                                 sandbox=# set transaction isolation level read committed;
                                                 SET

sandbox=# select * from customer;
 date_of_birth 
---------------
 1996-09-29
 1996-09-28
(2 rows)

sandbox=# begin transaction;
BEGIN
sandbox=# delete from customer
sandbox-# where date_of_birth = '1996-09-28';
DELETE 1
                                                 sandbox=# update customer
                                                 sandbox-# set date_of_birth = '1900-01-01'
                                                 sandbox-# where date_of_birth = '1996-09-28';
                                                 (Execution pauses here, waiting for transaction in other terminal.)
sandbox=# commit;
COMMIT
sandbox=# 
                                                 UPDATE 0
                                                 sandbox=#

See below for the documentation.

And what about a simple DELETE FROM myTable WHERE id = 4 statement? Do I also have to use FOR UPDATE?

There's no such statement as DELETE . . . FOR UPDATE.

You need to be sensitive to context when you're reading about database updates. Update can mean any change to a database; it can include inserting, deleting, and updating rows. In the docs cited below, "locked as though for update" is explicitly talking about UPDATE and DELETE statements, among others.

Current docs

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends. The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values on certain columns. Currently, the set of columns considered for the UPDATE case are those that have an unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future. Also, if an UPDATE, DELETE, or SELECT FOR UPDATE from another transaction has already locked a selected row or rows, SELECT FOR UPDATE will wait for the other transaction to complete, and will then lock and return the updated row (or no row, if the row was deleted).

Upvotes: 3

Related Questions