1

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?

  • There's *always* risk of a serializible transaction failure. It's called somebody-tripped-over-the-power-cord. Or the-cleaner-unplugged-it-to-plug-in-the-vacuum-cleaner. Or cosmic-ray-bit-flip-itis. Or oops-that's-a-kernel-panic. Or bad-disk-block-error. An app *must* be able to cope with tx's aborting. `SERIALIZABLE` makes aborts more likely than `READ COMMITTED`, but `READ 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 04 '14 at 16:29
  • @CraigRinger Again, sorry for my lack of clarity. I realize I didn't write the question as clearly as it should to show what I really intend. I'm trying to figure out what setting should be used on a read only transaction so that it will never cause another serializable write transaction to fail. Right now, I'm going with `READ ONLY` at the default isolation level. Is that sufficient? If so, can I move the read isolation level up to `REPEATABLE READ`? –  Jul 05 '14 at 06:05

1 Answers1

0

A READ ONLY transaction cannot cause a write transaction (that does not perform DDL) to fail, unless it explicitly uses LOCK TABLE or advisory locks.

READ ONLY transactions cannot SELECT ... FOR SHARE or SELECT ... FOR UPDATE. As they can't do DML, the strongest lock they can take on a table is ACCESS SHARE, which conflicts only with the ACCESS EXCLUSIVE lock taken by DDL.

Nor can a read-only transaction cause serialization failures if the write transaction is SERIALIZABLE, because serialization failures require that both transactions perform writes. It is always possible to logically serialize a read only transaction either before or after a read/write transaction, as it is impossible for them to be mutually interdependent.

So: It should be fine to use READ COMMITTED or SERIALIZABLE transactions, with READ ONLY, so long as you do not explicitly LOCK TABLE.

You also need to make sure you don't use advisory locks that might interact between the two sets of transactions. Most likely you don't use advisory locks at all and can forget about this entirely.

Separately, though, an application must be prepared to deal with serialization failures or other transaction aborts. Any design that tries to avoid this is broken. Transactions can be aborted because of OS / host machine level issues, admin action, etc. Do not rely on transactions that "cannot fail". If you absolutely must do this, you need to use two-phase commit, where you PREPARE TRANSACTION (at which point it's guaranteed that the tx cannot fail to commit), do the other work that relies on the tx committing safely, then COMMIT PREPARED. If something goes wrong with the other work, you can ROLLBACK PREPARED. 2PC has significant overheads and is best avoided when you can, but sometimes there's just no choice.

Craig Ringer
  • 51,279
  • 3
  • 136
  • 175
  • Thank you so very much again Craig Ringer! So blocking doesn't cause a serializable write to fail? It really is amazing how well put together this db is! –  Jul 07 '14 at 01:01
  • @Gracchus Blocking on what? If you mean waiting on a lock, then no, that's fine in a `SERIALIZABLE` tx, though it isn't clear why it's relevant from the question. However, with locking (in `SERIALIZABLE` or `READ COMMITTED` transactions) it is vital that you ensure you take both table and row level locks in *the same order* for all transactions, or you risk deadlocks causing transaction aborts. See the documentation. A well designed application solves this by not caring and just re-trying the transaction. – Craig Ringer Jul 07 '14 at 01:04
  • @Gracchus Oh, and PostgreSQL has no shortage of warts and ugly bits. But yeah, overall it works rather nicely. – Craig Ringer Jul 07 '14 at 01:04