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.
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.