Обсуждение: Deadlock on transaction

Hi list,

I am curious becouse I am seen my postgresql locking the transactions when i load a huge (in some case not to huge) numbers of records 70,000 records.

Could someone help me with it ?

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  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/

On Mon, 2007-02-12 at 11:43, Ezequias Rodrigues da Rocha wrote:
> Hi list,
>
> I am curious becouse I am seen my postgresql locking the transactions
> when i load a huge (in some case not to huge) numbers of records
> 70,000 records.
>
> Could someone help me with it ?

Are sure that what you're speaking of is a deadlock?  Or is it just a
stalled transaction while one one transaction waits for another to
complete?

A true deadlock means that two transactions are stalled waiting for the
other to complete, and neither ever can complete, and they are
DEADlocked.

If you've just got a problem where an update is stalling all the other
transactions, then give us a test case / example code of what's
happening.

If you've got a real deadlock that postgresql isn't catching, that's a
much more serious problem.

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 ?

Ezequias




--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  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/

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.

2. What data are you loading, to what tables?

3. Are there any foreign-keys or triggers we would need to know about?

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

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

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;



--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  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/

On Mon, 2007-02-12 at 12:08, 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 ?

Again, that's not a deadlock.  A deadlock would mean that the
transaction causing the lock never returned, because it wound up waiting
for another transaction that was in turn waiting on it.  i.e.

begin transaction a
begin trasnaction b
a does something that waits on b
b does something that waits on a
neither transaction can complete.

What you have is a blocking transaction.

In postgresql, very few transactions tend to block other transactions.

Have you got a test case that demonstrates your problem with blocking?

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

On Mon, 2007-02-12 at 13:20, 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).

Hmmm.  I'm not seeing anything in your example that should be blocking
other transactions.  Is there anything that Delphi is doing here that we
don't see from your code?  I.e. does to toss a "for update" on the ends
of selects, or "lock table" in there we don't see?

Maybe you could turn on logging of ALL queries in the database and see
what's really going on there, cause I'm not seeing anything in your code
to point to blocking.