Re: PREPARE TRANSACTION and webapps - Mailing list pgsql-general

Martijn van Oosterhout <kleptog@svana.org> writes:

> They make take less memory but they take many more resources. Backend
> don't take locks by themselves, transactions do.

And backends have transactions implicitly. The point here is that if you're
going to suspend transactions by leaving idle backends around that's an added
cost over just suspending the transaction. It's not a trivial cost either,
processes consume memory, they consume kernel resources and cause extra
context switching.

> What I don't understand about this whole discussion is that the
> concurrency control needed on a user level is of a completely different
> nature to what a programmer needs when writing programs. Let me give an
> example:
>
> User 1: Opens record 1 and begins to edit
> User 2: Opens record 1 and begins to edit
>
> Obviously these should both succeed. reading data doesn't block. Ten
> minutes later user 1 submits an update and goes to lunch without
> committing. User 2 then does an update but he has to wait. How long?
> Well, by your definition, forever. I doubt user 2 will be very happy
> with that.

There's nothing stopping you from coding up a daemon that checks for suspended
transactions older than some predetermined policy and rolling them back
automatically. If you invent your own transaction semantics above Postgres's
you'll have to do the same thing anyways.

> The way I would think about it would be to (a) let user 2 know straight
> away someone else is already looking at this record. This is useful
> info, maybe they talked to the same customer? and (b) when user 2
> submits his edit he should be warned there are conflict and be asked to
> resolve them. If you abort either transaction you're going to have some
> annoyed users on your hands.

It's not obvious that these should both succeed anyways. I would have expected
you to do SELECT ... FOR UPDATE and lock the record. This would still allow
other sessions to retrieve the data but not begin editing it. You would
presumably want to use NOWAIT as well and handle the error if it's already
locked.

That would prevent two users from ever getting to the edit screen. You could
give the second user the option of breaking the lock -- rolling back the other
user's transaction.

> Both of these fall outside MVCC. You can already check if the record
> was modified since you looked at it, no extra features needed there.
> Can you give an example of where MVCC for long running transactions
> makes sense?

You're assuming a simple case of a single record. What if the update screen
covers a complete data structure represented by many records in many tables.
And the update itself requires multiple stages on several different screens.
Now you reach a conflict and want to roll back all the changes from all those
screens. That requires a fairly large amount of machinery and all that
machinery already exists in Postgres. If you really need all that complexity
it makes sense to leverage the tool you have that implements it all.

I agree with Tom Lane here and the conventional dogma that you can nearly
always avoid this entire problem. And avoiding the problem nearly always leads
to simpler cleaner systems than trying to present transactional semantics to
the user. Your complaints all boil down to it being a bad idea to have such a
complex interface. But if your business case requires it then you're going to
have to bite the bullet and eat the added complexity and you may as well use
the best tool available to do it.


--
greg