Re: [Q] Serializable

David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Thu, Sep 24, 2015 at 12:15 PM, Ladislav Lenart <lenartlad(at)volny(dot)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