I am protecting critical transactions with application mutexes and SERIALIZABLE
.
I presume that this makes these operations as acid as possible, but now I'm concerned that reads might cause failures to those write transactions because of locking.
The reads are in libpqxx prepared statement transactions. I have read the docs, wiki, and white paper, but I can't find out how to configure a read transaction so that it never risks a failure of another critical serializable write transactions.
I cannot determine if locks will cause serialization failures. Can they?
The reads do not need to be perfectly acid because the application compensates for race conditions, old data, etc. The primary concern is that the serializable critical write transactions do not fail due to other reads.
How should a read only prepared statement be configured so absolutely not to risk another serializable transaction write failure?
SERIALIZABLE
makes aborts more likely thanREAD COMMITTED
, butREAD COMMITTED
can still deadlock, run out of RAM and get killed, etc. Are you looking for two-phase commit (2PC) or XA, perhaps? – Craig Ringer Jul 4 '14 at 16:29READ ONLY
at the default isolation level. Is that sufficient? If so, can I move the read isolation level up toREPEATABLE READ
? – user32234 Jul 5 '14 at 6:05