0

For my implementation, a particular write must be done in bulk and without the chance of another interfering.

I have been told that two competing transactions in this way will lead to the first one blocking the second, and the second may or may not complete after the first has.

Please post the documentation that confirms this. Also, what exactly happens to the second transaction if the first is blocking? Will it be queued, fail, or some combination?

If this cannot be confirmed, should the transaction isolation level for this transaction be set to SERIALIZABLE? If so, how can that be done with libpqxx prepared statements?

If the transactions are serialized, will the second transaction fail or be queued until the first has completed?

If either fail, how can this be detected with libpqxx?

Community
  • 1
  • 1

1 Answers1

2

The only way to conclusively prevent concurrency effects is to LOCK TABLE ... IN ACCESS EXCLUSIVE MODE each table you wish to modify.

This means you're really only doing one thing at a time. It also leads to fun problems with deadlocks if you don't always acquire your locks in the same order.

So usually, what you need to do is figure out what exactly the operations you wish to do are, and how they interact. Determine what concurrency effects you can tolerate, and how to prevent those you cannot.

This question as it stands is just too broad to usefully answer.

Options include:

  • Exclusively locking tables. (This is the only way to do a multi-row upsert without concurrency problems in PostgreSQL right now). Beware of lock upgrade and lock order related deadlocks.

  • appropriate use of SERIALIZABLE isolation - but remember, you have to be able to keep a record of what you did during a transaction and retry it if the tx aborts.

  • Careful row-level locking - SELECT ... FOR UPDATE, SELECT ... FOR SHARE.

  • "Optimistic locking" / optimistic concurrency control, where appropriate

  • Writing your queries in ways that make them more friendly toward concurrent operation. For example, replacing read-modify-write cycles with in-place updates.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • I cannot clearly understand what you mean by "interefere with", so it's hard to say exactly what all that is about. You'd need to be a bit more specific. From your description in the comments, I'd say it's a case of *You're Doing It Wrong™*, i.e. "If I don't use sequences, these bad things might happen, what should I do?". *Use sequences*. – Craig Ringer Apr 15 '14 at 13:47
  • 1
    Since I cannot guess what exactly *you mean*, I cannot conclusively say whether your interpretation of what Daniel says is the same as mine, and whether he's right. Daniel knows his stuff and is rarely wrong, but the whole discussion is confusing enough that it's hard to say what it *means*. Look, you're asking for a *specific answer* on a *broad, general, hand-waving question*. That's not how it works. Give me a specific problem, I'll give you a specific answer. Your description of the workload you expect is anything but specific. – Craig Ringer Apr 15 '14 at 14:23