0

This is straight from the postgresql docs and I am having a hard time understanding why no rows are deleted. Can anybody explain it to me, since I would expect 1 row to be deleted. Thanks.


https://www.postgresql.org/docs/current/transaction-iso.html

More complex usage can produce undesirable results in Read Committed mode. For example, consider a DELETE command operating on data that is being both added and removed from its restriction criteria by another command, e.g., assume website is a two-row table with website.hits equaling 9 and 10:

BEGIN;
UPDATE website SET hits = hits + 1;
-- run from another session:  DELETE FROM website WHERE hits = 10;
COMMIT;

The DELETE will have no effect even though there is a website.hits = 10 row before and after the UPDATE. This occurs because the pre-update row value 9 is skipped, and when the UPDATE completes and DELETE obtains a lock, the new row value is no longer 10 but 11, which no longer matches the criteria.

Because Read Committed mode starts each command with a new snapshot that includes all transactions committed up to that instant, subsequent commands in the same transaction will see the effects of the committed concurrent transaction in any case. The point at issue above is whether or not a single command sees an absolutely consistent view of the database.

2 Answers 2

2

The DELETE statement is executed in three steps:

  1. The table is scanned for rows that meet the condition. This is much like a SELECT and is not blocked by row locks.

  2. Each row found in the first step is locked with a FOR NO KEY UPDATE or FOR UPDATE lock.

    If that lock is blocked by a conflicting lock from a concurrent transaction, wait until the lock is released, fetch the latest committed version of the row (it might have been modified by a concurrent transaction) and re-evaluate the condition. If the condition is still true, lock the row, otherwise ignore it.

  3. Delete the locked row (mark it as invisible).

The row were hits was 9 is skipped in step 1, because the concurrent UPDATE has not yet committed, and the updated version is not yet visible.

The row where hits was 10 is found in step 1, but the lock taken in step 2 blocks because of the concurrent UPDATE. As soon as the concurrent UPDATE is done, PostgreSQL reads the latest version of that row and figures out there is none.

This strange behavior occurs because you are on a low isolation level, where anomalies are still pretty common. If you choose the next isolation level, REPEATABLE READ, the anomaly is avoided, because the transaction will receive a serialization error and has to be repeated.

1
  • @Mat Thanks for the correction. I should be more careful. Commented Oct 19, 2023 at 8:19
0

The key to this in the second paragraph in that section:

UPDATE [and] DELETE […] 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). 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. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row.

Notice the two UPDATE operations on the two rows are independent, they are not atomic.

So the first row (that is updated from 9 to 10) is not deleted since it was not found in the original snapshot before it was committed, and the second row (that is updated from 10 to 11) is not deleted since it was found but no longer having a value of 10 after being committed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.