CODEWITHSUNDEEP
postgresqltransactions
user13
user13

Reputation: 391

What is the meaning of the rollback in PostgreSql and when to use it?

What is the meaning of the rollback in PostgreSql and when to use it?

As far as I understand if one is making some transaction then via rollback he/she can be assured that this transaction will rollback (will not happen).
Description from the official documentation: "Description. ROLLBACK rolls back the current transaction and causes all the updates made by the transaction to be discarded".

Now I don't understand why somebody will want to write a transaction and then implicitly insist on its termination?
It seems to me, that nobody should use the rollback, and instead they should stick to the commit because commit lets the transaction happen if it is correct and terminate it otherwise.

Upvotes: 1

Views: 6242

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246393

A transaction can consist of several database statements, and there may be application code interleaved.

Imagine a simple money transfer:

START TRANSACTION;
SELECT amount FROM account WHERE account_nr = 432 FOR UPDATE;
/* do some calculation in the application */
UPDATE account SET amount = 543.23 WHERE account_nr = 432;
SELECT amount FROM account WHERE account_nr = 123 FOR UPDATE;
/* do another calculation in the application */
UPDATE account SET amount = 1903.4 WHERE account_nr = 123;
COMMIT;

Now at any point you could encounter a problem, for example:

  • The second SELECT statement may return no result, because there is no such account.
  • The second UPDATE could fail, because it violates a database constraint.
  • The second calculation in the application may cause an error condition.

If something like that happens, you need to abort the transaction and undo its work. For example, you cannot allow the change to account 432 to become visible.

The proper way to do that in a relational database is to issue a ROLLBACK, then all that will happen automatically.

Transactions are a service for the user to make it easy to write reliable and robust code.

Upvotes: 6

Related Questions