view release on metacpan or  search on metacpan

docs/EN.AmberDB_User-Guide.md  view on Meta::CPAN

        { blk => 10, type => 'num' },  # Block 10: Price (Numeric sorting)
        { blk => 12, type => 'date' }, # Block 12: Timestamp sorting (YYYYMMDDHHMMSS)
    ],
}
```

#### 6.4.2 Using Sort in Query Methods
Pass the `sort` option to `read_all`, `field_fetch`, or `search_table` to retrieve sorted datasets immediately:

```perl
# 1. Default Direction: Descending / Highest First (DESC: 99->0, Z->A)
my @products = $adb->read_all("catalog_product", { sort => 10 });
my @products = $adb->read_all("catalog_product", { sort => { blk => 10 } });

# 2. Reverse Direction: Ascending / Lowest First (ASC: 0->99, A->Z)
my @products = $adb->read_all("catalog_product", { sort => -10 });
my @products = $adb->read_all("catalog_product", { sort => { blk => 10, reverse => 1 } });

# 3. Primary Key (ID) Ascending Order:
my @products = $adb->read_all("catalog_product", { sort => { reverse => 1 } }); # 1..N oldest first

# 4. Sorting with field_fetch and search_table:
my @cat_items        = $adb->field_fetch("catalog_product", 1, "electronics", { sort => { blk => 10, reverse => 1 } });
my ($count, @search) = $adb->search_table("catalog_product", "headphone", { offset => 0, limit => 20, sort => -10 });
```

---

## 7. Transaction Safety, ACID Guarantees, and Crash Recovery (Transactions)

`AmberDB::Transact` provides full **ACID-compliant transactions** and **Strict Two-Phase Locking (Strict 2PL)** concurrency control for multi-table updates (e.g., creating an order, updating inventory, and charging accounts).

### 7.1 Transactional Integrity & The Single Transaction Spine

In modern e-commerce and enterprise workflows, a single high-level user action (such as "Complete Checkout") triggers an interdependent semantic operation chain spanning multiple tables and sub-systems:

```text
Checkout Operation Chain:
 ├─ Order Confirmation (creating entry in orders table)
 ├─ Cart Cleared (purging items from cart table)
 ├─ Customer Account (balance deduction or card charge record)
 ├─ Company Account (revenue entry in general ledger)
 ├─ Stock Inventory (deducting counts in catalog_product table)
 └─ Supplier Dispatch (writing work item to supplier_queue table)
```

These operations are **semantically coupled and mutually dependent**. If one operation fails while the others persist, the database falls into an inconsistent state:
- If the customer's payment is processed and the order record is created, but inventory deduction fails or crashes;
- Or if stock is deducted and the cart is emptied, but the revenue entry fails to record;

the system state becomes corrupted. To eliminate these anomalies, the entire sequence must be unified within a **single transaction spine (`transact_start` $\rightarrow$ `transact_end`)**. If any step encounters an error or if the process crashes, Am...

### 7.2 ACID Guarantees in AmberDB

AmberDB guarantees the four classical ACID properties through embedded flat-file database mechanics:

| ACID Property | Implementation Mechanism & Guarantees |
| :--- | :--- |
| **Atomicity** | **Disk-Backed Undo-Journaling:** When `transact_start()` is called, a microsecond-stamped `.txn` journal is created. Every `insert_id`, `modify_id`, and `delete_id` call appends reverse undo instructions. If a critical base error oc...
| **Consistency** | **Schema, Index, and State Integrity:** Inbound records are validated against schema field rules, data types, and byte limits. Primary keys (`autoid`), inverted word indexes, columnar facets, and URL slugs are synchronized in real...
| **Isolation** | **Strict Two-Phase Locking (Strict 2PL):** Every record modified within an active transaction acquires an exclusive OS-level lock (`flock LOCK_EX`). Locks are held throughout the entire transaction duration, preventing concurrent wo...
| **Durability** | **Synchronous Journaling & Crash Recovery (`transact_recover`):** All journal writes invoke `$fh->flush`. When configured with `cfg => { txn_sync => 1 }`, AmberDB triggers OS/kernel `fsync` (`$fh->sync`) and Berkeley DB cache flush...

> **Architectural Note: Batch ETL Imports vs. Business Transactions**  
> Methods such as `insert_list`, `modify_list`, and `delete_list` are specialized for high-throughput batch imports (e.g., ingesting large XML/JSON product catalogs). Since list records are typically independent entities without cross-dependencies, d...

### 7.3 Transaction Workflow

In the public API, transaction workflows are driven by 3 primary methods:

1. **`transact_start()`**: Opens a microsecond-stamped undo journal (`txn_*`) in `$dbase_dir/journal/` and recovers any orphaned transactions left by dead processes (`transact_recover`).
2. **CRUD Operations & `transact_error($context, $message)`**: `insert_id`, `modify_id`, `delete_id` write updates to the base `.db` file, acquire record write locks (`flock`), and record reverse undo entries in the journal. If a business logic const...
3. **`transact_end()`**: Finalizes and commits the transaction if everything proceeded normally without errors (`status => "commit"`). If an unhandled underlying database error occurred, it executes an automatic LIFO rollback (`status => "rollback"`)...

> [!NOTE]
> `transact_commit()` and `transact_rollback()` are internal engine methods executed automatically by `transact_end()` and `transact_error()`. Application code should signal business rule violations using `transact_error()`, and conclude normal succe...

### 7.4 Practical Example: Checkout & Inventory Transaction

```perl
# 1. Start Transaction
$adb->transact_start();

my $product_id = 42;
my $quantity   = 2;
my $user_id    = 1001;

# Read product and check inventory
my @product = $adb->read_id("catalog_product", $product_id);
my $current_stock = $product[8]; # Block 8 = Stock count

if ($current_stock < $quantity) {
    # Insufficient stock: report transaction error (transact_end will trigger automatic rollback)
    $adb->transact_error("catalog_product", "Insufficient stock ($current_stock < $quantity)");
} else {
    # Deduct stock and update product (@product[0] contains $product_id)
    $product[8] -= $quantity;
    $adb->modify_id("catalog_product", @product);

    # Create order record
    my @order = ( $user_id, $product_id, $quantity, time(), "confirmed" );
    my $order_id = $adb->insert_id("orders", undef, @order);
}

# Finalize transaction (commits if clean, automatically rolls back on error)
my $res = $adb->transact_end();

if ($res->{status} eq "commit") {
    print "Order placed successfully and stock deducted!\n";
} else {
    warn "Transaction aborted! All changes were automatically rolled back.\n";
}
```

### 7.5 Durability and Crash Recovery

- **IO::Handle Buffer Flushing & Sync:** Every journal entry is immediately flushed with `$fh->flush`. When configured with `cfg => { txn_sync => 1 }`, AmberDB enforces physical OS/disk-level synchronization (`$fh->sync` / `fsync`).
- **`flock`-Based Ownership:** Active transactions hold an exclusive non-blocking lock (`LOCK_EX | LOCK_NB`) on their `.txn` file. If a process crashes unexpectedly, the lock is automatically released by the operating system.
- **Orphan Recovery (`transact_recover`):** If a worker process terminates abruptly, stale `.txn` files in `txn/` are scanned. By verifying that the file lock has dropped and the process is no longer active, the journal is safely rolled back to resto...

### 7.6 Core Architectural Philosophy: Authoritative Data vs. Rebuildable Indexes