Skip site navigation (1) Skip section navigation (2)

Re: [SQL] Deadlock on transaction

From: Richard Huxton <dev(at)archonet(dot)com>
To: Ezequias Rodrigues da Rocha <ezequias(dot)rocha(at)gmail(dot)com>
Cc: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>,pgsql-sql(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org
Subject: Re: [SQL] Deadlock on transaction
Date: 2007-02-12 19:50:49
Message-ID: 45D0C519.2080107@archonet.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-sql
Ezequias Rodrigues da Rocha wrote:
> This is delphi. I don't intent you understand but the sql actions are quite
> simple (I am reading a list of numbers).

Well, let's see - the last Pascal I did was in 1986 I think...

>         If not dm.database1.InTransaction then
>           dm.database1.StartTransaction;
> 
>         For i:= 0 to memo1.Lines.Count - 1 do

Loop through your import.

>           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

OK, you're fetching ticket IDs one at a time.

>              dm.qQ1.SQL.clear;
>              dm.qQ1.SQL.text:= Concat('select nextval(',QuotedStr('
> base.ingresso_id') , ')');
>              dm.qQ1.open;
>              IngressoID:= dm.qQ1.fieldByName('nextval').asString;

And now you've generated a new ID from a sequence.

>              // $$$$$$$$$$ 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;

Insert one row into "ingresso".

>              // ########### 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;

Insert a row into "bilhete".

> 
>         end;

I don't see anything like "dm.database1.CommitTransaction" - are you 
sure you are committing after the inserts?


One other point - if Delphi doesn't offer something like it, you might 
find it useful to write a function that makes it easier to build queries:
   my_db_func('INSERT INTO foo (id,a,b,c) VALUES (?,?,?,?)',
   'NUM,TEXT,DATE,DATE',
   variable1,variable2,variable3...);
I'd be surprised if there wasn't something like that already though.

-- 
   Richard Huxton
   Archonet Ltd

In response to

pgsql-admin by date

Next:From: Scott MarloweDate: 2007-02-12 19:56:30
Subject: Re: [SQL] Deadlock on transaction
Previous:From: Ezequias Rodrigues da RochaDate: 2007-02-12 19:27:15
Subject: Re: [ADMIN] Deadlock on transaction

pgsql-sql by date

Next:From: Scott MarloweDate: 2007-02-12 19:56:30
Subject: Re: [SQL] Deadlock on transaction
Previous:From: Ezequias Rodrigues da RochaDate: 2007-02-12 19:27:15
Subject: Re: [ADMIN] Deadlock on transaction

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group