Rollback a Committed Transaction

Can I rollback a transaction I've already committed? (data loss)

No, you can't undo, rollback or reverse a commit.

STOP THE DATABASE!

(Note: if you deleted the data directory off the filesystem, do NOT stop the database. The following advice applies to an accidental commit of a DELETE or similar, not an rm -rf /data/directory scenario).

If this data was important, STOP YOUR DATABASE NOW and do not restart it. Use pg_ctl stop -m immediate so that no checkpoint is run on shutdown.

You cannot roll back a transaction once it has commited. You will need to restore the data from backups, or use point-in-time recovery, which must have been set up before the accident happened.

If you didn't have any PITR / WAL archiving set up and don't have backups, you're in real trouble.

Urgent mitigation

Once your database is stopped, you should make a file system level copy of the whole data directory - the folder that contains base, pg_clog, etc. Copy all of it to a new location. Do not do anything to the copy in the new location, it is your only hope of recovering your data if you do not have backups. Make another copy on some removable storage if you can, and then unplug that storage from the computer. Remember, you need absolutely every part of the data directory, including pg_xlog etc. No part is unimportant.

Exactly how to make the copy depends on which operating system you're running. Where the data dir is depends on which OS you're running and how you installed PostgreSQL.

Ways some data could've survived

If you stop your DB quickly enough you might have a hope of recovering some data from the tables. That's because PostgreSQL uses multi-version concurrency control (MVCC) to manage concurrent access to its storage. Sometimes it will write new versions of the rows you update to the table, leaving the old ones in place but marked as "deleted". After a while autovaccum comes along and marks the rows as free space, so they can be overwritten by a later INSERT or UPDATE. Thus, the old versions of the UPDATEd rows might still be lying around, present but inaccessible.

Additionally, Pg writes in two phases. First data is written to the write-ahead log (WAL). Only once it's been written to the WAL and hit disk, it's then copied to the "heap" (the main tables), possibly overwriting old data that was there. The WAL content is copied to the main heap by the bgwriter and by periodic checkpoints. By default checkpoints happen every 5 minutes. If you manage to stop the database before a checkpoint has happened and stopped it by hard-killing it, pulling the plug on the machine, or using pg_ctl in immediate mode you might've captured the data from before the checkpoint happened, so your old data is more likely to still be in the heap.

Now that you have made a complete file-system-level copy of the data dir you can start your database back up if you really need to; the data will still be gone, but you've done what you can to give yourself some hope of maybe recovering it. Given the choice I'd probably keep the DB shut down just to be safe.

Recovery

You may now need to hire an expert in PostgreSQL's innards to assist you in a data recovery attempt. Be prepared to pay a professional for their time, possibly quite a bit of time.

I posted about this on the Pg mailing list, and Виктор Егоров linked to depesz's post on pg_dirtyread, which looks like just what you want, though it doesn't recover TOASTed data so it's of limited utility. Give it a try, if you're lucky it might work.

See: pg_dirtyread on GitHub.

I've removed what I'd written in this section as it's obsoleted by that tool.

See also PostgreSQL row storage fundamentals

Prevention

See my blog entry Preventing PostgreSQL database corruption.

On a semi-related side-note, if you were using two phase commit you could ROLLBACK PREPARED for a transction that was prepared for commit but not fully commited. That's about the closest you get to rolling back an already-committed transaction, and does not apply to your situation.

Rollback a committed transaction

You cannot rollback what has already been commited. What you can do, in this particular situation, as one of the quickest options, is to issue a flashback query against a table you've deleted row(s) from and insert them back. Here is a simple example:

Note: Success of this operation depends on the value(default 900 seconds) of undo_retention parameter - period of time(can be reduced automatically) during which undo information is retained in undo tablespace.

/* our test table */
create table test_tb(
   col number
);
/* populate test table with some sample data */
insert into test_tb(col)
   select level
     from dual
  connect by level <= 2;

select * from test_tb;

COL
----------
         1
         2
/* delete everything from the test table */    
delete from test_tb;

select * from test_tb;

no rows selected

Insert deleted rows back:

/* flashback query to see contents of the test table 
  as of specific point in time in the past */ 
select *                                   /* specify past time */
  from test_tb as of timestamp timestamp '2013-11-08 10:54:00'

COL
----------
         1
         2
/* insert deleted rows */
insert into test_tb
   select *                                 /* specify past time */  
    from test_tb as of timestamp timestamp '2013-11-08 10:54:00'
   minus
   select *
     from test_tb

 select *
   from test_tb;

  COL
  ----------
          1
          2

What happens when you commit a transaction after rollback?

If you rollback the transaction, all changes made in that transactions are just... rolled back, cancelled. So your commit in finally block won't do anything, at least when you have no other transactions waiting.

I wouldn't place commit method in finally block, I'd commit the transaction at the end of try block and rollback in catch block.

Can I roll back a JTA transcation after I commit it?

I think that the answer is that you cannot do anything like this using JTA, or other RDBMs.

Transactions are either committed, or they are rolled back. Once successfully committed they cannot be rolled back.

The only possible "out" might be to try and use nested transactions, and rollback the outer transaction. But that probably isn't going to work:

Not all JTA implementations support nested transactions.
Even if they did, there is no guarantee that the outer transaction will successfully commit. And that could leave you with the "other" DB committed and the JTA transaction rolled back.

It sound like you are going to have to rethink your persistence APIs.

Entity Framework Core - transaction cannot be roll back after commit

When using Entity Framework, explicit transactions are only required when you want to link the success or failure of operations against the DbContext with other operations outside of the scope of the DbContext. All operations within a DbContext prior to SaveChanges are already grouped within a transaction. So for instance saving entities across two or more tables within a DbContext do not require setting up an explicit transaction, they will both be committed or rolled back together if EF cannot save one or the other.

When using an explicit transaction, the Commit() call should be the last operation for what forms essentially a unit of work. It will be the last operation to determine whether everything in the transaction scope is successful or not. So as a general rule, all operations, whether Database-based, file based, or such should register with and listen to the success or failure of the transaction.

An example of using a transaction: Say we have a system that accesses two databases via two separate DbContexts. One is an order system that tracks orders and has a record for a Customer and one is a CRM that tracks customer information. When we accept a new order from a new customer we check the CRM system for a customer and create a customer record in both systems if it is someone new.

using (var orderContext = new OrderDbContext())
{
    var transaction = orderContext.Database.BeginTransaction();

    try
    {
        var order = new Order
        {
            // TODO: Populate order details..
        }

        if(customerDetails == null && registerNewCustomer) // Where customerDetails = details loaded if an existing customer logged in and authenticated...
        {
            using(var crmContext = new CrmDbContext())
            {
                crmContext.Database.UseTransaction(transaction);
                var customer = new Customer 
                {
                    UserName = orderDetails.CustomerEmailAddress,
                    FirstName = orderDetails.CustomerFirstName,
                    LastName = orderDetails.CustomerLastName,
                    Address = orderDetails.BillingAddress
                };
                crmContext.Customers.Add(customer);
                crmContext.SaveChanges();
                var orderCustomer = new Orders.Customer
                {
                    CustomerId = customer.CustomerId,
                    FirstName = customer.FirstName,
                    LastName = customer.LastName
                }
                orderContext.Customers.Add(orderCustomer);
            }
         }
         
         order.CustomerId = crmContext.Customers
             .Select(c => c.CustomerId)
             .Single(c => c.UserName == customerDetails.UserName);
     
         orderContext.Orders.Add(order);
         orderContext.SaveChanges();

         transaction.Commit();
     }
     catch(Exception ex)
     {
         // TODO: Log exception....         
         transaction.Rollback();
     }
}

The order DB customer is just a thin wrapper of the CRM customer where we would go for all of the customer details. The CRM customer manages the Customer IDs which would correlate to an Order Customer record. This is by no means a production code type example, but rather just to outline how a Transaction might coordinate multiple operations when used.

In this way if there is any exception raised at any point, such as after a new Customer record has been created and saved, all saved changes will be rolled back and we can inspect any logged exception details along with recorded values to determine what went wrong.

When dealing with combinations of DbContext operations and other operations that we might want to support a rolling back process flow on failure then we can leverage constructs like the TransactionScope wrapper. However this should be used with caution and only in cases where you explicitly need to marry these operations rather than attempting to use the pattern as a standard across all operations. In most cases you will not need explicit transactions.

Can a transaction be rolled back after it's committed and connection is closed?

A committed transaction can never be rolled back if you want your transaction to be persistent after a commit.

Are you looking for savepoints?

Do I need to explicitly rollback a transaction?

It is important to rollback the tx if there is an error while executing any query, otherwise it is still running and holding locks. Check out this post .