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

At 06:04 PM 11/16/2005 +0100, Martijn van Oosterhout wrote:

>On Thu, Nov 17, 2005 at 12:29:25AM +0800, Lincoln Yeoh wrote:
> > 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.

Just curious: how much memory do locks/transactions occupy as a rough
percentage of backend memory usage? Assume a "typical" active backend
(5MB?). If it's 50% then sure forget it. But if it's 5% or even 1%...

>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.

I believe in postgresql there's "select for update ...  nowait" or
something like that, and transactions can have savepoints.

Also, if that sort of thing is a problem you could very easily link a user
session to pending uncommitted database transactions. Once the user session
times out you rollback all linked transactions.

I'm sure the solutions are decades old. After all in the dumb terminal
days, couldn't transactions be held open for quite a long time too?

>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.

What I used to do was make copies in event of a "collision" - but it starts
to get closer to a "version control and merging" problem, and less of a
transaction problem ;).

If so many people have no problems with doing transactions at the
application/middleware level, no wonder MySQL 3 was good enough for them -
they had little need for MVCC and ACID databases, since they were already
doing all that at a higher layer.

For what it is worth, I've done that sort of stuff at the application level
too. "shopping cart" tables, tables with "transaction_id" columns, a
transaction table, etc etc. I dunno about you all, but having to do that
feels a bit like using MySQL 4 - some tables "support transactions" and
some don't.

Oh well, maybe it's just not such a good idea after all. Just thought it
might be feasible and useful.

Regards,
Link.