I've been observing a interesting behavior with our DBT-2 workload.  It
appears that a commit to a transaction is taking some time to occur.
I'll try to briefly describe what we're seeing.  The transaction goes
something like this:

1. BEGIN

2. SELECT d_next_o_id
   INTO current_o_id
   FROM district
   WHERE d_w_id = 1
   AND d_id = 8

3. UPDATE district
   SET d_next_o_id = d_next_o_id + 1
   WHERE d_w_id = 1
   AND d_id = 8

4. INSERT INTO new_order (no_o_id, no_w_id, no_d_id)
   VALUES (current_o_id, 1, 8)

5. COMMIT/ROLLBACK


The transaction is implemented as a C stored function and is called
through the liqpq interface with an isolation level of read committed
and autocommit set to false.

The first pass through this transaction appears to execute correctly,
and a COMMIT is executed in step 5. The second pass through reads an old
d_next_i_id in step 2. For example, if d_next_o_id is 300 in the first
pass, it should also be updated to 301.  The next time this transaction
is called, it is still 300.  Also, on the second instance the
transaction is called, step 4 causes the libpq library to throw a
'Cannot insert a duplicate key into unique index pk_new_order' error so
my application executes a ROLLBACK, since the no_o_id, no_w_id, no_d_id
columns are the primary key in the new_order table.  I can verify that
the first transaction is eventually committed to the database by
examining the data in the database after the test has run.

This only occurs about 1% of the time.  I'm not sure how else to analyze
the situation.  Let me know if I can clarify anything or provide any
more information.

Thanks!

-- 
Mark Wong - - markw@osdl.org
Open Source Development Lab Inc - A non-profit corporation
12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005
(503) 626-2455 x 32 (office)
(503) 626-2436      (fax)

Wed, 24 Sep 2003 14:22:15 -0700 (PDT)
markw@osdl.org
Is this a commit problem?
markw@osdl.org writes:

> This only occurs about 1% of the time.  I'm not sure how else to analyze
> the situation.  Let me know if I can clarify anything or provide any
> more information.

Are you running more than one of these transactions in parallel,
overlapping?

-- 
Peter Eisentraut   peter_e@gmx.net


Thu, 25 Sep 2003 00:04:55 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Is this a commit problem?
On 25 Sep, Peter Eisentraut wrote:
> markw@osdl.org writes:
> 
>> This only occurs about 1% of the time.  I'm not sure how else to analyze
>> the situation.  Let me know if I can clarify anything or provide any
>> more information.
> 
> Are you running more than one of these transactions in parallel,
> overlapping?
> 

Yes, but each transaction has a unique d_w_id, d_id pair, so there is
never any 2 transactions touching the same row in the district table.

Mark

Wed, 24 Sep 2003 15:08:34 -0700 (PDT)
markw@osdl.org
Re: Is this a commit problem?
markw@osdl.org wrote:

> 2. SELECT d_next_o_id
>    INTO current_o_id
>    FROM district
>    WHERE d_w_id = 1
>    AND d_id = 8
> 
> 3. UPDATE district
>    SET d_next_o_id = d_next_o_id + 1
>    WHERE d_w_id = 1
>    AND d_id = 8

I don't  know exactly what you are trying to do
but usualy in cases like this, in order to avoid
further problem in the commit phase you ought to
do a SELECT .... FOR UPDATE  instead.


Regards
Gaeatano Mendola


Thu, 25 Sep 2003 00:46:56 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Is this a commit problem?
markw@osdl.org writes:
> I've been observing a interesting behavior with our DBT-2 workload.

AFAICS the only possible explanation for this is that you aren't
actually waiting for the first transaction to commit before you start
the second one.  What is the client doing exactly to issue these
queries?

			regards, tom lane

Thu, 25 Sep 2003 10:28:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Is this a commit problem? 
On 25 Sep, Gaetano Mendola wrote:
> markw@osdl.org wrote:
> 
>> 2. SELECT d_next_o_id
>>    INTO current_o_id
>>    FROM district
>>    WHERE d_w_id = 1
>>    AND d_id = 8
>> 
>> 3. UPDATE district
>>    SET d_next_o_id = d_next_o_id + 1
>>    WHERE d_w_id = 1
>>    AND d_id = 8
> 
> I don't  know exactly what you are trying to do
> but usualy in cases like this, in order to avoid
> further problem in the commit phase you ought to
> do a SELECT .... FOR UPDATE  instead.

That appears to have done the trick.  Thanks!

Mark

Thu, 25 Sep 2003 10:00:02 -0700 (PDT)
markw@osdl.org
Re: Is this a commit problem?
On 25 Sep, Tom Lane wrote:
> markw@osdl.org writes:
>> I've been observing a interesting behavior with our DBT-2 workload.
> 
> AFAICS the only possible explanation for this is that you aren't
> actually waiting for the first transaction to commit before you start
> the second one.  What is the client doing exactly to issue these
> queries?
> 
> 			regards, tom lane

Basically 4 steps:

PQexec(dbc->conn, "BEGIN");
PQexec(dbc->conn, "DECLARE mycursor CURSOR FOR SELECT new_order(...)");
PQexec(dbc->conn, "FETCH ALL IN mycursor");
PQexec(dbc->conn, "COMMIT");


I take it PQexec() should wait until the COMMIT finishes?

Mark

Thu, 25 Sep 2003 10:04:27 -0700 (PDT)
markw@osdl.org
Re: Is this a commit problem? 
markw@osdl.org writes:
> I take it PQexec() should wait until the COMMIT finishes?

Yeah, it does.  Where is the next iteration of the transaction coming
from?

Another thought occurred to me --- you said you have many parallel
instances of this transaction, but they affect different rows because
they have different keys.  Is it possible there's a logic bug that
occasionally allows parallel transactions to be fired with the same key?

			regards, tom lane

Thu, 25 Sep 2003 13:07:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Is this a commit problem? 
On 25 Sep, Tom Lane wrote:
> markw@osdl.org writes:
>> I take it PQexec() should wait until the COMMIT finishes?
> 
> Yeah, it does.  Where is the next iteration of the transaction coming
> from?
> 
> Another thought occurred to me --- you said you have many parallel
> instances of this transaction, but they affect different rows because
> they have different keys.  Is it possible there's a logic bug that
> occasionally allows parallel transactions to be fired with the same key?

Yeah, that thought has crossed my mind.  I did catch an addition error
in my logic that contributed to additional rollbacks. ;)  But I did try
to be diligent and output everything that was going on in the
transaction to make sure there weren't any parallel transactions with
the same key.  I feel confident that the logic is correct.

Mark

Thu, 25 Sep 2003 11:44:19 -0700 (PDT)
markw@osdl.org
Re: Is this a commit problem? 