Postgres behaviour of concurrent DELETE RETURNING queries

Question

If I have a database transaction which goes along the lines of:

DELETE FROM table WHERE id = ANY(ARRAY[id1, id2, id3,...]) RETURNING foo, bar;

if num_rows_returned != num_rows_in_array then
    rollback and return

Do stuff with deleted data...
Commit

My understanding is that the DELETE query will lock those rows, until the transaction is committed or rolled back. As according to the postgres 9.1 docs:

An exclusive row-level lock on a specific row is automatically acquired when the row is updated or deleted. The lock is held until the transaction commits or rolls back, just like table-level locks. Row-level locks do not affect data querying; they block only writers to the same row.

I am using the default read committed isolation level in postgres 9.1.13

I would take from this that I should be OK, but I want to ensure that this means the following things are true:

Only one transaction may delete and return a row from this table, unless a previous transaction was rolled back.
This means "Do stuff with deleted data" can only be done once per row.
If two transactions try to do the above at once with conflicting rows, one will always succeed (ignoring system failure), and one will always fail.
Concurrent transactions may succeed when there is no crossover of rows.
If a transaction is unable to delete and return all rows, it will rollback and thus not delete any rows. A transaction may try to delete two rows for example. One row is already deleted by another transaction, but the other is free to be returned. However since one row is already deleted, the other must not be deleted and processed. Only if all specified ids can be deleted and returned may anything take place.

"Do stuff with deleted data..." FWIW, this looks suspicious. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Mar 1, 2015 at 18:51
What do you mean, "I should be OK"? Also, what isolation level are you assuming? — philipxy
– philipxy, Commented Mar 2, 2015 at 13:29
I'm using read committed and I mean that my list of requirements should be satisfied based upon my understanding, which may be false. — Matthew Mitchell
– Matthew Mitchell, Commented Mar 2, 2015 at 15:15
@MatthewMitchell: Usually, you delete rows when you're done with them. That means you don't need to "do stuff with them". Along with the odd requirement that the number of rows equals the number of IDs in the WHERE clause gives this the feel of an x-y problem. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Mar 3, 2015 at 10:51

philipxy · Accepted Answer · 2015-03-02 03:52:10Z

1

Using the normal idea of concurrency, processes/transactions do not fail when they are locked out of data, they wait.

The DBMS implements execution in such a way that transactions advance but only seeing effects from other transactions according to the isolation level. (Only in the case of detected deadlock is a transaction aborted, and even then its implemented execution will begin again, and the killing is not evident to its next execution or to other transactions except per isolation level.) Under SERIALIZABLE isolation level this means that the database will change as if all transactions happened without overlap in some order. Other levels allow a transaction to see certain effects of overlapped implementation execution of other transactions.

However in the case of PostgresSQL under SERIALIZABLE when a transaction tries to commit and the DBMS sees that it would give non-serialized behaviour the tranasaction is aborted with notification but not automatically restarted. (Note that this is not failure from implementation execution attempted access to a locked resource.)

(Prior to 9.1, PostgrSQL SERIALIZABLE did not give SQL standard (serialized) behaviour: "To retain the legacy Serializable behavior, Repeatable Read should now be requested.")

The locking protocols are how actual implementation execution gets interleaved to maximize throughput while keeping that true. All locking does is prevent actual overlapped implementation execution accesses to effect the apparent serialized execution.

Explicit locking by transaction code also just causes waiting.

Your question does not reflect this. You seem to think that attempted access to a locked resource by the implementation aborts a transaction. That is not so.

edited Mar 2, 2015 at 3:52

answered Mar 2, 2015 at 3:13

philipxy

15.2k6 gold badges44 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matthew Mitchell Over a year ago

Hi. I'm using the default READ COMMITTED isolation level. "You seem to think that attempted access to a locked resource by the implementation aborts a transaction. That is not so." I do not think you understand what I wanted to happen. I want the transaction to fail if the DELETE query fails to delete and return all required rows. Please see where I put rollback and return. But what you are saying is that the rows aren't locked until the end of the transaction unless SERIALIZABLE is used? The docs do not mention this.

philipxy Over a year ago

Please edit READ COMMITTED into your question. I'll add more re your bullets when i get the chance. After I posted I realized by "fail" you likely meant among transactions running this code & assuming those are the only transactions running. Understand the "concurrency" link 13.2 (also 13.1) and MVCC. You don't need to understand locks to understand behaviour that doesn't use explicit locks.

Collectives™ on Stack Overflow

Postgres behaviour of concurrent DELETE RETURNING queries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related