---
Title: Manage transactions
URL Source: https://company-skill.com/p/oceanbase/oceanbase-manage-transactions
Language: en
Description: You need to coordinate data changes across multiple operations while ensuring consistency, isolation, and recoverability in OceanBase. This includes locking specific rows, executing cross-resource…
---

# Manage transactions

Part of **OceanBase**. Route queries via `POST https://company-skill.com/api/route`.

## What You Want to Do

You need to coordinate data changes across multiple operations while ensuring consistency, isolation, and recoverability in OceanBase. This includes locking specific rows, executing cross-resource global transactions, or handling failures gracefully.

**Typical User Questions**:
- How to handle in-doubt transactions in OceanBase?
- How to implement global transaction coordination?

## Decision Tree

Pick the best path for your situation:

- **If** you require strict ACID compliance across multiple resource managers using two-phase commit → Use DBMS_XA XA (go to *oceanbase/oceanbase-transaction*)
- **If** you only need to prevent concurrent writes on selected rows within a single transaction → Use FOR UPDATE (go to *oceanbase/oceanbase-sql*)
- **If** your transaction has already failed and you need to diagnose errors like missing data or constraint violations → Use (go to *oceanbase/oceanbase-errorhandling*)
- **Otherwise (default)** → Start with **** if you're debugging; otherwise, use ** FOR UPDATE ** for simple concurrency control unless you explicitly need distributed XA semantics.

## Path Comparison

| Path | Best For | Complexity | Code Required | Automation | Key Fact | Detail Skill |
|------|----------|------------|---------------|------------|----------|-------------|
| DBMS_XA XA | ACID | high | Yes | Yes | Free - These transaction management functions are included as part of the core OceanBase database functionality and are not billed separately. | `oceanbase/api/oceanbase-transaction` |
| FOR UPDATE | low | Yes | Yes | Maximum 10 queries per second rate limit in regions cn-hangzhou, cn-shanghai, cn-beijing | `oceanbase/api/oceanbase-sql` |
| Console / Dashboard | medium | Yes | Yes | Free - All database error handling operations via the console are included at no additional cost. | `oceanbase/guide/oceanbase-errorhandling` |

## Path Details

### Path 1: DBMS_XA XA 

**Best For**: ACID 

**Brief Description**: OceanBase’s `DBMS_XA` package implements the XA/Open standard for managing global transaction branches across multiple resource managers. It provides explicit control over the two-phase commit protocol via procedures like `XA_START`, `XA_END`, `XA_PREPARE`, `XA_COMMIT`, and `XA_ROLLBACK`. The `XA_RECOVER` function enables resolution of in-doubt transactions after failures.

**Key technical facts**:
- Billing: Free - These transaction management functions are included as part of the core OceanBase database functionality and are not billed separately.

**When to Use**:
- Application requires strict ACID compliance across multiple resource managers
- Need to recover in-doubt transactions after system failure using XA_RECOVER
- Working in financial or other strong-consistency scenarios
- Already using XA-compatible resource managers

**When NOT to Use**:
- Only need row-level locking within a single transaction (use FOR UPDATE instead)
- Want simple automatic transaction handling without manual XA protocol steps
- Don't require cross-resource distributed transactions

**Known Limitations**:
- Requires explicit PL/SQL code to manage transaction lifecycle (start, end, prepare, commit)
- Must handle specific XA error codes like XAER_PROTO, XA_RBDEADLOCK manually
- Transaction branch ID (XID) has strict format: gtrid max 64 bytes, bqual max 64 bytes
- XA_SETTIMEOUT timeout value is in seconds, not microseconds
- XA_COMMIT with onePhase=TRUE skips prepare phase, reducing safety

### Path 2: FOR UPDATE 

**Brief Description**: OceanBase supports `SELECT ... FOR UPDATE` to place exclusive locks on queried rows, blocking other transactions from performing concurrent writes until the current transaction ends. It also supports `MULTI FOR UPDATE` for multi-table queries. This mechanism operates entirely within a single database session and does not involve distributed coordination.

**Key technical facts**:
- Billing: Per-request billing model where each SQL query execution is charged based on input and output operations.
- Max concurrency: 10
- Regions available: cn-hangzhou, cn-shanghai, cn-beijing

**When to Use**:
- Need simple row-level locking to prevent concurrent modifications in a single table
- Implementing basic business logic that requires exclusive access to specific records
- Working with single-partition or standalone multi-partition transactions
- Want low-complexity solution without XA protocol overhead

**When NOT to Use**:
- Require strict ACID guarantees across multiple resource managers (use DBMS_XA instead)
- Need to coordinate transactions across different databases or systems
- Working in financial or strong-consistency scenarios requiring two-phase commit

**Known Limitations**:
- Only provides row-level locking within a single transaction, not cross-resource coordination
- Does not guarantee ACID across multiple databases or systems
- Limited to preventing concurrent writes on selected rows, not full distributed transaction semantics
- Maximum 10 queries per second rate limit
- No built-in recovery mechanism for failed transactions

### Path 3: Console / Dashboard
**Brief Description**: OceanBase provides Oracle-compatible PL/SQL exception handling, including predefined exceptions like `NO_DATA_FOUND` and `TOO_MANY_ROWS`, system exceptions bound via `PRAGMA EXCEPTION_INIT`, and user-defined errors raised with `RAISE_APPLICATION_ERROR`. Developers can inspect errors using `SQLCODE` and `SQLERRM`, and output debug info with `DBMS_OUTPUT.PUT_LINE`.

**Key technical facts**:
- Billing: Free - All database error handling operations via the console are included at no additional cost.

**When to Use**:
- Need to handle specific database exceptions like missing data or duplicate keys
- Want to provide custom error messages for application-level errors
- Working with Oracle-compatible PL/SQL code
- Debugging transaction errors using SQLCODE and SQLERRM

**When NOT to Use**:
- Need automatic error recovery without custom code
- Working only with simple SQL queries without PL/SQL blocks
- Require cross-system error handling beyond database exceptions

**Known Limitations**:
- Only works within PL/SQL execution context (anonymous blocks, procedures, functions)
- Cannot be used in standalone SQL statements
- Requires Oracle-compatible mode for DBMS_OUTPUT and exception handling
- Custom error messages limited to RAISE_APPLICATION_ERROR with error code -20999
- System error codes must be known in advance to bind with PRAGMA EXCEPTION_INIT

## FAQ

Q: Which path should I start with?
A: If you’re building new logic and only need to avoid concurrent updates on specific rows, start with ** FOR UPDATE **. If you’re in finance or integrating with external transactional systems, start with ** DBMS_XA XA **. If your code is already failing, begin with ****.

Q: What if I need to coordinate updates across two different databases but used SELECT ... FOR UPDATE?
A: You’ll hit a fundamental limitation: `SELECT ... FOR UPDATE` only locks rows within a single OceanBase transaction and cannot enforce atomicity across external systems, leading to potential data inconsistency.

Q: What if I try to handle an XA protocol violation like XAER_PROTO using only the error handling skill without DBMS_XA context?
A: You’ll miss critical recovery steps—`PRAGMA EXCEPTION_INIT` can catch the error code, but without calling `XA_RECOVER` or understanding XID state, you cannot resolve in-doubt transactions.

Q: Can I use RAISE_APPLICATION_ERROR to simulate XA rollback behavior?
A: No—`RAISE_APPLICATION_ERROR` only terminates the current PL/SQL block and rolls back its own transaction; it does not interact with the XA coordinator or affect other branches in a global transaction.

Q: Does FOR UPDATE work in all OceanBase regions?
A: It’s documented as available in `cn-hangzhou`, `cn-shanghai`, and `cn-beijing` with a 10 QPS limit; other regions may have different behavior—consult the detail skill for updates.

Q: Are NO_DATA_FOUND and TOO_MANY_ROWS automatically raised during XA transactions?
A: Yes—if your XA-managed DML triggers these conditions inside a PL/SQL block, they behave like any other exception and can be caught using standard handlers alongside `SQLCODE` and `SQLERRM`.

## Related queries

manage distributed transactions, handle XA transactions, resolve in-doubt transactions, lock rows to prevent concurrent writes, recover failed global transactions, implement two-phase commit, use DBMS_XA, handle transaction errors, catch NO_DATA_FOUND, deal with TOO_MANY_ROWS, use SELECT FOR UPDATE

---
Part of [OceanBase](https://company-skill.com/p/oceanbase.md) · https://company-skill.com/llms.txt