Transaction Management

/**
 * Commits the current transaction
 * @returns Promise that resolves when transaction is committed
 */
commit(): Promise<void>;

/**
 * Rolls back the current transaction
 * @returns Promise that resolves when transaction is rolled back
 */
rollback(): Promise<void>;

const oracledb = require('oracledb');

// Manual transaction control
oracledb.autoCommit = false; // Disable auto-commit

const connection = await oracledb.getConnection(config);

try {
  // Execute multiple related operations
  await connection.execute(
    'INSERT INTO orders (order_id, customer_id, total) VALUES (:1, :2, :3)',
    [1001, 'CUST001', 199.99]
  );
  
  await connection.execute(
    'INSERT INTO order_items (order_id, product_id, quantity) VALUES (:1, :2, :3)',
    [1001, 'PROD001', 2]
  );
  
  await connection.execute(
    'UPDATE inventory SET quantity = quantity - :1 WHERE product_id = :2',
    [2, 'PROD001']
  );
  
  // All operations successful - commit
  await connection.commit();
  console.log('Transaction committed successfully');
  
} catch (error) {
  // Error occurred - rollback
  await connection.rollback();
  console.error('Transaction rolled back:', error.message);
  throw error;
  
} finally {
  await connection.close();
}

/**
 * Begins a distributed transaction
 * @param xid - Transaction identifier
 * @param flags - Optional transaction flags
 * @param timeout - Optional timeout in seconds
 * @returns Promise that resolves when transaction begins
 */
tpcBegin(xid: string, flags?: number, timeout?: number): Promise<void>;

/**
 * Ends a distributed transaction branch
 * @param xid - Transaction identifier
 * @param flags - Optional end flags
 * @returns Promise that resolves when transaction branch ends
 */
tpcEnd(xid: string, flags?: number): Promise<void>;

/**
 * Prepares a distributed transaction for commit
 * @param xid - Transaction identifier
 * @returns Promise resolving to boolean indicating if transaction is read-only
 */
tpcPrepare(xid: string): Promise<boolean>;

/**
 * Commits a distributed transaction
 * @param xid - Transaction identifier
 * @param onePhase - Whether to use one-phase commit
 * @returns Promise resolving to boolean indicating success
 */
tpcCommit(xid: string, onePhase?: boolean): Promise<boolean>;

/**
 * Rolls back a distributed transaction
 * @param xid - Transaction identifier
 * @returns Promise that resolves when transaction is rolled back
 */
tpcRollback(xid: string): Promise<void>;

/**
 * Forgets a distributed transaction
 * @param xid - Transaction identifier
 * @returns Promise that resolves when transaction is forgotten
 */
tpcForget(xid: string): Promise<void>;

/**
 * Recovers distributed transactions
 * @param flags - Recovery flags
 * @returns Promise resolving to array of transaction identifiers
 */
tpcRecover(flags?: number): Promise<string[]>;

// Distributed transaction example with two connections
const connection1 = await oracledb.getConnection(config1);
const connection2 = await oracledb.getConnection(config2);

const xid = 'distributed-tx-' + Date.now();

try {
  // Begin distributed transaction on both connections
  await connection1.tpcBegin(xid, oracledb.TPC_BEGIN_NEW);
  await connection2.tpcBegin(xid, oracledb.TPC_BEGIN_NEW);
  
  // Perform operations on both databases
  await connection1.execute(
    'UPDATE accounts SET balance = balance - :amount WHERE account_id = :id',
    { amount: 1000, id: 'ACC001' }
  );
  
  await connection2.execute(
    'UPDATE accounts SET balance = balance + :amount WHERE account_id = :id',
    { amount: 1000, id: 'ACC002' }
  );
  
  // End transaction branches
  await connection1.tpcEnd(xid);
  await connection2.tpcEnd(xid);
  
  // Prepare both branches
  const readOnly1 = await connection1.tpcPrepare(xid);
  const readOnly2 = await connection2.tpcPrepare(xid);
  
  // Commit both branches
  await connection1.tpcCommit(xid);
  await connection2.tpcCommit(xid);
  
  console.log('Distributed transaction committed successfully');
  
} catch (error) {
  // Rollback both branches on error
  try {
    await connection1.tpcRollback(xid);
    await connection2.tpcRollback(xid);
  } catch (rollbackError) {
    console.error('Rollback error:', rollbackError);
  }
  
  console.error('Distributed transaction failed:', error.message);
  throw error;
  
} finally {
  await connection1.close();
  await connection2.close();
}

/**
 * Begins a sessionless transaction
 * @param options - Transaction options
 * @returns Promise resolving to transaction ID
 */
beginSessionlessTransaction(options?: SessionlessTransactionOptions): Promise<Buffer>;

/**
 * Resumes a sessionless transaction
 * @param transactionId - Transaction ID from beginSessionlessTransaction
 * @param options - Resume options
 * @returns Promise that resolves when transaction is resumed
 */
resumeSessionlessTransaction(transactionId: Buffer, options?: SessionlessTransactionOptions): Promise<void>;

interface SessionlessTransactionOptions {
  timeout?: number;
}

// Begin sessionless transaction
const connection1 = await oracledb.getConnection(config);

const transactionId = await connection1.beginSessionlessTransaction({
  timeout: 300 // 5 minutes
});

// Perform some operations
await connection1.execute(
  'INSERT INTO temp_data (id, data) VALUES (:1, :2)',
  [1, 'Transaction data']
);

// Close first connection
await connection1.close();

// Later, resume transaction with different connection
const connection2 = await oracledb.getConnection(config);

await connection2.resumeSessionlessTransaction(transactionId);

// Continue transaction
await connection2.execute(
  'UPDATE temp_data SET data = :1 WHERE id = :2',
  ['Updated data', 1]
);

// Commit the sessionless transaction
await connection2.commit();
await connection2.close();

console.log('Sessionless transaction completed');

interface Connection {
  // Transaction state (read-only)
  transactionInProgress: boolean;
}

const connection = await oracledb.getConnection(config);

console.log('Transaction in progress:', connection.transactionInProgress); // false

// Start a transaction
await connection.execute('INSERT INTO test (id) VALUES (1)');

console.log('Transaction in progress:', connection.transactionInProgress); // true

// Commit transaction
await connection.commit();

console.log('Transaction in progress:', connection.transactionInProgress); // false

// Global auto-commit setting
oracledb.autoCommit: boolean;

// Per-execute auto-commit
interface ExecuteOptions {
  autoCommit?: boolean;
}

// Global configuration
oracledb.autoCommit = true; // All operations auto-commit

// Per-operation override
await connection.execute(
  'INSERT INTO logs (message) VALUES (:1)',
  ['Debug message'],
  { autoCommit: false } // Don't auto-commit this operation
);

// Batch operations with manual commit
oracledb.autoCommit = false;

await connection.execute('INSERT INTO batch (id) VALUES (1)');
await connection.execute('INSERT INTO batch (id) VALUES (2)');
await connection.execute('INSERT INTO batch (id) VALUES (3)');

// Commit all at once
await connection.commit();

// Or use auto-commit for the entire batch
await connection.executeMany(
  'INSERT INTO batch (id, data) VALUES (:1, :2)',
  [[1, 'data1'], [2, 'data2'], [3, 'data3']],
  { autoCommit: true }
);

// TPC/XA begin flags
const TPC_BEGIN_JOIN = 0x00000002;
const TPC_BEGIN_NEW = 0x00000001;
const TPC_BEGIN_PROMOTE = 0x00000008;
const TPC_BEGIN_RESUME = 0x00000004;

// TPC/XA end flags
const TPC_END_NORMAL = 0;
const TPC_END_SUSPEND = 0x00100000;

// Join existing distributed transaction
await connection.tpcBegin(xid, oracledb.TPC_BEGIN_JOIN);

// Suspend transaction branch
await connection.tpcEnd(xid, oracledb.TPC_END_SUSPEND);

// Resume suspended transaction branch
await connection.tpcBegin(xid, oracledb.TPC_BEGIN_RESUME);

// Normal transaction end
await connection.tpcEnd(xid, oracledb.TPC_END_NORMAL);

// Robust transaction with retry logic
async function executeWithRetry(connection, operations, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      // Execute all operations
      for (const operation of operations) {
        await connection.execute(operation.sql, operation.binds);
      }
      
      // Commit if all successful
      await connection.commit();
      console.log('Transaction completed successfully');
      return;
      
    } catch (error) {
      await connection.rollback();
      
      if (attempt < maxRetries && isRetryableError(error)) {
        console.log(`Transaction attempt ${attempt} failed, retrying...`);
        await new Promise(resolve => setTimeout(resolve, 1000 * attempt));
        continue;
      }
      
      console.error(`Transaction failed after ${attempt} attempts`);
      throw error;
    }
  }
}

function isRetryableError(error) {
  // Check for specific retryable Oracle errors
  return error.message.includes('ORA-00060') || // Deadlock
         error.message.includes('ORA-08177'); // Serialization failure
}

// Deadlock handling
try {
  await connection.execute('UPDATE table1 SET col1 = :1 WHERE id = :2', [val1, id1]);
  await connection.execute('UPDATE table2 SET col2 = :1 WHERE id = :2', [val2, id2]);
  await connection.commit();
} catch (error) {
  await connection.rollback();
  
  if (error.message.includes('ORA-00060')) { // Deadlock detected
    console.log('Deadlock detected, retrying transaction...');
    // Implement retry logic with exponential backoff
    await new Promise(resolve => setTimeout(resolve, Math.random() * 1000));
    // Retry transaction
  } else {
    throw error;
  }
}