From: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
To: Richard Huxton <dev@archonet.com>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: Re: [SQL] Deadlock on transaction
Date: Mon, 12 Feb 2007 16:20:30 -0300
Message-ID: <55c095e90702121120s5b98a2d8u4a05a31f9625ffa@mail.gmail.com> (raw)
In-Reply-To: <45D0B8A4.7000602@archonet.com>
References: <55c095e90702120943v2909044eo91445b0eda3958c7@mail.gmail.com>
<1171302488.3565.15.camel@state.g2switchworks.com>
<55c095e90702121008u6d3d27c0m16e52d63f97c3e7d@mail.gmail.com>
<45D0B565.9060402@archonet.com>
<55c095e90702121047x1c66b098xbcf0d308195ff584@mail.gmail.com>
<45D0B8A4.7000602@archonet.com>
This is delphi. I don't intent you understand but the sql actions are quite
simple (I am reading a list of numbers).
If not dm.database1.InTransaction then
dm.database1.StartTransaction;
For i:= 0 to memo1.Lines.Count - 1 do
Begin
// Catching ID
dm.qQ1.SQL.text:= Concat('select id from base.cartao where
numero = ', memo1.lines[i]);
dm.qQ1.open;
cartaoID:= dm.qQ1.fieldByName('id').asString;
// Cathing the ticket ID
dm.qQ1.SQL.clear;
dm.qQ1.SQL.text:= Concat('select nextval(',QuotedStr('
base.ingresso_id') , ')');
dm.qQ1.open;
IngressoID:= dm.qQ1.fieldByName('nextval').asString;
// $$$$$$$$$$ Recording Tickets $$$$$$$$$$
dm.Qq1.sql.text:= Concat('Insert into base.ingresso values ('
, QuotedStr(IngressoID), ',' , EstadoID, ',' , 'now()', ',' , valor, ',' ,
valor_promotor, ',' , AssentoID, ',', CaixaID, ',' , CartaoID, ',' ,
PromocaoID, ',' , SessaoID, ',' , VendedorID, ')');
// Showmessage(dm.Qq1.sql.text);
dm.Qq1.execSQL;
// ########### Recording Tickets ###########
// Pegando o Id do Bilhete no PostgreSQL
dm.qQ1.SQL.clear;
dm.qQ1.SQL.text:= Concat('select nextval(',QuotedStr('
base.bilhete_id') , ')');
dm.qQ1.open;
BilheteID:= dm.qQ1.fieldByName('nextval').asString;
dm.qQ1.SQL.clear;
dm.qQ1.SQL.add(Concat('Insert into base.bilhete (id, estado,
uso_sequencia, promocao_documento, assento_id, cartao_id, ingresso_id,
promocao_id, sessao_id, vendedor_venda_id )'));
dm.qQ1.SQL.add(Concat('values(', BilheteID, ',' ,
QuotedStr(EstadoID), ',' , '0,0', ',' , QuotedStr(AssentoID), ',' ,
QuotedStr(CartaoID), ',' , QuotedStr(IngressoID) , ',' ,
QuotedStr(PromocaoID), ',' , QuotedStr(SessaoID) , ',' ,
QuotedStr(VendedorID), ')' ) );
dm.qQ1.execSQL;
end;
2007/2/12, Richard Huxton <dev@archonet.com>:
>
> Ezequias Rodrigues da Rocha wrote:
> > 2007/2/12, Richard Huxton <dev@archonet.com>:
> >>
> >> Ezequias Rodrigues da Rocha wrote:
> >> > I mean really deadlock. Other transactions can't access the database
> >> until
> >> > the main transaction is complete. A question:
> >> >
> >> > PostgreSQL doesn't permit multiple transactions concurrently ?
> >>
> >> PG has quite good concurrency behaviour. And "can't access the
> database"
> >> isn't anything to do with locking - clients should still be able to log
> >> in.
> >>
> >> We'll need more details:
> >> 1. How do you know this is a deadlock? You haven't shown us what's in
> >> pg_locks, but I assume you've identified the problem there.
> >
> >
> > I knew becouse my Delphi application reports it to me. If there is
> anything
> > in pg_log I could see it to you.
>
> You still aren't showing us the deadlock in pg_locks. How does your
> Delphi application decide there is a deadlock? I can see how it might
> know a timeout has occurred, but not how it determines what any other
> connections to the database might be doing. You do know the difference
> between just waiting for locks and having a deadlock?
>
> http://en.wikipedia.org/wiki/Deadlock
>
> > 2. What data are you loading, to what tables?
> >
> > Simple data but allways with the same username (does it make any
> difference
> > ?)
> >
> > 3. Are there any foreign-keys or triggers we would need to know about?
> >
> > No.
>
> Then you don't have a deadlock. Without multiple processes trying to
> take the same locks in different orders you don't get deadlock.
>
> > Ps: When I do not use transactions the connections does not lock to
> other
> > users.
>
> You are always using transactions. There is no out-of-transaction way to
> execute a statement in PostgreSQL. You can't lock either a connection or
> users, just rows.
>
> OK - I think you'd better give a full example. Can you show us:
> 1. What query or queries you execute to load the data?
> 2. What query or queries you think are causing "deadlock"?
> 3. The order they execute in and what error you get.
>
> --
> Richard Huxton
> Archonet Ltd
>
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ezequias.rocha@gmail.com, dev@archonet.com, smarlowe@g2switchworks.com, pgsql-admin@postgresql.org
Subject: Re: [SQL] Deadlock on transaction
In-Reply-To: <55c095e90702121120s5b98a2d8u4a05a31f9625ffa@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox