Introduction

Transactions on IBM Informix Dynamic Server (IDS) are atomic — meaning that either all of the operations within a transaction are committed or none are committed. For example, if an airline reservation application is booking a multi-city flight from New York to Chicago to Dallas to Los Angeles, and it receives a constraint violation or space full error while booking the last leg, the application would have to cancel the entire transaction and then retry or send an error.

IDS 11.50.xC3 introduces the savepoint feature. This feature enables applications to rollback a transaction to a predetermined marker. Savepoints are named markers within a database transaction. In the case of an error, the transaction logic can specify that the transaction rollback to a savepoint. The IDS implementation of savepoints follows the SQL-99 standard.

So in the airline reservation example, you could setup a savepoint before each leg of the journey and retry just the failed part of the reservation within the same transaction by rolling back to the appropriate savepoint.

All the examples in this article use the stores_demo demonstration database. The stores_demo database is a logging database that you can create by running the command: dbaccessdemo –log. The Resources section of this article contains a link to more information about creating and using the stores_demo database.

Application development with savepoints

You can use savepoints in applications written with any IDS client API or language. This section shows examples of how to use savepoints with each API.

ESQL/C

All the new savepoint statements are supported by ESQL/C clients starting with version 3.50.xC3.

The example in Listing 1 is an ESQL/C program that demonstrates the use of savepoints. The example program sets a savepoint named newcustomer before adding the new customer, and sets another savepoint named neworder before inserting new orders for this new customer. If there is an error, the program rolls back the transaction to the neworder savepoint. This rollback undoes all the operations that were performed after the neworder savepoint was set.

					
    #include <stdio.h>
		      	        
    main()
    {
    EXEC SQL BEGIN DECLARE SECTION;
    INT qty, mycount;
    EXEC SQL END DECLARE SECTION;
		        
    /* Connect to logged database, stores_demo */
    $database stores_demo;
       
    /* Count of customers named John */
    $select count(*) into :mycount from customer where fname = "John";
    printf("Customer John : %d\n", mycount);
		        
    /* No of items in order number 2000 */
    $select count(*) into :mycount from items where order_num = 2000;
    printf("No of items in our order : %d\n", mycount);
       
    $begin;
    /* Set a savepoint before adding new customer */
    $savepoint newcustomer;
    $insert into customer (customer_num, fname, lname) 
              values (0, "John","Doe");
    
    /* Set a savepoint before entering order for the new customer */
    $savepoint neworder;
    $insert into orders (order_num, customer_num) 
              values (2000, 125);
    $insert into items 
             (item_num, order_num, quantity, stock_num, manu_code) 
             values (3, 2000, 2, 4, "HSK");
	        
    /* Rollback to the neworder savepoint */
    if (SQLCODE)
          $rollback to savepoint neworder;
    
    /* Addition of customer named John is now successful */
    $select count(*) into :mycount from customer where fname = "John";
    printf("Customer John : %d\n", mycount);
	        
    /* We only rolled back the entry of new order for order number 2000 */
    $select count(*) into :mycount from items 
          where order_num = 2000;
    printf("No of items in our order : %d\n", mycount);
    $rollback work;
    }
		        
		    

JDBC

JDBC implements the java.sql.Savepoint interface defined in the JDBC 3.0. This lets you set, rollback, and release savepoints in a transaction.

JDBC 3.50.JC3 implements the following methods in the Connection object:

• Savepoint setSavepoint() throws SQLException;
• Savepoint setSavepoint(String name) throws SQLException;
• Void rollback (Savepoint savepoint) throws SQLException;
• Void releaseSavepoint(Savepoint savepoint) throws SQLException;

A new class, IfxSavepoint, implements the java.sql.Savepoint interface. A new interface, IfmxSavepoint, is also added to support the Informix extensions to the JDBC specification, such as the UNIQUE option for the savepoint object. A new public method is added to the IfxConnection interface to set a UNIQUE savepoint:

• IfxSavepoint setSavepointUnique(String name) throws SQLException;

The example in Listing 2 illustrates the usage of savepoints in a Java® application.

					
    Public class testSavepoints
    {	
    IfxSavepoint newCustSvpt, newOrderSvpt;
		    
     // Create Connection object conn to connect to the stores_demo database
     conn.setAutoCommit(false);
		    
    // Count of customers named John
	    PreparedStatement pstmt = conn.prepareCall
               (“select * from customer where 
               fname = \"John\", ResultSet.TYPE_SCROLL_INSENSITIVE, 
               ResultSet.CONCUR_READ_ONLY);
		    
    ResultSet res = pstmt.executeQuery();
		    
		    // No of items in order number 2000
    pstmt = conn.prepareCall(“select * from items where order_num = 2000",
          ResultSet.TYPE_SCROLL_INSENSITIVE, 
	        ResultSet.CONCUR_READ_ONLY); 
		    
    ResultSet res = pstmt.executeQuery();
	   
    // Set a savepoint before adding new customer 
    newCustSvpt = conn.setSavepoint(“newcustomer");
		    
    Statement stmt = conn.createStatement();
    // Add new customer named ‘John Doe’
    str = “insert into customer (customer_num, fname, lname) 
          values (0, \"John\",\"Doe\")";
    stmt.executeUpdate(str);
		    
     // Set another savepoint before entering new order
    newOrderSvpt = conn.setSavepoint(“neworder");
		    
    // Add new order
    str = “insert into orders (order_num, customer_num) 
          values (2000, 125)";
    stmt.executeUpdate(str);
		    
    str = “insert into items 
	        (item_num, order_num, quantity, stock_num, manu_code)
          values (3, 2000, 2, 4, \"HSK\")";
    stmt.executeUpdate(str);
		    
    // Rollback to new order savepoint
    conn.rollback (newOrderSvpt);
		    
    // Count of customers named John increases by 1
    pstmt = conn.prepareCall
	         (“select * from customer where fname = \"John\"", 
	         ResultSet.TYPE_SCROLL_INSENSITIVE, 
	         ResultSet.CONCUR_READ_ONLY);
		    
    ResultSet res = pstmt.executeQuery();
		    
    // No of items in order number 2000 is 0
    pstmt = conn.prepareCall
	         (“select * from items where order_num = 2000" , 
	         ResultSet.TYPE_SCROLL_INSENSITIVE, 
	         ResultSet.CONCUR_READ_ONLY);
		    
    ResultSet res = pstmt.executeQuery();
		    
    conn.rollback();
    }
		    
		    

Others

Applications using other clients can use savepoints in IDS by sending the savepoint statements directly to the server. This is also true for ESQL/C and JDBC applications.

Back to top

Savepoints in action

This section describes the SQL statements that were introduced with IDS 11.50.xC3 to support savepoint processing. For complete details and syntax of the statements, refer to the Informix SQL section of the IDS Information center linked to from the Resources section of this article.

SAVEPOINT statement

The SAVEPOINT statement sets a savepoint at the current position in the transaction. This is the marker within the transaction that can be rolled back to, if required. The syntax of the statement is:

SAVEPOINT svpt1 UNIQUE

The svpt1 identifier is the name of the savepoint.

The UNIQUE keyword is optional. When used, it specifies that this is meant to be a unique savepoint within the scope of the current transaction. If a savepoint with the same name that is specified for svpt1 already exists in the same transaction, this statement generates an error.

The example in Listing 3 demonstrates the usage of the SAVEPOINT statement in a transaction connected to the stores_demo database. The example sets a savepoint named newcustomer before inserting a new customer, and sets another savepoint named neworder before adding the new order for this new customer. Setting another savepoint named newcustomer with the UNIQUE keyword results in an error.

					
    Database stores_demo;
    Begin work;
		      
    Savepoint newcustomer;
    Insert into customer (customer_num, fname, lname) 
        values (0, “John",“Doe");
    // Next statement results in an error because there is already a newcustomer savepoint
    Savepoint newcustomer UNIQUE;
    Savepoint neworder;
    Insert into orders (order_num, customer_num) values (2000, 125);
    Insert into items (item_num, order_num, quantity, stock_num, manu_code) 
        values (3, 2000, 2, 4, “HSK");
		      
		    

ROLLBACK TO SAVEPOINT statement

The ROLLBACK statement with the optional TO SAVEPOINT clause undoes changes that occurred since a particular savepoint was set. The syntax of the statement is:

ROLLBACK TO SAVEPOINT svpt1

The TO SAVEPOINT clause is optional. When used, the statement rolls back the part of the transaction that occurred after the savepoint identified by svpt1. Or, if the optional svpt1 identifier is not used, rollback happens to the last savepoint specified in the current transaction. A ROLLBACK statement without TO SAVEPOINT rolls back the entire transaction.

As described earlier, you set savepoints with the SAVEPOINT statement. The ROLLBACK TO SAVEPOINT statement causes all the operations, including data manipulation and definition statements, performed between the specified SAVEPOINT statement and the ROLLBACK statement to be rolled back. After a rollback, any savepoints set subsequent to the SAVEPOINT statement that was rolled back to cannot be referenced because they no longer refer to any meaningful position in the transaction. All locks acquired after the specified SAVEPOINT statement are retained until the end of the transaction. Any non-hold cursor opened after the specified SAVEPOINT statement is closed.

Note: You may also see the ROLLBACK statement used as ROLLBACK WORK. These two statements are identical; the statement works the same whether or not WORK is used.

The example in Listing 4 is again connected to the stores_demo database. It sets a savepoint named newcustomer before inserting a new customer and a savepoint named neworder before adding the new customer’s order. If an error occurs while adding the customer's order, the ROLLBACK statement is used to undo the part of the transaction that added the order but keep the part of the transaction that added the new customer.

					
    Database stores_demo;
    Begin work;
		      
    Savepoint newcustomer;
    Insert into customer (customer_num, fname, lname) 
        values (0, “John",“Doe");
   		      
    Savepoint neworder;
    Insert into orders (order_num, customer_num) values (2000, 125);
    Insert into items (item_num, order_num, quantity, stock_num, manu_code) 
        values (3, 2000, 2, 4, “HSK");
    Rollback to savepoint neworder;
		
    Select * from customer where fname = “John";
    Select * from orders o, customer c where 
        o.customer_num = c.customer_num AND
        fname = “John"; 	
		
		    

RELEASE SAVEPOINT statement

The RELEASE SAVEPOINT statement releases savepoints. The syntax of the statement is:

RELEASE SAVEPOINT svpt1

This statement releases the savepoint identified by svpt1 plus any other savepoints that were set after svpt1 and before this RELEASE SAVEPOINT statement. Any reference to a released savepoint is invalid and results in an error.

The example in Listing 5 demonstrates the effect of a RELEASE SAVEPOINT statement.

					
    Database stores_demo;
    Begin work;

    Savepoint newcustomer;
    Insert into customer (customer_num, fname, lname) 
        values (0, “John",“Doe");

    Savepoint neworder;
    Insert into orders (order_num, customer_num) values (2000, 125);
    Insert into items (item_num, order_num, quantity, stock_num, manu_code) 
        values (3, 2000, 2, 4, “HSK");
    Release savepoint neworder;
    // Next statement results in an error because the neworder savepoint no longer exists 
    Rollback to savepoint neworder;
				

Back to top

Savepoints and cursors

When a transaction rolls back to a savepoint, all non-hold cursors opened after that savepoint are closed. Any subsequent reference to the cursor after the ROLLBACK statement results in an error indicating that the cursor was closed.

The example in Listing 6 demonstrates how a reference to a cursor can cause an error if the cursor has been closed as the result of a ROLLBACK statement.

	

    Database stores_demo;

    Create procedure newcust_neworder()
    define qty int;
    define qty_in_stock int;
    define stnum int;
    define mcode char(3);

    prepare stmt1 from “select stock_num, manu_code from items where order_num=2000";
    declare cur1 cursor for stmt1;

    Savepoint newcustomer;
    Insert into customer (customer_num, fname, lname) values (0, “John",“Doe");

    Savepoint neworder;
    Insert into orders (order_num, customer_num) values (2000, 125);
    Insert into items (item_num, order_num, quantity, stock_num, manu_code) 
        values (3, 2000, 2, 4, “HSK");

    Open cur1;

    fetch cur1 into stnum, mcode, qty;
    while (SQLCODE == 0)
        select count(*) into qty_in_stock from stock 
            where stock_num=stnum AND manu_code=mcode;
        if (qty_in_stock < qty)
        then
            rollback to savepoint neworder;
        end if;
        // If rollback to neworder occurred, the following statement results in an error
        fetch cur1 into stnum, mcode;
    end while;
    end procedure;
   
 Begin work;

    Savepoint newcustomer;

    execute procedure newcust_neworder();

    select * from customer where fname = “John";

    rollback to savepoint newcustomer;

    select * from customer where fname = “John";

		  

The procedure in the example above adds the new customer and order, and then goes through the items ordered under order number 2000. For each item in the order, the procedure checks if the quantity in stock can fulfill the quantity ordered. If any ordered item cannot be fulfilled, the procedure rolls back the order entry. After the quantity check, the procedure tries to fetch next from the cursor, but if the rollback occurred, this causes an error because the rollback closed the cursor.

Although the new order cannot be entered, the new customer addition still passes with the procedure execution, and is rolled back with the final rollback to savepoint in the main transaction. The next section of this article describes the usage of savepoints in the context of stored procedures.

The example in Listing 7 shows how you would code the same functionality as Listing 6, but in an ESQL/C program.

	

    main()
    {
    EXEC SQL BEGIN DECLARE SECTION;
    INT qty, mycount, qty_in_stock, mcode, st;
    EXEC SQL END DECLARE SECTION;

    $declare cur1 cursor for 
        “select stock_num, manu_code from items where order_num=2000";

    /* Connect to logged database, stores_demo */
    $database stores_demo;

    /* Count of customers named John */
    $select count(*) into :mycount from customer where fname = "John";
    printf("Customer John : %d\n", mycount);

    /* No of items in order number 2000 */
    $select count(*) into :mycount from items where order_num = 2000;
    printf("No of items in our order : %d\n", mycount);

    $begin;
    /* Set a savepoint before adding new customer */
    $savepoint newcustomer;
    $insert into customer (customer_num, fname, lname) values (0, "John","Doe");

    /* Set a savepoint before entering order for the new customer */
    $savepoint neworder;
    $insert into orders (order_num, customer_num) values (2000, 125);
    $insert into items (item_num, order_num, quantity, stock_num, manu_code) 
        values (3, 2000, 2, 4, "HSK");

    /* Open the cursor after setting the savepoint */
    $open cur1;

    $fetch cur1 into :stnum, :qty;
    while (SQLCODE == 0)
        {
        select count(*) into :qty_in_stock from stock 
            where stock_num = :stnum AND manu_code = :mcode;

        if (qty_in_stock < qty)
            /* Rollback to the neworder savepoint */
            $rollback to savepoint neworder;

        /* Keep fetching from the cursor until we are done */
        $fetch cur1 into :stnum, :qty;
        }

    /* Addition of customer named John is now successful */
    $select count(*) into :mycount from customer where fname = "John";
    printf("Customer John : %d\n", mycount);

    /* We only rolled back the entry of new order for order number 2000 */
    $select count(*) into :mycount from items where order_num = 2000;
    printf("No of items in our order : %d\n", mycount);
    $rollback;
    }

		  

Back to top

Savepoints in stored procedures

You can use all of the savepoint statements within stored procedures. The scope of savepoints set inside a stored procedure is restricted to the individual execution of the stored procedure. Procedure calls can be nested or recursive, but the scope of a savepoint is restricted to the execution of the stored procedure in which it is defined. Therefore, a savepoint name needs to be unique only in its scope of execution. This also means that you can refer to a savepoint only in the same execution of a procedure, but not from another execution of the same or other procedures irrespective of the nesting level.

The next example is shown in two parts, Listing 8.1 and Listing 8.2. The example connects to the stores_demo database.

The first part of the example in Listing 8.1 defines a procedure for processing a new order for a new customer. The procedure sets a savepoint named neworder. If the available quantity from the stock table for the requested item is less than the quantity ordered, the transaction rolls back to the neworder savepoint. This rolls back the addition of the new order, but the addition of the new customer is still successful at the end of the procedure execution.

				

    Database stores_demo;

    Create procedure newcust_neworder()
    define qty int;

    Savepoint newcustomer;
    Insert into customer (customer_num, fname, lname) values (0, “John",“Doe");

    Savepoint neworder;
    Insert into orders (order_num, customer_num) values (2000, 125);
    Insert into items (item_num, order_num, quantity, stock_num, manu_code) 
        values (3, 2000, 2, 4, “HSK");

    select count(*) into qty from stock where stock_num=4 AND manu_code="HSK";
    if (qty < 2)
    then
        rollback to savepoint neworder;
    end if;

    end procedure;
		  

The second part of the example, which is shown in Listing 8.2, calls the procedure defined in Listing 8.1. The code sets a savepoint with the same newcustomer name as the savepoint set inside the procedure. The procedure rolls back to its newcustomer savepoint ending up retaining the new customer that was added after the procedure execution, but this is also rolled back with the ROLLBACK statement.

				
		    
    Begin work;

    Savepoint newcustomer;

    execute procedure newcust_neworder();

    select * from customer where fname = “John";

    rollback to savepoint newcustomer;

    select * from customer where fname = “John";

			

The next example also illustrates the use of savepoints with stored procedures. The example is shown in three parts in Listings 9.1 to 9.3.

The example program goes through the items table to fulfill orders from the stock table. For any given order number, the program identifies whether all of its items are available in stock. The program fulfills the order if all items are in stock, otherwise it rolls back the current order only.

				
		    
    Database stores_demo;

    Create procedure item_in_stock(stnum integer, mcode integer, qty integer) 
    returning integer
    define qty_in_stock int;

    select count(*) into qty_in_stock from stock 
        where stock_num=stnum AND manu_code=mcode;

    if (qty <= qty_in_stock)
    then
        delete from stock where stock_num=stnum AND manu_code=mcode;
        return 0;
    else
        return 1;
    end if;
    End procedure;

		  

The item_in_stock procedure above identifies if a particular item is in stock and if so, deletes it from the stock table.

		    
    Create procedure order_items(ordnum integer)
    Define stnum, mcode, qty integer;
    Define result integer;
  
    Savepoint order1;
  
    Foreach cur1 for
        (select stock_num, manu_code, quantity into stnum, mcode, qty  
            From items where order_num = ordnum)
        execute procedure item_in_stock(stnum, mcode, qty) into result;
        If (result == 1)
        Then
            Rollback to savepoint order1;
        End if;
    End foreach;
  
    Update orders set ship_date = TODAY,  backlog = ‘n’
        where order_num = ordnum;
  
    Release savepoint order1;
  
    End procedure;
  
        

The order_items procedure above calls the item_in_stock procedure for each item obtained from the items table for the given order number. If any item is not in stock, the program rolls back the current order fulfillment process by rolling back to the savepoint named order1, which was set before going through the items table for the given order. If all items are fulfilled, the order is marked as being shipped with the current date. This illustrates the use of savepoints in a scenario where you would want to rollback just a small part of a transaction.

				
		    

    Create procedure fulfill_orders()
    Define ordnum integer;

    Foreach cur1 for
        (select order_num into ordnum from orders where backlog != ‘n’)
        execute procedure order_items(ordnum);
    end foreach;

    End procedure;

    Begin work;
			
    Execute procedure fulfill_orders();
			
    Commit work;

		  

The fulfill_orders procedure above calls the order_items procedure for each order from the orders table that is not in backlog. At the end of the execution of this procedure, the program has gone through all the orders and fulfilled only those that can be fulfilled with the items currently in stock.

Back to top

Savepoints and Data Definition Language statements

Data Definition Language (DDL) statements can also be rolled back with a ROLLBACK TO SAVEPOINT statement. Because all DDL statements are logged by IDS, they support the rollback property inherently. When you rollback to a savepoint set earlier in a transaction, IDS just rolls back all the operations logged by the transaction after the savepoint was set.

The example in Listing 10 demonstrates how you can rollback DDL statements such as CREATE TABLE, DROP INDEX, and ALTER TABLE.

				
		    

    Database stores_demo;

    Begin;

    Savepoint ddl_svpt1;
    Create table t1 (col1 int);
    Insert into t1 values(1);

    Savepoint ddl_svpt2;
    Create index i1 on t1(col1);

    Savepoint ddl_svpt3;
    Alter table t1 add (col2 char(20));
    Select * from t1;		<------------ A 
  
    Rollback to savepoint ddl_svpt3;
    Select * from t1;		<------------ B 
    
    Rollback to savepoint ddl_svpt2;
    Select * from t1;		<------------ C
      
    Rollback to savepoint ddl_svpt1;
    Select * from t1;		<------------ D
        
    Commit;
		  

The statement above at label A shows table t1 with two columns: col1 and col2. The statements at B and C show only the col1 column because the ALTER TABLE statement has been rolled back. The statement at D results in an error because the CREATE TABLE statement for the t1 table has been rolled back.

Back to top

Savepoints in distributed transactions

Savepoints are also supported in distributed transactions as long as the transaction is dealing with logged databases and all the participating servers are at version 11.50.xC3 or higher.

If you set a savepoint and update a pre-11.50.xC3 subordinate server in a transaction, you receive a warning. Any rollbacks to the savepoint fail if any updates were made on an older server.

The example in Listing 11 connects to a local database named ldb, sets savepoints, performs operations on remote databases, and rolls back to a savepoint. The behaviors and results are the same as if the operations were performed on a local database.

				
		    

    Dbaccess ldb;
    Begin work;

    Savepoint newcustomer;
    Insert into stores_demo@remote1:customer (customer_num, fname, lname) 
        values (0, “John",“Doe");

    Savepoint neworder;
    Insert into stores_demo@remote1:orders (order_num, customer_num) 
        values (2000, 125);
    Insert into stores_demo@remote1:items 
        (item_num, order_num, quantity, stock_num, manu_code) 
        values (3, 2000, 2, 4, “HSK");

    Rollback to savepoint neworder;

    Select * from stores_demo@remote1:customer where fname = “John";
    Select * from stores_demo@remote1:orders o, stores_demo@remote1:customer c where 
        o.customer_num = c.customer_num AND fname = “John"; 

		  

In the example above, the logged database named stores_demo, has been created on the server instance named remote1. If the remote1 server does not support savepoints (pre-11.50.xC3), then the insert statements in the example would return a warning indicating that a non-savepoint site was updated when a savepoint was set. The ROLLBACK statement would fail with the error: Rollback to savepoint disallowed on updating an old server in same transaction.

Back to top

Savepoints and temp tables

Temp tables are maintained at the session level. On rolling back to a savepoint, all the logged temp tables — data and schema — are restored to their states prior to setting the savepoint.

				
		    
		  
    Database stores_demo;
    Select stock_num from stock into temp t1;
    select * from t1;               
    Begin work;
    savepoint s1;
    drop table t1;
    rollback to savepoint;
    select * from t1;               
    savepoint s2;
    drop table t1;
    create temp table t1(c1 int, c2 int); 
    select * from t1;		<--------A
    rollback to savepoint s1;
    select * from t1;
    rollback;
		    
		  

In the example above, the first and second select statements show the table with one column named stock_num and all the rows, The third select statement (label A) shows the new schema with two columns named c1 and c2, but no rows. The final select statement shows the stock_num column with the original set of data.

Back to top

Conclusion

Savepoints are the perfect mechanism to achieve finer granularity of atomicity within a transaction. IDS now supports this valuable feature with 11.50.xC3 and future versions.

Acknowledgement

The author would like to thank his architect, Keshava Murthy, for reviewing this article.

Resources

Learn

• Informix on developerWorks Find articles and tutorials, and connect to other resources to expand your Informix skills.
  
  
• Learn more about Informix at the IBM Informix Dynamic Server 11.5 Information Center.
  
  
• The "Demonstration Databases" article in the IBM Informix Dynamic Server 11.5 Information Center provides information on creating the stores_demo database.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Download a free trial version of Informix Dynamic Server.
  
  

Discuss

• Participate in the discussion forum.
  
  

About the author

Uday Kale is the IDS SQL lead developer. He has been with IBM Informix development for about 9 years, developing various features in IDS kernel and SQL components of the server.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Application development with savepoints
• Savepoints in action
• Savepoints and cursors
• Savepoints in stored procedures
• Savepoints and Data Definition Language statements
• Savepoints in distributed transactions
• Savepoints and temp tables
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers