On 24.11.2020 13:11, Yugo NAGATA wrote:
>
>> I wonder if it is possible to somehow use predicate locking mechanism of
>> Postgres to avoid this anomalies without global lock?
> You mean that, ,instead of using any table lock, if any possibility of the
> anomaly is detected using predlock mechanism then abort the transaction?
Yes. If both transactions are using serializable isolation level, then
lock is not needed, isn't it?
So at least you can add yet another simple optimization: if transaction
has serializable isolation level,
then exclusive lock is not required.
But I wonder if we can go further so that even if transaction is using
read-committed or repeatable-read isolation level,
we still can replace exclusive table lock with predicate locks.
The main problem with this approach (from my point of view) is the
predicate locks are able to detect conflict but not able to prevent it.
I.e. if such conflict is detected then transaction has to be aborted.
And it is not always desirable, especially because user doesn't expect
it: how can insertion of single record with unique keys in a table cause
transaction conflict?
And this is what will happen in your example with transactions T1 and T2
inserting records in R and S tables.
And what do you think about backrgound update of materialized view?
On update/insert trigger will just add record to some "delta" table and
then some background worker will update view.
Certainly in this case we loose synchronization between main table and
materialized view (last one may contain slightly deteriorated data).
But in this case no exclusive lock is needed, isn't it?
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company