Postgresql blocks deadlock

Question

I am developing a system using Django + Postgresql. This is my first time with postgresql, but I chose it because I need transactions and foreign key functions.

In a specific way, I need to lock my tables with AccessExclusiveLock to prevent reading or writing during this view. This is because I do some checks on all the data before saving / updating my objects.

I noticed a fickle error that happens from time to time. This is because of the select statement that occurs immediately after the lock statement. It requires AccessShareLock. I read on the postgresql website that AccessShareLock conflicts with AccessExclusiveLock.

I do not understand why this is happening in the first place. Why does postgresql request an implicit lock if it already has an explicit lock that covers the implicit? The second thing I cannot understand is why does this view work on two different postregsql processes? Are they supposed to be collected in one transaction?

Thanx in advance.

Answer 1

In PostgreSQL, instead of acquiring exclusive access locks, I would recommend setting the appropriate level of transaction isolation in your session. Therefore, before starting your “update”, send the following command to your database:

begin;
set transaction isolation level repeatable read;
-- your SQL commands here
commit;

According to your description, you will need a repeatable reading level .