Transaction management

Against databases that use logging, a UDR inherits the transaction state that is started by the SQL statement that invoked the UDR.

All statements in a UDR occur inside a transaction because the UDR is called from within an SQL statement. An SQL statement is always part of a transaction. The type of transaction that the SQL statement is part of is based on the type of database and whether it uses transaction logging, as the following table shows.

Table 1. Types of transactions
Status of database	Status of SQL statement	ANSI-compliant	Description
Database does not use transaction logging.	No transactions exist.	Yes	The database server does not log changes to the database that SQL statements might make. Any UDRs that are part of the SQL statement are not logged and their actions cannot be rolled back.
Database uses transaction logging.	Explicit transaction	Yes	The client application begins an explicit transaction with the BEGIN WORK statement and ends it with either the COMMIT WORK statement (transaction successful) or the ROLLBACK WORK statement (transaction not successful). Operations within a single cursor (from OPEN to CLOSE) constitute a transaction as well. SQL statements between the BEGIN WORK and COMMIT WORK or ROLLBACK WORK statements (or within a cursor) execute within the explicit transaction. If these SQL statements contain any UDRs, each of the UDRs executes within the explicit transaction.
Database uses transaction logging.	Single-statement transaction	Yes	The client application begins a single-statement transaction for any SQL statement that is not contained within a BEGIN WORK statement and a COMMIT WORK or ROLLBACK WORK statement. Any UDRs that are part of the SQL statement are within this single-statement transaction. The only exception to this rule is the EXECUTE FUNCTION statement; it does not execute within a transaction.
Database logging is always in effect.	Each SQL statement executes within an implicit transaction, which is always in effect.	No	The client application invokes an SQL statement, which begins the implicit transaction, and the transaction ends explicitly with COMMIT WORK or ROLLBACK WORK. Any UDRs within the SQL statement that began the implicit transaction are automatically part of the transaction. In addition, any SQL statements that execute before the COMMIT WORK or ROLLBACK WORK statement ends the transaction are also part of the implicit transaction.

You can obtain the transaction ID of the current transaction with the mi_get_transaction_id() function.

As a rule, a C UDR must not issue any of the following SQL transaction statements because they interfere with transaction boundaries:

BEGIN WORK
COMMIT WORK
ROLLBACK WORK

In all databases that use logging, an SQL statement is within a transaction. In such databases, a DML statement (SELECT, INSERT, UPDATE, DELETE) implicitly starts a transaction, if a transaction is not already in effect. If a UDR that executes one of these SQL transaction statements is called from a DML statement, the database server raises an error (-535).

However, the EXECUTE PROCEDURE and EXECUTE FUNCTION statements do not implicitly start another transaction, if they are not already in a transaction. If a UDR is called from an EXECUTE PROCEDURE or EXECUTE FUNCTION statement, the database server only raises an error if the UDR interferes with the current transaction boundaries.

For example, suppose you have a UDR named udr1() that uses the mi_exec() function to execute two SQL statements:

void udr1(...)
{
   mi_exec(...DML statement 1...);
   mi_exec(...DML statement 2...);
}

Suppose also that you execute this UDR with the EXECUTE PROCEDURE statement, as follows:

EXECUTE PROCEDURE udr1();

If a transaction has not already been started, this UDR would have two transactions, one for each call to mi_exec().

To get a single transaction, you can surround these SQL statements with a begin and end work, as udr2() shows:

void udr2(...)
{
   mi_exec(...'begin work'..);
   mi_exec(...DML statement 1...);
   mi_exec(...DML statement 2...);
   mi_exec(...'commit work'...);
}

However, you can only start a transaction within a UDR if you are not already in a transaction. Therefore, you can only invoke a UDR that starts a transaction when the following restrictions are met:

You must invoke the UDR with the EXECUTE PROCEDURE or EXECUTE FUNCTION statement.
Because udr2() is a user-defined procedure, you must use EXECUTE PROCEDURE to invoke it, as follows:
```
EXECUTE PROCEDURE udr2();
```
Suppose you tried to invoke udr2() with the following SELECT statement:
```
SELECT udr2() FROM tab WHERE x=y;
```
If a transaction had not started, the SELECT operation starts its own implicit transaction. The database server raises an error when execution reaches the first call to mi_exec() in udr2():
```
mi_exec(...'begin work'..);
```
The UDR calling context must not have already started a transaction.
The following code fragment fails because the EXECUTE PROCEDURE statement is already within a transaction block and udr2() attempts to start another transaction:
```
BEGIN WORK;
...
EXECUTE PROCEDURE udr2();  /* This statement fails. */
...
COMMIT WORK:
```
The database server raises an error when execution reaches the first call to mi_exec() in udr2():
```
mi_exec(...'begin work'..);
```

Important: Unless a UDR knows its calling context, it must not issue an SQL transaction statement. If the caller has already begun a transaction, the UDR fails.

You can execute an SQL transaction statement in a UDR that you call directly from a DataBlade® API module (not from within an SQL statement). You can also choose whether to commit or rollback the current transaction from within an end-of-statement or end-of-transaction callback function. For more information, see State transitions in a C UDR (Server).

In a database with logging, the database server creates an internal savepoint before execution of each statement within a UDR that might affect the database state. If one of these statements fails, the database server performs a rollback to this internal savepoint. At this point, the database server does not release table locks. However, the same user can obtain a lock on the same table in the same transaction. The database server releases the table lock when the entire transaction ends (commit or rollback).

Restriction: For databases that do not use logging, no changes to the database that a UDR might make are logged. Therefore, none of these changes can be rolled back. Consider carefully whether you want to use logging for your database.