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

On Thu, Nov 17, 2005 at 12:29:25AM +0800, Lincoln Yeoh wrote:
> MVCC-style transactions that are not limited by/to database connections.
>
> This could be useful if you want to have X pending database transactions
> and Y max concurrent database connections, where X is significantly greater
> than Y (magnitudes higher?).
>
> My assumption is that pending transactions (e.g. locks and other metainfo)
> will take much less memory than database backends.

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

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.

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.

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?

The example given where you have an ordering system for a limited
number of widgets where the ordering process might take some time to
enter is silly. The discussion about concurrency control is bypassing
the fact that what you really want is a queue. You know, "there are 3
widgets available but 5 people started their orders before you. If they
cancel you get yours". Much better than waiting an hour for everyone
else to finish.

> It'll be nice (but it might be difficult) to have an implementation that
> allowed migration of transactions to a different node in a cluster - so
> that one could bring down a database node server in the middle of a
> transactions without affecting database users/applications severely. A
> suitable protocol might allow a database client to automatically save its
> transaction, and then resume it on another node, without the database
> user/app noticing much (not sure if this is a good idea though).

This is a completely different kettle of fish. I'm not sure what it
would take to serialise a transaction, maybe most of that is done
already.

> My assumption is managing pending transactions would be easier than
> reimplementing MVCC and the other stuff. Especially if only controlled
> types of transactions are saved and resumed - one scenario might even put
> such transactions in a different database so as not to affect other
> transactions. But I could be wrong :).

This is silly. Any transaction that updates a row will block any other
transaction using that row until he commits or aborts. Putting it on
another server doesn't change the fact that the row is locked *for
everybody*.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.