3

I have seen another database system that offers to suspend transaction. The current transaction is kept intact but put on hold while your code is allowed to work with the database to effect immediate permanent changes to rows. Then you can resume transaction, continuing where you left off with the same locks and other transaction protections in place as if you'd never interrupted it.

For example, say an customer is placing an order, in a transaction. During that transaction, the customer notices their phone number needs to be updated, so we change that data. Next, customer decides to cancel the not-yet-completed order. A rollback of the order has the unintended consequence of also undoing the phone number change. So it would be nice if we could:

  1. Suspend the transaction for the order.
  2. Update the phone number, committed to the database.
  3. Resume the transaction for the order.

Is there some way to suspend a transaction in Postgres? In JDBC?

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154

3 Answers3

1

If a transaction cannot continue, it must roll back.

If your transaction has a point at which you don't know how to carry on, then your transaction logic is flawed, you need to reorganize it - either split into multiple transactions (or sub-transactions, aka save points), or take out the parts that do not belong to the transaction logic.

Is there some way to suspend a transaction in Postgres?

No, no such thing. And the data integrity principle is unconditional as to time.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
1

No.

The closest things are

  • prepared transactions: this allows (with some conditions) for a transaction to be saved, and then later rolled back or committed.

  • savepoints: this allows for "nested transactions", where portions of transactions can be rolled back .

Neither of these fit exactly what you are looking for. It seems that our example has two operations that do not need to be part of the same transaction at all, since the phone number update appears to be unrelated to the success of the order. (Also, a long-running transaction is a bad idea....your order should probably be a state machine implemented without long-running transaction.)

Paul Draper
  • 78,542
  • 46
  • 206
  • 285
0

Workaround – open second connection

In JDBC, you could just open a second connection to the database.

Do your separate work on that second connection and close. The first connection is still open and remains in its same state. Any active transaction in that first connection remains.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • This will often run into a dead-lock, when the second transaction needs to do something that's locked by the first transaction, thus unable to proceed. It only won't happen when there is no dependency between those two transactions, but that means your first transaction is wrong, as it tries to do something it shouldn't be doing in the first place. Workarounds on design problems of the database are usually a bad idea. – vitaly-t Jun 13 '17 at 06:30
  • @vitaly-t I added an example scenario to the Question. Start a customer order in a txn, as a side note we decide to update phone number, then customer cancels new-but-incomplete order. When we rollback the order we unintentionally also rollback the phone number. Is that a design problem as you mentioned? Seems like a legitimate problem & solution to me, but I'm open to hearing another position. – Basil Bourque Jun 13 '17 at 06:44
  • 1
    Your example confuses a business transaction with a database transaction. Those are two completely different things. – vitaly-t Jun 13 '17 at 07:00
  • @vitaly-t unfortunately some apps confuse those too and keep transaction open while user is clicking around in a GUI interface. The correct way is to fix the GUI interface. If that cannot be done, the GUI interface should lock every row it touches so that everybody else must wait. Otherwise, some work will be lost because some transaction will fail eventually. – Mikko Rantalainen Mar 20 '20 at 14:21