Hi there!
Today I realised that my knowledge concerning how postgres handles concurrency is not very good, and its even worse
whenit comes to using that knowledge in real-life.
Let me give you an example.
I have this table
create table userpositions ( userID int, positionID int, unique (userID,positionID));
For a given userID there can be many positionIDs.
There are then two operations performed on this table, the first is "select positionID from userpositions where
userID=..."to get all positions for a user, and the second is to replace all positions for the user with new positions.
Forthis I was thinking about running it in a transaction
begin;
delete from userpositions where userID=...;
insert into userpositions (userID,positionID) values ....;
commit;
But will this work? I don't want select to return empty results, I don't want two inserts running causing a unique
violation.
Experimenting with it tells me yes, it will work, but how should I reason to "convinse" my self that it will work?
Quoting the docs: "The partial transaction isolation provided by Read Committed mode is adequate for many
applications,and this mode is fast and simple to use; however, it is not sufficient for all cases. Applications that do
complexqueries and updates might require a more rigorously consistent view of the database than Read Committed mode
provides."
How do I know I'm not creating one of those complex queries? Or to put it differntly, how do you identify a complex
querywith potential issues? Any special techniques to analyze?
/J