Re: concurrent updates problem
От | Jan Ploski |
---|---|
Тема | Re: concurrent updates problem |
Дата | |
Msg-id | 5394851.985034383510.JavaMail.jpl@remotejava обсуждение исходный текст |
Ответ на | concurrent updates problem (mwaples@waples.net) |
Список | pgsql-general |
On Mon, Mar 19, 2001 at 02:00:03PM -0500, Tom Lane wrote: > > But the one-line version without select for update is equivalent, right? > > That is, a single UPDATE statement is atomic? > > No, not really. > > What will happen in the case of a conflict is that the second process to > try to update the tuple will wait for the first to commit, then throw > away its computed update tuple and try to re-evaluate the query plan for > the tuple at issue. Which leads to correct results (updates becoming serialized), doesn't it? > I consider the code that does this (EvalPlanQual > in execMain.c) to be a completely untrustworthy hack. Oh no, don't tell us the grave truth ;) > It probably works in really simple query plans like the given example, but > heaven help you if you've got joins, subplans, aggregates, nextval() calls, > etc. To say nothing of rules or triggers, which will see none of this. I have to admit not understanding this devil's work (not intended to offend any of the developers!) in detail, but I have the feeling it should be mentioned in some prominent place in documentation. I recall from reading MySQL manuals that they recommended the UPDATE SET x=x+1 type of queries as a simple way to avoid transactions in some contexts. Now it looks that with PostgreSQL the matter is more complicated, perhaps due to a different policy of row-level locking? Which makes me wonder how many other types of queries that one would think of as atomic at first have such hidden gotchas? Can you recommend a rule that would guard against this kind of errors? Obviously, my naive "each SQL statement = atomic, multiple statements = atomic if grouped into a transaction" does not suffice! Thanks in advance for sharing some more light on it (I read the chapter about multi-version concurrency, but it kind of lacks examples that would make the real-world dangers of loosing consistency clear). -JPL
В списке pgsql-general по дате отправления: