Advanced Transaction: Savepoint And Prepared Transaction

Hope you found my two previous posts on transaction interesting and useful. Based on some of readers request, I would cover few advanced transaction topic in my subsequent posts. In this post, I am going to cover following advanced variant of traditional transaction:

     1. Savepoint

     2. Prepared Transaction

Savepoint

In order to understand its usage, imagine one of your customer work-load as below:

            START TRANSACTION;

            Perform operation-1

            Perform operation-2

            Perform operation-3

Perform operation-4

            COMMIT;

As per the business logic it is likely that operation-3 and operation-4 are going to fail and also failure of these operations does not impact operation-1 and operation-2. Now as per transaction properties (recall the Basic of Transaction), whole of operation will be roll backed in-case of any of the four operation fails. So in the given business scenario though operation-1 and operation-2 should not be rollbacked but still it will be. 

In order to avoid this behavior  and control the main transaction life span, savepoint is being introduced.

Savepoint can be created only inside a transaction block. It creates a saving point for overall transaction i.e in-case if any commands results in error inside a transaction block, then instead of rollback whole transaction, it allows to rollback till the point where savepoint was created. E.g. Consider following example:
        CREATE TABLE TBL(ID INT, ID2 INT);
        CREATE UNIQUE INDEX IDX ON TBL(ID);

        START TRANSACTION
        INSERT INTO TBL VALUES(100, 100);
        INSERT INTO TBL VALUES(200, 200);
        SAVEPOINT first_save;
        INSERT INTO TBL VALUES(200, 300); <=== This will fail because of unique index.

Related Comamnds

1. SAVEPOINT savepoint_name: This command create a new savepoint inside a transaction block as shown in above example. There can be nested savepoint also i.e. savepoint can be created with-in already created savepoint.

2. ROLLBACK TO [SAVEPOINT ] savepoint_name: This command rollback all operation done from the point savepoint was created as shown in above example. Rollback to savepoint does not destroy the given savepoint but it destroys any savepoint nested with-in the given savepoint. E.g.

        START TRANSACTION

        INSERT INTO TBL VALUES(100, 100);

        INSERT INTO TBL VALUES(200, 200);

        SAVEPOINT first_save;

        INSERT INTO TBL VALUES(300, 300);

        SAVEPOINT second_save;

        INSERT INTO TBL VALUES(400, 400);

        ROLLBACK TO SAVEPOINT first_save;   <=== Will destroy second_save but first_save will remain active.

3. RELEASE [SAVEPOINT] savepoint_name: As the name suggest, it just removes/destroys the savepoint created. It does not have any impact on any other command executed after this savepoint. Similar to ROLLBACK TO SAVEPOINT, this command also destroy all savepoint nested with-in the savepoint getting released.

        START TRANSACTION

        INSERT INTO TBL VALUES(100, 100);

        INSERT INTO TBL VALUES(200, 200);

        SAVEPOINT first_save;

        INSERT INTO TBL VALUES(300, 300);

        SAVEPOINT second_save;

        INSERT INTO TBL VALUES(400, 400);

        RELEASE SAVEPOINT first_save; <=== Destroy both savepoints but no impact on records inserted.

        INSERT INTO TBL VALUES(400, 500); <=== As no savepoint active, so whole transaction gets rollbacked.

NOTE: There is no command like COMMIT TO SAVEPOINT as there is no meaning to commit only part of transaction (As it will be against transaction basic properties).

Prepare Transaction

Before jumping to define this variation, it is important to understand two-phase commit. As the name suggest it does commit of any transaction in two steps.

1.     First step is to verify all condition which needs to be satisfied in order to commit this transaction. This is called prepare stage.

2.    Second step is the actual commit, which is almost same as traditional commit.

This is more useful for a cluster based database or any similar variations, where in various nodes (databases) are responsible to run a part of bigger operation. So it is important that each node does either COMMIT or ROLLBACK at the same time in order to maintain overall transaction property. Many cluster solution achieve this using a dedicated transaction manager or just coordinator, whose tasks are:

1. Step-1: will probe all nodes to check if commit can be done and in turns all nodes will respond with either positive or negative acknowledgment.
2. Step-2: Once coordinator or transaction manager gets response from all nodes, it will issue COMMIT or ROLLBACK to all nodes.

I will cover more detailed two-phase commit in my future blogs.

So now coming back to understand Prepare Transaction, it actually prepares a normal current running transaction for two-phase commit. Once a transaction is prepared, it dissociates from current session and its gets stored on disk. Since its state gets stored on disk, it is almost certain that this transaction can be committed successfully even in-case of any database crash happened before explicit issue of commit.

Prepare transaction is given a unique identifier, which is used later to commit or rollback this transaction. Once prepare transaction is done, transaction gets dissociated from current session. Subsequently this transaction can be committed or rollbacked from any session using the name given during prepare.

Like traditional transaction, unless prepared transaction gets committed or rollbacked, impact of any operation done will not be visible by any session including the session which has performed this transaction.

This is not recommended to be used by application or any interactive sessions. Rather it should be used where there is need of any external transaction manager, which performs some validation or has some other dependency before actual commit.

Related Comamnds

1. PREPARE TRANSACTION name: Prepares the current transaction and immediately dissociates from the current session. A unique name is given to this prepared transaction, so that later it can be committed/rollbacked using the same name.
2. COMMIT PREPARED name: Commit the already prepared transaction. It’s like performing second phase of two phase commit protocol.
3. ROLLBACK PREPARE name: Rollback the already prepared transaction.

       Example:

        START TRANSACTION

        INSERT INTO TBL VALUES(100, 100);

        INSERT INTO TBL VALUES(200, 200);

        PREPARE TRANSACTION 'first_prepare';

        SELLECT * FROM TBL  <=== This will return zero record, as transaction is not yet committed

        COMMIT PREPARED 'first_prepare';

        SELLECT * FROM TBL  <=== This will return both records.

        START TRANSACTION

        INSERT INTO TBL VALUES(100, 100);

        INSERT INTO TBL VALUES(200, 200);

        PREPARE TRANSACTION 'first_prepare';

        ROLLBACK PREPARED 'first_prepare';

        SELLECT * FROM TBL  <=== This will return zero records as transaction rollbacked.

It is highly recommended to take PostgreSQL package from https://www.postgresql.org/download/ and try all of the commands described. 

Subscribe to my blog to stay informed about my new upcoming post.  

Please feel free to drop your query/suggestion/comments.