Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] update error with serializable

[GENERAL] update error with serializable

From
Tom DalPozzo
Date:
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;





Re: [GENERAL] update error with serializable

From
Kevin Grittner
Date:
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

Re: [GENERAL] update error with serializable

From
Rob Sargent
Date:

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?

Re: [GENERAL] update error with serializable

From
Kevin Grittner
Date:
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