Browse Prior Art Database

Method for managing distributed savepoints within stored procedures accessing multiple DBMS's within a distributed transaction.

IP.com Disclosure Number: IPCOM000189558D
Original Publication Date: 2009-Nov-12
Included in the Prior Art Database: 2009-Nov-12

Publishing Venue

IBM

Abstract

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 portion of the 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. Within a transaction, customers can issue any SQL statement, including stored procedures and statements that modify a remote (non-local) database maintained by a different server. This article describes the method for RDBMS server to efficiently co-ordinate the maintenance and rollback of savepoints in complex transactions involving distributed transactions and nested stored procedures.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 20% of the total text.

Page 1 of 10

Method for managing distributed savepoints within stored procedures accessing multiple DBMS's within a distributed transaction.

SAVEPOINT SQL statements offer a mechanism to set markers within transaction and let application undo portions of a transaction. With this feature, the user can arbitrarily mark points within one SQL transaction, called a savepoint. If an error occurs during execution or for any reason in business logic, application can rollback the database changes made between the time the savepoint is started and the time the savepoint rollback request is issued.

begin work; savepoint update

_customer

_tab;

update customer set last

_order

date =

TODAY where customer

_

id = 1234;

savepoint update

_shipping

_tab

update shipping set shipping

_

date =

_;TODAY where customer

_

id = 1234 and order

_

id

= "x12345"; savepoint update

_credit

_info;

                                   credit - 100; -- check for error here and decide to rollback.
rollback to savepoint update

update cust

_credit remaining

_

credit = remaining

_

_shipping

_

tab;

Problem:

The savepoint feature is straight forward to implement when you have a single

database or a single database server and savepoints are set, released and rolled back from the application directly.

There are two other features and/or scenarios where

the savepoint management becomes complicated.

1. usage of stored procedures (UDRs) within transactions and use of savepoints within them.
2. usage of distributed queries within stored procedures when transaction uses savepoints.

Let's consider each scenario separately first.
1. savepoints and stored procedures (UDRs):

Naming scope of Savepoints within each stored procedure (UDR) should be limited to respective procedure invocation. Each procedure invocation starts a new 'level' to provide the scope for savepoints defined in that invocation. When writing the stored procedure, it's impossible to assume the current savepoints in the higher levels of transaction.

create procedure foo(id int, name varchar(32)); savepoint step1; insert into logtab values(id, name); savepoint step2 ; insert into shipping

_

                tab values(id, name); -- something happens..
rollback work to savepoint step1;
end procedure;

1

Page 2 of 10

begin work; -- start a new transaction. savepoint step1;
insert into order tab values(blah..blah); savepoint step2 ;
execute procedure foo(33, 'Jack');
-- something else happens
rollback work to savepoint step1; commit work;

step1 within the transaction is different from step1 within the procedure.

2. savepoints in stored procedures and distributed queries:

After a savepoint is started, at any level, database server needs to keep track of all the modifications to each of the remote servers. When the transaction rolls back to a certain savepoint, database server needs to identify the exact point each of the subordinate servers has to roll back to. When you have...