[Q] Serializable

On Thu, Sep 24, 2015 at 12:15 PM, Ladislav Lenart <lenartlad@volny.cz>
wrote:

Hello.

I would like to know how SERIALIZABLE behaves.

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)?

This is on 9.4.

Thank you in advance,

​Probably not but there seems to be insufficient information provided to
prove this. You seem to probably have the "write dependency" covered but
you mention nothing about "read dependencies".

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?

David J.
​

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general