# oceanbase-transaction

Part of **OCEANBASE**

<!-- intent-backlink:auto -->

> 💡 **Path Selection**: This skill is one implementation path for [Manage distributed database transactions](../../intent/oceanbase-manage-transactions/SKILL.md). If you're unsure which path to take, check the routing skill first.

# OceanBase Transaction Management

## Capabilities Overview

| Sub-capability | Calling Mode | Description |
|----------------|--------------|-------------|
| Manage Distributed Transaction | Synchronous | Manage distributed transactions using DBMS_XA functions. |
| Synchronize In-Doubt Transactions | Synchronous | Synchronize in-doubt transactions using the DIST_TXN_SYNC API. |
| Lock Rows for Update | Synchronous | Use FOR UPDATE to lock selected rows during a transaction. |
| Manage Global Transactions | Synchronous | Perform XA transaction operations including start, end, prepare, commit, and rollback. |
| Recover Transaction Branches | Synchronous | Retrieve and manage transaction branches during recovery scenarios. |
| Handle Transaction Errors | Synchronous | Get error codes and set timeouts for XA transactions. |

## API Calling Patterns

### Authentication
OceanBase transaction management APIs are accessed through SQL/PL interfaces within the database session. No external authentication is required beyond standard database connection credentials.

- Use your existing OceanBase database connection (via OBClient, JDBC, or other SQL clients)
- Ensure your database user has appropriate privileges (e.g., FORCE ANY TRANSACTION for managing other users' transactions)

### Service Endpoint
These APIs are invoked directly within SQL statements or PL/SQL blocks executed against your OceanBase database instance.

- No HTTP endpoints — these are database-internal functions
- Execute via standard SQL clients connected to your OceanBase cluster
- Available in all regions where OceanBase is deployed

### Synchronous Pattern
All transaction management functions follow a synchronous calling pattern:

1. Establish a database connection to your OceanBase instance
2. Execute the DBMS_XA function call as part of a SQL statement or PL/SQL block
3. Process the return value immediately (typically a PLS_INTEGER status code)
4. Handle any errors based on the returned status code

Example flow for a complete XA transaction:
```sql
-- Start transaction branch
DECLARE result PLS_INTEGER;
BEGIN
  result := DBMS_XA.XA_START(xid, DBMS_XA.TMNOFLAGS);
  -- Check result = XA_OK
  
  -- Perform DML operations
  INSERT INTO table1 VALUES (...);
  
  -- End transaction branch
  result := DBMS_XA.XA_END(xid, DBMS_XA.TMSUCCESS);
  -- Check result = XA_OK
  
  -- Prepare transaction
  result := DBMS_XA.XA_PREPARE(xid);
  -- Check result = XA_OK
  
  -- Commit transaction
  result := DBMS_XA.XA_COMMIT(xid, FALSE);
END;
```

## Parameter Reference

### Manage Global Transactions

| Parameter | Type | Required | Default | Constraints | Description |
|-----------|------|----------|---------|-------------|-------------|
| xid | DBMS_XA_XID | Yes | - | - | The global transaction identifier. See DBMS_XA_XID object type for details. |
| onePhase | BOOLEAN | No | FALSE | - | If TRUE, uses one-phase commit protocol. Otherwise, requires prior XA_PREPARE success. |
| flag | PLS_INTEGER | Yes | - | One of: TMSUCCESS, TMSUSPEND, TMJOIN, TMRESUME, TMNOFLAGS | Specifies the action to take after disassociating the session from the transaction branch. |
| seconds | PLS_INTEGER | Yes | 60 | - | The timeout value specifies the maximum amount of time in seconds which a transaction branch may be dissociated from the session before the system automatically terminates the transaction. |

## Code Examples

### Basic XA Transaction Flow - PL/SQL - all

```plsql
DECLARE
  xid DBMS_XA_XID;
  result PLS_INTEGER;
BEGIN
  -- Create XID
  xid := DBMS_XA_XID('global_id_123', 'branch_1');
  
  -- Start transaction
  result := DBMS_XA.XA_START(xid, DBMS_XA.TMNOFLAGS);
  IF result != DBMS_XA.XA_OK THEN
    RAISE_APPLICATION_ERROR(-20001, 'XA_START failed');
  END IF;
  
  -- Perform work
  INSERT INTO orders VALUES (1, 'customer1', SYSDATE);
  
  -- End transaction
  result := DBMS_XA.XA_END(xid, DBMS_XA.TMSUCCESS);
  IF result != DBMS_XA.XA_OK THEN
    RAISE_APPLICATION_ERROR(-20002, 'XA_END failed');
  END IF;
  
  -- Prepare transaction
  result := DBMS_XA.XA_PREPARE(xid);
  IF result != DBMS_XA.XA_OK THEN
    RAISE_APPLICATION_ERROR(-20003, 'XA_PREPARE failed');
  END IF;
  
  -- Commit transaction
  result := DBMS_XA.XA_COMMIT(xid, FALSE);
  IF result != DBMS_XA.XA_OK THEN
    RAISE_APPLICATION_ERROR(-20004, 'XA_COMMIT failed');
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('Transaction completed successfully');
END;
```

### FOR UPDATE Row Locking - SQL - all

```sql
-- Single table row locking
EXPLAIN SELECT * FROM t1 WHERE c1 = 1 FOR UPDATE;

-- Multi-table row locking
EXPLAIN SELECT * FROM t1, t2 WHERE c1 = 1 AND c1 = d1 FOR UPDATE;
```

### Recover In-Doubt Transactions - PL/SQL - all

```plsql
DECLARE
  xid_array DBMS_XA_XID_ARRAY;
BEGIN
  -- Get list of prepared/committed/rolled-back transaction branches
  xid_array := DBMS_XA.XA_RECOVER();
  
  -- Process each transaction branch
  FOR i IN 1..xid_array.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Found transaction: ' || xid_array(i).global_transaction_id);
    
    -- Example: commit recovered transaction
    IF DBMS_XA.XA_COMMIT(xid_array(i), FALSE) = DBMS_XA.XA_OK THEN
      DBMS_OUTPUT.PUT_LINE('Committed transaction successfully');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Failed to commit transaction');
    END IF;
  END LOOP;
END;
```

### Set Transaction Timeout - PL/SQL - all

```plsql
DECLARE
  result PLS_INTEGER;
BEGIN
  -- Set timeout to 120 seconds
  result := DBMS_XA.XA_SETTIMEOUT(120);
  
  IF result = DBMS_XA.XA_OK THEN
    DBMS_OUTPUT.PUT_LINE('Timeout set successfully');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Failed to set timeout, error code: ' || result);
  END IF;
END;
```

### Forget Transaction Branch - PL/SQL - all

```plsql
DECLARE
  xid DBMS_XA_XID;
BEGIN
  -- Initialize xid with appropriate values
  xid := DBMS_XA_XID('global_id', 'branch_qualifier');
  
  -- Call XA_FORGET to inform RM to forget the transaction branch
  IF DBMS_XA.XA_FORGET(xid) = DBMS_XA.XA_OK THEN
    DBMS_OUTPUT.PUT_LINE('Transaction branch forgotten successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Failed to forget transaction branch.');
  END IF;
END;
```

### Get Last XA Error Code - PL/SQL - all

```plsql
DECLARE
  v_error_code PLS_INTEGER;
BEGIN
  v_error_code := DBMS_XA.XA_GETLASTOER;
  DBMS_OUTPUT.PUT_LINE('Last XA Error Code: ' || v_error_code);
END;
```

## Response Format

**Key Fields**:
- `return_value` — The status code returned by XA functions indicating success or failure type
- `DBMS_XA_XID_ARRAY` — Array of transaction identifiers returned by XA_RECOVER

## Error Handling

| Error Code | Description | Recommended Action |
|------------|-------------|-------------------|
| XA_RBBASE | The lower bound of the rollback code. | Check specific rollback reason codes between XA_RBBASE and XA_RBEND |
| XA_RBROLLBACK | The rollback is caused by an unknown reason. | Retry the transaction if appropriate |
| XA_RBCOMMFAIL | The rollback is caused by a communication error. | Check network connectivity and retry |
| XA_RBDEADLOCK | A deadlock is found. | Retry the transaction with modified access patterns |
| XA_RBINTEGRITY | A violation to the integrity of the resources is detected. | Validate data constraints and fix application logic |
| XA_RBOTHER | The resource manager (RM) rolled back a transaction without specifying the reason. | Check RM logs for detailed error information |
| XA_RBPROTO | The resource manager (RM) encountered a protocol error. | Verify XA protocol compliance in your application |
| XA_RBTIMEOUT | The transaction branch took too much time. | Increase timeout settings or optimize transaction duration |
| XA_RBTRANSIENT | Indicates that you can retry the transaction branch. | Implement retry logic with exponential backoff |
| XA_RBEND | The inclusive upper bound of the rollback code. | Not an actual error code - used for range checking |
| XA_NOMIGRATE | The transaction branch may already be complete. | Verify transaction status before proceeding |
| XA_HEURHAZ | The transaction branch may already be complete. | Manual intervention may be required to resolve |
| XA_HEURCOM | The transaction branch may already be committed. | Verify actual commit status in application data |
| XA_HEURRB | The transaction branch may already be rolled back. | Verify actual rollback status in application data |
| XA_HEURMIX | Some transaction branches may already be committed, others may be rolled back. | Requires manual reconciliation of transaction state |
| XA_RETRY | The routine returned with no effect and may be re-issued. | Retry the operation immediately |
| XA_RDONLY | The transaction is read-only and already committed. | No action needed for read-only transactions |
| XA_OK | The execution is normal. | Operation succeeded |
| XAER_ASYNC | An outstanding asynchronous operation exists. | Wait for async operation to complete before proceeding |
| XAER_RMERR | The transaction branch encountered a resource manager error. | Check RM-specific error logs and configuration |
| XAER_NOTA | The XID is invalid. | Verify XID format and ensure it hasn't been discarded |
| XAER_INVAL | One or more parameters passed are invalid. | Validate all parameter values against documented constraints |
| XAER_PROTO | The routine is invoked in an improper context. | Ensure proper transaction state before calling the function |
| XAER_RMFAIL | The resource manager is unavailable. | Check RM availability and retry when available |
| XAER_DUPID | The XID already exists. | Use unique XIDs or properly join existing transactions |
| XAER_OUTSIDE | Resource manager is working outside global transaction. | Ensure session is properly associated with global transaction |
| ORA-00001 | Unique constraint violation. Check for duplicate entries in the transaction log. | Resolve data conflicts before retrying |
| ORA-01001 | Invalid cursor. The cursor used in the XA call is not valid or has been closed. | Reinitialize cursor before making XA calls |

## FAQ

Q: What privileges are required to manage XA transactions?
A: Users need the FORCE ANY TRANSACTION privilege to manage transaction branches created by other users. For managing your own transactions, standard DML privileges are sufficient.

Q: How do I handle in-doubt transactions after a system failure?
A: Use XA_RECOVER to get a list of prepared transaction branches, then use XA_COMMIT, XA_ROLLBACK, or XA_FORGET to resolve them based on your application's requirements.

Q: What's the difference between TMNOFLAGS, TMJOIN, and TMRESUME in XA_START?
A: TMNOFLAGS starts a new transaction branch, TMJOIN joins an existing active branch, and TMRESUME resumes a suspended branch (after XA_END with TMSUSPEND).

Q: Can I use FOR UPDATE with multiple tables?
A: Yes, OceanBase supports MULTI FOR UPDATE for locking rows across multiple tables in a single query.

Q: How do transaction timeouts work in OceanBase?
A: OceanBase has two timeout mechanisms: idle transaction timeout (ob_trx_idle_timeout) and uncommitted transaction timeout (ob_trx_timeout). Additionally, XA_SETTIMEOUT sets the XA-specific timeout for transaction branches.

## Pricing & Billing

### Billing Model
Free - These transaction management functions are included as part of the core OceanBase database functionality and are not billed separately.

### Free Tier
No explicit free tier mentioned; typically included in database licensing.

### Usage Limits
No usage limits specified in documentation.