Re: [Q] Serializable - Mailing list pgsql-general

From Kevin Grittner
Subject Re: [Q] Serializable
Date
Msg-id 852195802.971418.1444128345760.JavaMail.yahoo@mail.yahoo.com
Whole thread Raw
In response to Re: [Q] Serializable  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-general
David G. Johnston <david.g.johnston@gmail.com> wrote:
> On Thu, Sep 24, 2015 at 12:15 PM, Ladislav Lenart <lenartlad@volny.cz> wrote:

>> Suppose I have two (or more) concurrent DB transactions:
>> * Each runs in SERIALIZABLE.
>> * Each updates (insert / update / delete) different rows in the
>>   same table.
>>
>> Can I get serializable failures (i.e. ERROR: could not serialize
>> access due to read/write dependencies among transactions)?
>
> ​Probably not but there seems to be insufficient information
> provided to prove this.

Right.   I don't think there's enough here to be sure whether you
would actually have a serialization anomaly without the error, but
keep in mind that there can be false positives due to locking
granularity and other technical issues.   You can minimize this by
setting max_pred_locks_per_transaction higher, making sure that
your declare transactions to be read-only if you know that they
will not be modifying data, etc.   See the bulleted list of hints at
the bottom of this section.

http://www.postgresql.org/docs/9.4/interactive/transaction-iso.html#XACT-SERIALIZABLE

> You seem to probably have the "write dependency" covered but you
> mention nothing about "read dependencies".

For examples, see this page:

https://wiki.postgresql.org/wiki/SSI

> Why not just assume it can and put code in place to handle that
> possibility - especially since you should probably be
> frameworking database access to enforce that all parts of the
> system use SERIALIZABLE?

+1

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


pgsql-general by date:

Previous
From: Scott Mead
Date:
Subject: Re: Recording exceptions within function (autonomous transactions?)
Next
From: Scott Mead
Date:
Subject: Re: How to reduce pg_dump backup time