Re: How can I see if my code is "concurrency safe"?
От | Ben Chobot |
---|---|
Тема | Re: How can I see if my code is "concurrency safe"? |
Дата | |
Msg-id | 2C604E51-1A22-4556-B64A-9FBC4BCA0864@silentmedia.com обсуждение исходный текст |
Ответ на | How can I see if my code is "concurrency safe"? (Janne H <jannehson51@yahoo.com>) |
Ответы |
Re: How can I see if my code is "concurrency safe"?
|
Список | pgsql-general |
On Apr 25, 2012, at 5:17 PM, Janne H 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? Think about it this way: once one session starts a transaction, any modifications it makes are invisible to other sessionsuntil you commit your transaction, at which point they all become visible atomically. (Unless those other sessionshave explicitly said, "yes, I want to play with fire" and changed their isolation mode to something other than ReadCommitted.) So given what you've said, it seems like you should be set, so long as you don't have two different sessions try to insertthe same {userID,positionID} tuple. If that happens, then the second one that tries to commit will fail. You can avoidthat by using a sequence for positionID, which will result in gaps and non-sequential IDs, but also no uniqueness failures.
В списке pgsql-general по дате отправления: