ACID Transactions

Overview

Every write operation in CoreWrite executes within an ACID transaction. This ensures data consistency, integrity, and reliability across all operations including main record, cascades, and outbox events.

ACID Properties:

• Atomicity - All operations succeed or all fail
• Consistency - Database remains in valid state
• Isolation - Concurrent transactions don't interfere
• Durability - Committed changes persist permanently

Transaction Scope

Single Write Operation

Every POST/PUT/PATCH/DELETE is transactional:

BEGIN TRANSACTION
    ↓
1. Insert/Update/Delete main record
2. Execute cascade operations
3. Insert outbox event
    ↓
COMMIT TRANSACTION
    ↓
Success: All changes persisted
    OR
ROLLBACK TRANSACTION
    ↓
Failure: All changes reverted

CoreWrite Implementation (PHP):

// CoreWrite DataStore.php
public function insert(string $dimension, array $data): array {
    $this->pdo->beginTransaction();

    try {
        // 1. Insert main record
        $recordId = $this->insertMainRecord($dimension, $data);

        // 2. Execute INSERT_CASCADE operations
        $this->executeCascadeInserts($dimension, $recordId, $data);

        // 3. Insert outbox event
        $this->insertOutboxEvent($dimension, $recordId, 'INSERT', $data);

        // 4. Commit transaction (all-or-nothing)
        $this->pdo->commit();

        return ['id' => $recordId, 'status' => 'success'];
    } catch (\Exception $e) {
        // Rollback on ANY error
        $this->pdo->rollBack();
        throw $e;
    }
}

Atomicity

All-or-Nothing Execution

Success case:

Request:

POST /api/v4/core/ORD
{
  "data": {
    "XORD01": "ORD-2025-001",
    "XORD_CUSTOMER_ID": "cust_123",
    "items": [
      { "XORDITEM10": "prd_1", "XORDITEM_QTY": 2 },
      { "XORDITEM10": "prd_2", "XORDITEM_QTY": 1 }
    ]
  }
}

Transaction SQL:

BEGIN TRANSACTION;

-- 1. Insert order
INSERT INTO TB_ANAG_ORD00 (XORD01, XORD_CUSTOMER_ID, TREC, CDATA, OWNER)
VALUES ('ORD-2025-001', 'cust_123', 'N', '20251219160000', 'user@example.com');

-- 2. Insert order items (cascade)
INSERT INTO TB_ANAG_ORDITEM00 (XORDITEM_ORDER_ID, XORDITEM10, XORDITEM_QTY, TREC, CDATA, OWNER)
VALUES (123, 'prd_1', 2, 'N', '20251219160000', 'user@example.com');

INSERT INTO TB_ANAG_ORDITEM00 (XORDITEM_ORDER_ID, XORDITEM10, XORDITEM_QTY, TREC, CDATA, OWNER)
VALUES (123, 'prd_2', 1, 'N', '20251219160000', 'user@example.com');

-- 3. Insert outbox event
INSERT INTO OUTBOX (event_type, aggregate_id, payload, created_at)
VALUES ('OrderCreated', 123, '{"XORD01":"ORD-2025-001",...}', '20251219160000');

COMMIT;  -- ✅ All changes persisted

Result:

• Order created: TB_ANAG_ORD00
• 2 items created: TB_ANAG_ORDITEM00
• Event queued: OUTBOX

Failure case (item 2 fails validation):

BEGIN TRANSACTION;

-- 1. Insert order
INSERT INTO TB_ANAG_ORD00 (...) VALUES (...);  -- ✅ Success

-- 2. Insert item 1
INSERT INTO TB_ANAG_ORDITEM00 (...) VALUES (...);  -- ✅ Success

-- 3. Insert item 2
INSERT INTO TB_ANAG_ORDITEM00 (...) VALUES (...);  -- ❌ Fails (foreign key violation)

ROLLBACK;  -- ⚠️ All changes reverted

Result:

• Order NOT created
• Item 1 NOT created
• Item 2 NOT created
• No outbox event

⚠️ Atomicity ensures no partial data - either complete success or complete failure.

Consistency

Database Constraints Enforced

Foreign keys, unique constraints, and check constraints are validated:

-- Product table constraints
CREATE TABLE TB_ANAG_PRD00 (
    PRD_ID VARCHAR(36) PRIMARY KEY,
    XPRD01 VARCHAR(255) NOT NULL,  -- Name required
    XPRD02 DECIMAL(10,2) NOT NULL CHECK (XPRD02 >= 0),  -- Price non-negative
    XPRD05 VARCHAR(50),
    XPRD03 VARCHAR(50) UNIQUE,  -- Counter unique
    FOREIGN KEY (XPRD05) REFERENCES TB_ANAG_CAT00(CAT_ID)  -- Category exists
);

Violation example:

Request:

POST /api/v4/core/PRD
{
  "data": {
    "XPRD01": "Widget",
    "XPRD02": -10.00,  // ❌ Negative price (CHECK constraint violation)
    "XPRD05": "cat_invalid"  // ❌ Category doesn't exist (FK violation)
  }
}

Transaction:

BEGIN TRANSACTION;

INSERT INTO TB_ANAG_PRD00 (XPRD01, XPRD02, XPRD05, ...)
VALUES ('Widget', -10.00, 'cat_invalid', ...);

-- ❌ CHECK constraint failed: XPRD02 >= 0
-- ❌ Foreign key constraint failed: cat_invalid not in TB_ANAG_CAT00

ROLLBACK;  -- Database remains consistent

Response:

{
  "error": "ConstraintViolationError",
  "message": "Check constraint violation: XPRD02 must be >= 0",
  "code": "CHECK_CONSTRAINT_VIOLATION",
  "status": 400
}

Isolation

Concurrent Transaction Handling

Isolation level: READ COMMITTED (default):

Transaction A (User 1)        Transaction B (User 2)
    |                             |
BEGIN TRANSACTION            BEGIN TRANSACTION
    |                             |
UPDATE product                    |
SET XPRD02 = 89.99               |
WHERE PRD_ID = 123               |
    |                             |
    |                        UPDATE product
    |                        SET XPRD02 = 79.99
    |                        WHERE PRD_ID = 123
    |                             |
    |                        [BLOCKED - waiting for A]
    |                             |
COMMIT                            |
    |                             |
    |                        [UNBLOCKED - proceeds]
    |                             |
    |                        COMMIT

Result: Transaction B sees A's committed changes (89.99), then overwrites with 79.99 (last write wins).

Row-Level Locking

CoreWrite uses row-level locks (SELECT FOR UPDATE):

// CoreWrite DataStore.php
public function update(string $dimension, int $id, array $data): array {
    $this->pdo->beginTransaction();

    try {
        // 1. Lock row for update
        $current = $this->pdo->query(
            "SELECT * FROM TB_ANAG_{$dimension}00 WHERE {$dimension}_ID = :id FOR UPDATE",
            ['id' => $id]
        )->fetch();

        if (!$current) {
            throw new NotFoundException("Record not found");
        }

        // 2. Update record (no one else can modify until commit)
        $this->updateRecord($dimension, $id, $data);

        // 3. Execute cascades
        $this->executeCascadeUpdates($dimension, $id, $data);

        // 4. Insert outbox event
        $this->insertOutboxEvent($dimension, $id, 'UPDATE', $data);

        // 5. Commit (releases lock)
        $this->pdo->commit();

        return ['id' => $id, 'status' => 'success'];
    } catch (\Exception $e) {
        $this->pdo->rollBack();
        throw $e;
    }
}

Benefits:

• Prevents lost updates
• Ensures serializable modifications
• Other transactions wait (no overwrite)

Durability

Write-Ahead Logging (WAL)

Database WAL ensures committed changes persist:

Transaction committed
    ↓
1. Write changes to WAL (write-ahead log)
2. WAL synced to disk (fsync)
3. Changes applied to data files (async)
    ↓
Power failure or crash
    ↓
Database recovery from WAL
    ↓
All committed transactions replayed

Durability guarantee:

• Committed changes survive crashes
• Power failures don't lose data
• Database can recover to consistent state

Outbox Pattern Durability

Outbox events are part of transaction:

BEGIN TRANSACTION;

-- 1. Write main record
INSERT INTO TB_ANAG_PRD00 (...) VALUES (...);

-- 2. Write outbox event (same transaction)
INSERT INTO OUTBOX (event_type, aggregate_id, payload, created_at)
VALUES ('ProductCreated', 123, '...', '20251219160000');

COMMIT;  -- Both persisted together

Benefit: Event publishing is guaranteed (at-least-once delivery).

Transaction Lifecycle

Complete Transaction Example

Request:

POST /api/v4/core/PRD
{
  "data": {
    "XPRD01": "Widget Pro",
    "XPRD02": 99.99,
    "XPRD05": "cat_electronics"
  }
}

Transaction Steps:

// 1. Start transaction
$pdo->beginTransaction();

// 2. Generate UUID for primary key
$productId = Uuid::uuid4();

// 3. Generate counter (TB_COUNTER)
$counter = $this->generateCounter('PRD', 'XPRD03');  // "PRD-2025-001"

// 4. Set audit fields
$data['PRD_ID'] = $productId;
$data['XPRD03'] = $counter;
$data['TREC'] = 'N';
$data['CDATA'] = date('YmdHis');
$data['OWNER'] = $currentUser;

// 5. Insert main record
$sql = "INSERT INTO TB_ANAG_PRD00 (PRD_ID, XPRD01, XPRD02, XPRD03, XPRD05, TREC, CDATA, OWNER)
        VALUES (:PRD_ID, :XPRD01, :XPRD02, :XPRD03, :XPRD05, :TREC, :CDATA, :OWNER)";
$pdo->execute($sql, $data);

// 6. Execute INSERT_CASCADE (if any)
foreach ($cascades as $cascade) {
    $this->executeCascade($cascade, $productId, $data);
}

// 7. Insert outbox event
$event = [
    'event_type' => 'ProductCreated',
    'aggregate_id' => $productId,
    'aggregate_type' => 'PRD',
    'payload' => json_encode($data),
    'created_at' => date('YmdHis')
];
$pdo->execute("INSERT INTO OUTBOX (...) VALUES (...)", $event);

// 8. Commit transaction
$pdo->commit();

// ✅ All changes persisted atomically

Error Handling and Rollback

Automatic Rollback

Any exception triggers rollback:

public function insert(string $dimension, array $data): array {
    $this->pdo->beginTransaction();

    try {
        $recordId = $this->insertMainRecord($dimension, $data);
        $this->executeCascades($dimension, $recordId, $data);
        $this->insertOutboxEvent($dimension, $recordId, 'INSERT', $data);

        $this->pdo->commit();
        return ['id' => $recordId, 'status' => 'success'];
    } catch (ValidationException $e) {
        $this->pdo->rollBack();  // Validation failed
        throw $e;
    } catch (ConstraintViolationException $e) {
        $this->pdo->rollBack();  // DB constraint violated
        throw $e;
    } catch (\PDOException $e) {
        $this->pdo->rollBack();  // Database error
        throw new DatabaseException('Database operation failed', 0, $e);
    } catch (\Exception $e) {
        $this->pdo->rollBack();  // Any other error
        throw $e;
    }
}

Error types that trigger rollback:

1. Validation errors (required field missing)
2. Constraint violations (foreign key, unique, check)
3. Database errors (connection lost, deadlock)
4. Business logic errors (custom validation)
5. Cascade failures
6. Any unexpected exceptions

Deadlock Detection and Retry

Deadlock Scenario

Transaction A                Transaction B
    |                            |
BEGIN                        BEGIN
    |                            |
UPDATE product 123               |
(locks product 123)              |
    |                            |
    |                       UPDATE product 456
    |                       (locks product 456)
    |                            |
UPDATE product 456               |
(waits for B's lock)             |
    |                            |
    |                       UPDATE product 123
    |                       (waits for A's lock)
    |                            |
    |                            |
[DEADLOCK DETECTED]              |
    ↓                            |
ROLLBACK                         |
    |                            |
    |                       [CONTINUES]
    |                            |
    |                       COMMIT

CoreWrite deadlock handling:

public function insertWithRetry(string $dimension, array $data, int $maxRetries = 3): array {
    $attempt = 0;

    while ($attempt < $maxRetries) {
        try {
            return $this->insert($dimension, $data);
        } catch (\PDOException $e) {
            // Deadlock error code (MySQL: 1213, Oracle: ORA-00060)
            if ($this->isDeadlock($e)) {
                $attempt++;
                if ($attempt >= $maxRetries) {
                    throw new DeadlockException('Transaction failed after retries', 0, $e);
                }
                // Exponential backoff
                usleep(pow(2, $attempt) * 100000);  // 200ms, 400ms, 800ms
                continue;
            }
            throw $e;
        }
    }
}

Best Practices

✅ DO:

Trust transaction management:

// ✅ Good - let CoreWrite handle transactions
await createProduct(data);

Keep transactions short:

// ✅ Good - quick transaction
BEGIN TRANSACTION;
INSERT INTO ...;
INSERT INTO OUTBOX ...;
COMMIT;

Handle transaction failures gracefully:

// ✅ Good - retry on failure
try {
  await createProduct(data);
} catch (error) {
  if (error.code === 'DEADLOCK') {
    await retryWithBackoff(() => createProduct(data));
  }
}

❌ DON'T:

Don't try to manage transactions manually:

// ❌ Bad - CoreWrite handles this
$pdo->beginTransaction();
// ... CoreWrite operations ...
$pdo->commit();

Don't perform long operations in transactions:

// ❌ Bad - locks held too long
BEGIN TRANSACTION;
INSERT INTO ...;
sleep(10);  // External API call, file I/O, etc.
COMMIT;

Don't ignore transaction errors:

// ❌ Bad - swallow errors
try {
  await createProduct(data);
} catch (error) {
  // Ignore
}

// ✅ Good - handle appropriately
try {
  await createProduct(data);
} catch (error) {
  logError(error);
  notifyUser(error);
}

Summary

• ✅ Every write operation is ACID transactional
• ✅ Atomicity: All operations succeed or all fail (no partial writes)
• ✅ Consistency: Database constraints enforced
• ✅ Isolation: Row-level locking prevents concurrent conflicts
• ✅ Durability: Committed changes persist permanently
• ✅ Automatic rollback on any error
• ✅ Outbox event included in transaction (at-least-once delivery)

Key Takeaways:

1. CoreWrite manages transactions automatically
2. Main record + cascades + outbox are atomic
3. Row-level locking prevents lost updates
4. Rollback on any validation or database error
5. Deadlock detection with automatic retry
6. Committed changes survive crashes (WAL)

Transaction Guarantees:

Property	Guarantee
Atomicity	All-or-nothing execution
Consistency	Constraints enforced
Isolation	READ COMMITTED, row locks
Durability	WAL ensures persistence

Next: Cascade Operations →

Related Concepts

• Cascade Operations - INSERT/UPDATE/DELETE_CASCADE
• Outbox Pattern - Event sourcing with transactions
• Validation - Multi-level validation