Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id DD90C9FB4EF for ; Mon, 12 Feb 2007 15:20:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 68225-07 for ; Mon, 12 Feb 2007 15:20:30 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 7257C9FB60D for ; Mon, 12 Feb 2007 15:20:31 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so1351004ugf for ; Mon, 12 Feb 2007 11:20:30 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=VvNnrHbPduIDIJY8EFf36TrLDy4YU/ovHXCpohZ36pIkmL6ZqLZE+SKp626vhsCRaik1LrP8HN2duHcyck+AfDxfQ1Qm+HU+8VxhY96qg+mSyHjbFmKz76u1Ae6Wce5q9QbhGbKTHAsfVDVhSOaR9eqp6aRKeqDuEq7l/uKyVp0= Received: by 10.66.250.17 with SMTP id x17mr9466455ugh.1171308030249; Mon, 12 Feb 2007 11:20:30 -0800 (PST) Received: by 10.66.217.13 with HTTP; Mon, 12 Feb 2007 11:20:30 -0800 (PST) Message-ID: <55c095e90702121120s5b98a2d8u4a05a31f9625ffa@mail.gmail.com> Date: Mon, 12 Feb 2007 16:20:30 -0300 From: "Ezequias Rodrigues da Rocha" To: "Richard Huxton" Subject: Re: [SQL] Deadlock on transaction Cc: "Scott Marlowe" , pgsql-sql@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: <45D0B8A4.7000602@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_80705_22554765.1171308030200" 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> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.524 tagged_above=0 required=5 tests=AWL, BAYES_50, HTML_40_50, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200702/166 X-Sequence-Number: 24631 ------=_Part_80705_22554765.1171308030200 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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:=3D 0 to memo1.Lines.Count - 1 do Begin // Catching ID dm.qQ1.SQL.text:=3D Concat('select id from base.cartao where numero =3D ', memo1.lines[i]); dm.qQ1.open; cartaoID:=3D dm.qQ1.fieldByName('id').asString; // Cathing the ticket ID dm.qQ1.SQL.clear; dm.qQ1.SQL.text:=3D Concat('select nextval(',QuotedStr(' base.ingresso_id') , ')'); dm.qQ1.open; IngressoID:=3D dm.qQ1.fieldByName('nextval').asString; // $$$$$$$$$$ Recording Tickets $$$$$$$$$$ dm.Qq1.sql.text:=3D 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:=3D Concat('select nextval(',QuotedStr(' base.bilhete_id') , ')'); dm.qQ1.open; BilheteID:=3D 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 : > > Ezequias Rodrigues da Rocha wrote: > > 2007/2/12, Richard Huxton : > >> > >> 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 lo= g > >> 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 > --=20 =3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D= -=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D= -=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- A pior das democracias ainda =E9 melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ------=_Part_80705_22554765.1171308030200 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline This is delphi. I don't intent you understand but the sql actions are q= uite simple (I am reading a list of numbers).

 = ;        If not dm.database1.InTransacti= on then
           dm.= database1.StartTransaction ;

         For i:=3D 0 to me= mo1.Lines.Count - 1 do
        &= nbsp;  Begin
         =     // Catching ID
      &n= bsp;       dm.qQ1.SQL.text:=3D Concat('se= lect id from base.cartao where numero =3D ', memo1.lines[i]);
            &nb= sp; dm.qQ1.open;
         &= nbsp;    cartaoID:=3D dm.qQ1.fieldByName('id').asStr= ing;
           &= nbsp; // Cathing the ticket ID
       = ;       dm.qQ1.SQL.clear;
  &nbs= p;           dm.qQ1.SQL.t= ext:=3D Concat('select nextval(',QuotedStr(' base.ingresso_id') , ')');
     &nb= sp;        dm.qQ1.open;
  &= nbsp;           IngressoI= D:=3D dm.qQ1.fieldByName('nextval').asString;

  &n= bsp;           // $$$$$$$= $$$ Recording Tickets $$$$$$$$$$
      &nb= sp;      =20 dm.Qq1.sql.text:=3D Concat('Insert into base.ingresso values (' , Q= uotedStr(IngressoID), ',' , EstadoID, ',' , 'now()'= , ',' , valor, ',' , valor_promotor, ',' , AssentoI= D, ',', CaixaID, ',' , CartaoID, ',' , PromocaoID, = ',' , SessaoID, ',' , VendedorID, ')');
    //        &n= bsp; Showmessage(dm.Qq1.sql.text);
      &= nbsp;       dm.Qq1.execSQL;

 &nbs= p;            // ###= ######## Recording Tickets ###########

     = ;         // Pegando o Id do Bilhet= e no PostgreSQL
         &n= bsp;     =20 dm.qQ1.SQL.clear;
         =        dm.qQ1.SQL.text:=3D Concat('select= nextval(',QuotedStr('base.bilhete_id') , ')');
&nbs= p;            &= nbsp;  dm.qQ1.open;
        = ;        BilheteID:=3D dm.qQ1.fieldByNam= e ('nextval').asString;

      &n= bsp;         dm.qQ1.SQL.clear;
&= nbsp;           &nbs= p;   dm.qQ1.SQL.add(Concat('Insert into base.bilhete (id, est= ado, uso_sequencia, promocao_documento, assento_id, cartao_id, ingresso_id,= promocao_id, sessao_id, vendedor_venda_id )'));
            = ;    dm.qQ1.SQL.add(Concat('values(', BilheteID, = 9;,' , QuotedStr(EstadoID), ',' , '0,0', ',' , = QuotedStr(AssentoID), ',' , QuotedStr(CartaoID), ',' , Quot= edStr(IngressoID) , ',' , QuotedStr(PromocaoID), ',' , Quot= edStr(SessaoID) , ',' , QuotedStr(VendedorID), ')' ) );

           &= nbsp;    dm.qQ1.execSQL;

    &nbs= p;    end;
2007/2= /12, Richard Huxton <dev@archonet.co= m>:
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 que= stion:
>> >
>> > PostgreSQL doesn't permit mult= iple transactions concurrently ?
>>
>> PG has quite good = concurrency behaviour. And "can't access the database"
>> isn't anything to do with locking - clients should still b= e able to log
>> in.
>>
>> We'll need more d= etails:
>> 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 t= o 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 mightknow a timeout has occurred, but not how it determines what any other
connections to the database might be doing. You do know the difference<= br>between just waiting for locks and having a deadlock?

http://en.wikipedia.org/wiki/Deadloc= k

> 2. What data are you loading, to what tables?
>
&= gt; Simple data but allways with the same username (does it make any differ= ence
> ?)
>
> 3. Are there any foreign-keys or triggers w= e would need to know about?
>
> No.

Then you don't have a deadlock. Without mul= tiple processes trying to
take the same locks in different orders you do= n't get deadlock.

> Ps: When I do not use transactions the co= nnections does not lock to other
> users.

You are always using transactions. There is no out-o= f-transaction way to
execute a statement in PostgreSQL. You can't lo= ck 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 the= y execute in and what error you get.

--
   Richard Huxt= on
   Archonet Ltd



-= -
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-= =3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D
 &n= bsp;            = ;            &n= bsp;       Atenciosamente (Sincerely)            &n= bsp;           Ezequ= ias Rodrigues da Rocha
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-= =3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D= -
A pior das democracias ainda =E9 melhor do que a melhor das ditaduras<= br>The worst of democracies is still better than the better of dictatorship= s
http://ezequiasrocha.blo= gspot.com/ ------=_Part_80705_22554765.1171308030200--