On Wed, 25 Apr 2012 16:17:53 -0700 (PDT) Janne H <jannehson51@yahoo.com> wrote:
> 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?
Just try it.
Open two psql sessions and manually enter the commands one at a time,
alternating between windows. Try some scenarios that you're concerned
about and see what happens. Experience will teach you a lot very
quickly.
I can say, from experience, that what you have above is not
sufficient to protect you from empty result sets and duplicate
keys in rare, but annoyingly persistent cases. Especially if your
front-end is a web-app, you may be shocked to find out how often
users will double-click instead of single click, thus resulting in
the exact same queries running close enough together to conflict
with each other. Of course, this can be fixed in many ways, but
understanding why it happens is the first step to choosing the
best approach for your application.
--
Bill Moran <wmoran@potentialtech.com>