Re: [SQL] Deadlock on transaction

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(at)archonet(dot)com>:
>
> Ezequias Rodrigues da Rocha wrote:
> > 2007/2/12, Richard Huxton <dev(at)archonet(dot)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/