Search Results

In the case of ACCESS SHARE, you could do that with SET default_transaction_read_only = on; That will cause an error as soon as anybody wants to take a lock higher than ACCESS SHARE. But it seems tha …

I think that you can prevent this problem by putting FOR UPDATE at the end of your SELECT statements. I am not sure because your description is somewhat confusing. To avoid deadlocks, add an ORDER BY …

What the SQL standard has to say about concurrency is the definition of transaction isolation levels. Depending on which one you choose, you get certain guarantees. I won't quote the standard, since t …

The idea to synchronize application threads via regular row locks in a special table is a bad one. The reason is that you need to keep a database transaction open as long as you want to hold the lock, …

Assuming that it works like PostgreSQL in this case: This is a serialization error that you get because you are using the SERIALIZABLE transaction isolation level. That is nothing to worry about, ju …

Yes, both statements will take a UPDATE lock on all the modified rows. See the documentation on row locks: The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE …

Normal operations don't place a persistent lock on pg_class. There must be a DDL command (CREATE TABLE, DROP TABLE or similar) that is blocked because of a lock, so that it cannot complete the operat …

You can query pg_locks after the procedure is done, but before the transaction ends. Since locks are held until the end of the transaction, you should see all locks taken by statements in the procedur …

Explicitly locking tables is almost always the wrong thing to do. If you describe the use case in more detail, I am confident that a better solution can be found. … Locking tables frequently will keep autovacuum from doing its important work on the table, and eventually an anti-wraparound autovacuum job will come along and block you, bringing processing to a standstill …

Locks are not taken when the statement starts, but when the row is found. Since you are using pg_sleep(5) in the WHERE condition, it take five seconds before the row is found and PostgreSQL attempts t …

That is indeed not clear from the documentation, but a simple experiment shows that advisory locks are local to the database. That is, you can take the same advisory lock in two different databases wi …

My advice is to use a “deferred constraint trigger” to update the "Products" table whenever "Items" is modified. Such triggers are AFTER triggers that are not run immediately after the statement, but …

You could use transaction level advisory locks to serialize your operations: BEGIN; -- take an advisory lock for the duration of the session SELECT pg_advisory_xact_lock(4711); SELECT * FROM inventory …

The order in which rows are locked is not guaranteed. You could use EXPLAIN to see the execution plan, which will reveal what the database does. But don't rely on an execution plan: changes in the dat …

A normal autovacuum process will succumb silently if it blocks another transaction, but if that happens all the time and autovacuum can never finish, you will eventually have old table rows that have …