Postgresql locks deadlock

Question

I am developing a system using Django + Postgresql. It's my first time with postgresql but I chose it because I needed the transactions and foreign key features.

In a certain view I have to lock my tables with AccessExclusiveLock, to prevent any read or write during this view. That's because I do some checks on the whole data before I save/update my entities.

I noticed an inconsistent error that happens from time to time. It's because of a select statement that happens directly after the lock statement. It demands to have AccessShareLock. I read on postgresql website that AccessShareLock conflicts with AccessExclusiveLock.

What I can't understand is why is it happening in the first place. Why would postgresql ask for implicit lock if it already has an explicit lock that cover that implicit one ? The second thing I can't understand is why is this view running on 2 different postregsql processes ? Aren't they supposed to be collected in a single transaction ?

Thanx in advance.

Answer 1

In PostgreSQL, instead of acquiring exclusive access locks, I would recommend to set the appropriate transaction isolation level on your session. So, before running 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 need repeatable read isolation level.