Обсуждение: [GENERAL] update error with serializable
Hi,
I've two threads countinuously updataing rows in the same table.
Each one does: BEGIN, UPDATE,UPDATE,,,,COMMIT
There can't be two active transactions updating the same row (my bug apart but I don't think so).
I'm using default_transaction_isolation = 'serializable'
I get "could not serialize access due to read/write dependencies among transactions"
I din't expect to see it, hence there must be something in postgresql theory that I haven't understood well and I'd like a clarification.
Below here a log of commands issued by my threads followed by the error msg from PG server.
Thanks
Pupillo
Log:
thread 0: BEGIN
thread 1: BEGIN
thread 0: UPDATE stato SET dati=$1 WHERE id=0;
thread 0: UPDATE stato SET dati=$1 WHERE id=1;
thread 1: UPDATE stato SET dati=$1 WHERE id=10;
thread 0: UPDATE stato SET dati=$1 WHERE id=2;
thread 1: UPDATE stato SET dati=$1 WHERE id=11;
thread 1: UPDATE stato SET dati=$1 WHERE id=12;
thread 0: UPDATE stato SET dati=$1 WHERE id=3;
thread 1: UPDATE stato SET dati=$1 WHERE id=13;
thread 0: UPDATE stato SET dati=$1 WHERE id=4;
thread 1: UPDATE stato SET dati=$1 WHERE id=14;
thread 0: UPDATE stato SET dati=$1 WHERE id=5;
thread 1: UPDATE stato SET dati=$1 WHERE id=15;
thread 1: UPDATE stato SET dati=$1 WHERE id=16;
thread 0: UPDATE stato SET dati=$1 WHERE id=6;
thread 1: UPDATE stato SET dati=$1 WHERE id=17;
thread 0: UPDATE stato SET dati=$1 WHERE id=7;
thread 1: UPDATE stato SET dati=$1 WHERE id=18;
thread 1: UPDATE stato SET dati=$1 WHERE id=19;
thread 0: UPDATE stato SET dati=$1 WHERE id=8;
thread 0: UPDATE stato SET dati=$1 WHERE id=9;
thread 1: COMMIT
thread 0:UPDATE ERROR
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during conflict out checking.
HINT: The transaction might succeed if retried.
STATEMENT: UPDATE stato SET dati=$1 WHERE id=9;
On Fri, Jan 20, 2017 at 4:44 AM, Tom DalPozzo <t.dalpozzo@gmail.com> wrote: > I've two threads countinuously updataing rows in the same table. > Each one does: BEGIN, UPDATE,UPDATE,,,,COMMIT > There can't be two active transactions updating the same row (my > bug apart but I don't think so). > I'm using default_transaction_isolation = 'serializable' > I get "could not serialize access due to read/write dependencies > among transactions" > I din't expect to see it, hence there must be something in > postgresql theory that I haven't understood well and I'd like a > clarification. Most likely one or both transactions have have updated 3 or more tuples on a single page, causing the tuple locks for the transaction on that page to be combined into a single page lock for that transaction. This is intended to prevent the memory required for tracking predicate locks from growing too large. This threshold of 3 per page was entirely arbitrary and always seen as something which could and should be improved someday. That might happen for version 10 (expected to be released next year), since a patch has been submitted to make that configurable. https://www.postgresql.org/message-id/flat/d8joa0eh9yw.fsf@dalvik.ping.uio.no#d8joa0eh9yw.fsf@dalvik.ping.uio.no If you are able to build from source, you might want to test the efficacy of the patch for your situation. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 01/20/2017 10:05 AM, Kevin Grittner wrote: > On Fri, Jan 20, 2017 at 4:44 AM, Tom DalPozzo <t.dalpozzo@gmail.com> wrote: > >> I've two threads countinuously updataing rows in the same table. >> Each one does: BEGIN, UPDATE,UPDATE,,,,COMMIT >> There can't be two active transactions updating the same row (my >> bug apart but I don't think so). >> I'm using default_transaction_isolation = 'serializable' >> I get "could not serialize access due to read/write dependencies >> among transactions" >> I din't expect to see it, hence there must be something in >> postgresql theory that I haven't understood well and I'd like a >> clarification. > Most likely one or both transactions have have updated 3 or more > tuples on a single page, causing the tuple locks for the > transaction on that page to be combined into a single page lock for > that transaction. This is intended to prevent the memory required > for tracking predicate locks from growing too large. This > threshold of 3 per page was entirely arbitrary and always seen as > something which could and should be improved someday. That might > happen for version 10 (expected to be released next year), since a > patch has been submitted to make that configurable. > > https://www.postgresql.org/message-id/flat/d8joa0eh9yw.fsf@dalvik.ping.uio.no#d8joa0eh9yw.fsf@dalvik.ping.uio.no > > If you are able to build from source, you might want to test the > efficacy of the patch for your situation. > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > Configurable or dynamic? Wouldn't something related to tuples per page (and maybe fillfactor) do the trick?
On Fri, Jan 20, 2017 at 11:27 AM, Rob Sargent <robjsargent@gmail.com> wrote: > On 01/20/2017 10:05 AM, Kevin Grittner wrote: >> https://www.postgresql.org/message-id/flat/d8joa0eh9yw.fsf@dalvik.ping.uio.no#d8joa0eh9yw.fsf@dalvik.ping.uio.no > Configurable or dynamic? Wouldn't something related to tuples per page (and > maybe fillfactor) do the trick? Please keep discussion such as that on the thread for the patch. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company