Is this a commit problem?

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(at)osdl(dot)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)