Re: PREPARE TRANSACTION and webapps

On Fri, Nov 11, 2005 at 05:45:28PM +0800, Lincoln Yeoh wrote:
> At 04:11 PM 11/10/2005 -0500, Tom Lane wrote:
> >A prepared transaction eats just about the same resources (other than an
> >active connection) as a live one. In particular it still holds its
> >locks, which makes leaving it around for a long time just as evil as
> >simply sitting on it in an un-prepared state.
>
> Assuming the transactions don't explicitly do any locks (lock table, select
> for update - just selects, inserts and normal updates), would it be
> possible to have say 10000 pending prepared transactions? What would the
> main limiters be?

Every transaction takes locks, on every table it accesses. Shared lock,
but locks anyway. UPDATEs take stronger locks, so any UPDATE may cause
other queries to wait until you COMMIT or ABORT.

See also:
: The state of each prepared transaction is kept in a so called "2PC
: state file" in the pg_twophase directory. There is one state file for
: each prepared transaction, and the filename is the xid of the
: transaction.
:
: The state file is created and populated when the transaction is
: prepared, and it's used in commit/rollback to finish the transaction
: on behalf of the original backend. It's also used on database
: recovery to recover any in-memory state the transaction must have,
: like locks held.
http://users.tkk.fi/~hlinnaka/pgsql/

> It will be very nice if that sort of thing is viable. Previously if you
> want to do transactional stuff with webapps, you'd have to simulate it at
> the application layer (or leave db connections open[1]). Doing transaction
> stuff at the application level seems rather MySQL-ish (OK MySQL 3-ish ;) ).
> Having to have tables with transactionid columns, transaction table etc.

But once you've prepared a transaction, you can't reopen it, all you
can do is either commit it or abort it. I don't see how prepared
transaction relate to webapps at all.

See also the docs:
http://www.postgresql.org/docs/8.1/static/sql-prepare-transaction.html

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)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.