Re: PREPARE TRANSACTION and webapps

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org
Subject: Re: PREPARE TRANSACTION and webapps
Date: 2005-11-11 13:44:24
Message-ID: 20051111134422.GF13177@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, Nov 11, 2005 at 02:22:05PM +0100, Martijn van Oosterhout wrote:
> 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.

Note also, you don't want to use prepare transactions until you know
exactly what you're doing. Take for example (in a single session):

test=# begin;
BEGIN
test=# update test set value=1 where value=1;
UPDATE 1
test=# prepare transaction 'test';
PREPARE TRANSACTION
test=# begin;
BEGIN
test=# update test set value=1 where value=1;

and you've deadlocked yourself. Until you create another connection and
commit or rollback the prepared transaction, this query will never end.
This is at the lowest isolation level. You do not want to keep
transactions open longer than absolutly necessary.

Have a nice day,
--
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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bill Bartlett 2005-11-11 13:49:54 Re: Using native win32 psql.exe using alternative cygwin - psql 8.0.0 beta 3 question
Previous Message Martijn van Oosterhout 2005-11-11 13:22:05 Re: PREPARE TRANSACTION and webapps