Thread: [Q] Serializable
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, Ladislav Lenart
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