PostgreSQL Views – How SELECT FOR UPDATE Behaves with Views

lockingpostgresqltransaction

To retrieve domain model data from PostgreSQL, I sometimes select against a view rather than a table. I do this so that I can return as much information as possible in one query, rather than fire off multiple queries to resolve all relational dependencies. Mostly this is because there is a 1-to-1 relationship between tables, or they are lookup tables and I just need certain columns, etc. To prevent certain race conditions, one strategy I'm using is SELECT ... FOR SHARE/UPDATE queries. I'm wondering how these behave when we start using them against views.

Suppose we have two tables A and B, each with a column ID. Now, suppose we have a view AB that is a join operation between those two tables. Let's assume this is a Cartesian join:

SELECT
    "A"."ID" AS "AID",
    "B"."ID" AS "BID"
FROM "A"
CROSS JOIN "B"

If we then SELECT FOR UPDATE against AB what rows of what tables will get locked in the following instances?

SELECT * FROM "AB" WHERE "AB"."AID" = :ID
SELECT * FROM "AB" WHERE "AB"."BID" = :ID
SELECT * FROM "AB" WHERE "AB"."AID" = :ID1 AND "AB"."BID" = :ID2

Best Answer

Short answer

The following rows will be locked for update in the three cases:

a row with "ID" = :ID in table "A" and all rows in table "B"
a row with "ID" = :ID in table "B" and all rows in table "A"
a row with "ID" = :ID1 in table "A" and a row with "ID" = :ID2 in table "B"

The results are as one can expect after reading this statement in the documentation:

If a locking clause is applied to a view or sub-query, it affects all tables used in the view or sub-query.

How to test similar cases

Install the extension pgrowlocks..

create extension if not exists pgrowlocks;

Run two instances of psql. In the first one start a transaction and execute a query.

psql #1:

begin;

select *
from "AB"
where "AID" = 3 and "BID" = 103
for update;

You can see locked rows in the other instance of psql.

psql #2:

select * from pgrowlocks('"B"');

 locked_row | locker | multi |  xids   |     modes      |  pids
------------+--------+-------+---------+----------------+--------
 (0,4)      |  88577 | f     | {88577} | {"For Update"} | {8068}
(1 row)

Do not forget to close the transaction at the end (before a next test).

psql #1:

rollback;

The first column of the above result is ctid. You can select the original row with the query:

select *
from "B"
where ctid = '(0,4)';

Related Solutions

Db2 – the locking behavior for Updatable/Insertable Views

The short answer is that it depends on the isolation level of your connection and/or statement being run.

If you are taking the default isolation level (which is usually Cursor Stability - CS) in DB2, then I would assume it only puts a lock on the actual rows being modified (as an update lock) and it puts a read lock on the row currently being held by the cursor. All other rows are not technically locked in any sort of way.

Now if you go up higher into Read Stability (RS) or Repeatable Read (RR), then you start to get into where it will lock more rows in the result set (and even the whole table in a join case)....

You may wish to check out the Isolation Level settings in the DB2 Information Center, as this will give you a better picture of how it will act.

Also note, even though there is a default isolation level for the connection, this can be overridden in the session, as well as at the statement level, including the underlying statement that builds the view. So if the default is CS, but the view was built with a SELECT that uses RR, then you are using RR and probably locking on all rows in both the result set and all rows utilized by the engine to create the result set. I'm guessing this is not likely (or at least hopefully someone didn't write the view that way....) and you are using CS, in which what I explained at the beginning is more likely how it behaves.

Sql-server – How to avoid locks by large transactions

I don't think you have to force all of your deletes to occur within a single, monolithic transaction. Instead of having a transaction that does:

BEGIN TRANSACTION;
  DELETE all the things from child table 1;
  DELETE all the things from child table 2;
  ...
  DELETE all the things from child table N;
  DELETE all the things from parent table;
COMMIT TRANSACTION;

Why not delete in chunks? You can play around with the TOP (?) parameter based on how many rows lead to what kind of duration of transaction (there is no magic formula for this, even if we did have a lot more information about your schema). Pseudo-code:

DECLARE @p TABLE(p INT PRIMARY KEY);

SELECT @rc = 1;

WHILE @rc > 0
BEGIN
  DELETE @p;

  INSERT @p SELECT TOP (?) primary_key FROM parent table 
    WHERE (clause that defines the data to be deleted);

  SET @rc = @@ROWCOUNT;

  BEGIN TRANSACTION;
    DELETE child table 1 WHERE parentID IN (SELECT p FROM @p);
    DELETE child table 2 WHERE parentID IN (SELECT p FROM @p);
    ...
    DELETE child table N WHERE parentID IN (SELECT p FROM @p);

    DELETE parent table WHERE parentID IN (SELECT p FROM @p);
  COMMIT TRANSACTION;
  -- to minimize log impact you may want to CHECKPOINT
  -- or backup the log here, every loop or every N loops
END

This may extend the total amount of time that the operation takes (especially if you backup or checkpoint on each loop, or add an artificial delay using WAITFOR, or both), but should allow other transactions to sneak in between chunks, waiting for shorter transactions instead of the whole process.

I wrote a lot more about this technique here.