Thread: 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@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)
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
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
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
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
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
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
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
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