# Transaction Support in AbsurderSQL

AbsurderSQL provides **full transactional support** for SQLite operations with IndexedDB persistence in WASM environments. This document outlines the transactional capabilities, limitations, and usage patterns.

## Supported Features

### 1. **Explicit Transactions**
- `BEGIN TRANSACTION` / `BEGIN` - Start a new transaction
- `COMMIT` - Commit all changes made within the transaction
- `ROLLBACK` - Discard all changes made within the transaction

### 2. **Implicit Transactions**
- Single statements (INSERT, UPDATE, DELETE) are automatically wrapped in implicit transactions
- Changes are automatically committed after successful execution

### 3. **Transaction Persistence**  
- Committed transactions (via COMMIT) are in SQLite's in-memory state
- Data persists to IndexedDB only after calling `sync()`
- Changes survive browser restarts after successful sync

### 4. **Crash Consistency**
- Uncommitted changes are not visible after crash (in-memory only)
- Data persisted via sync() survives crashes and restarts
- Must call sync() before page unload to ensure durability

## Usage Examples

### JavaScript API (Recommended)

#### Explicit Transaction with Commit
```javascript
import init, { Database } from '@npiesco/absurder-sql';

await init();
const db = await Database.newDatabase('myapp');

// Explicit transaction
await db.execute('BEGIN TRANSACTION');
await db.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
await db.execute("INSERT INTO users (name) VALUES ('Alice')");
await db.execute("INSERT INTO users (name) VALUES ('Bob')");
await db.execute('COMMIT'); // All changes committed to SQLite

// Persist to IndexedDB
await db.sync();

await db.close();
```

#### Transaction Rollback
```javascript
await db.execute('BEGIN TRANSACTION');
await db.execute("INSERT INTO users (name) VALUES ('Charlie')");
await db.execute("INSERT INTO users (name) VALUES ('Dave')");
await db.execute('ROLLBACK'); // All changes discarded
```

#### Implicit Transaction
```javascript
// Each statement is automatically wrapped in a transaction by SQLite
await db.execute("INSERT INTO users (name) VALUES ('Eve')"); // Auto-committed
await db.sync(); // Persist to IndexedDB
```

## Architecture

### Transaction Layers

1. **SQLite Transaction Manager**
   - Handles BEGIN/COMMIT/ROLLBACK semantics
   - Manages transaction isolation and ACID properties
   - Controls journal file operations

2. **IndexedDB VFS Layer**
   - Translates SQLite file operations to IndexedDB
   - Provides persistent storage backend
   - Handles async operations

3. **Block Storage System**
   - Data stored in 4KB blocks in IndexedDB
   - Each block has metadata with checksums
   - LRU cache (128 blocks default) for performance
   - Dirty blocks tracked until sync

### Persistence Flow

1. **Execute SQL** → SQLite processes transaction in memory
2. **COMMIT** → SQLite finalizes transaction (in-memory)
3. **sync()** → Writes dirty blocks to IndexedDB (persistent)

**Important**: Changes are not persisted to IndexedDB until `sync()` is called!

## ⚡ Performance Characteristics

### Transaction Overhead
- **BEGIN/COMMIT**: Minimal overhead, handled by SQLite
- **Block writes**: Batched and optimized for IndexedDB
- **Sync operations**: Asynchronous, non-blocking

### Concurrency
- Multiple database instances can read committed data
- Writer blocks are isolated until commit
- No reader-writer conflicts for committed data

## ACID Properties

### **Atomicity** [Supported]
- SQLite transactions are all-or-nothing (within SQL)
- ROLLBACK completely undoes all in-transaction changes
- sync() atomically writes all dirty blocks to IndexedDB

### **Consistency** [Supported]
- SQLite enforces database constraints
- Foreign key relationships maintained
- Schema changes are transactional within SQLite

### **Isolation** [Limited]
- SQLite provides read committed isolation in-memory
- Multiple Database instances operate independently
- No cross-instance transaction coordination
- Last sync() wins for conflicting writes

### **Durability** [Two-Step]
- **Two-phase durability model**:
  1. SQL COMMIT → durable in SQLite's in-memory state
  2. sync() → durable in IndexedDB (persistent storage)
- **WITHOUT sync()**: Changes lost on page refresh/crash
- **WITH sync()**: Survives browser restarts and crashes

## Limitations

### 1. **Manual Sync Required**
- **CRITICAL**: Changes are NOT automatically persisted to IndexedDB
- Must call `await db.sync()` after write operations to persist
- Without sync(), data is lost on page refresh/crash

### 2. **Journal Mode**
- MEMORY journal mode used (journal in-memory only)
- WAL mode not currently supported
- Journal operations don't persist until sync()

### 3. **Multi-Tab Concurrency Model** [Supported]
- **Leader Election**: Only leader tab can execute write operations
- **Write Guard**: Non-leaders are blocked from direct writes via `execute()`
- **Write Queue**: Non-leaders can use `queueWrite()` to forward writes to leader
- **Limitations of queueWrite**:
  - Each queued write is a **separate transaction** (not atomic with other queued writes)
  - Cannot use queueWrite for multi-statement transactions (BEGIN...COMMIT blocks)
  - 5-second default timeout (leader must respond within timeout)
  - Requires active leader tab (fails if no leader present)
- **For atomic transactions**: Tab must be leader or call `waitForLeadership()` first

### 4. **Browser Storage Limits**
- Subject to IndexedDB quota (typically 50MB-1GB)
- Large transactions may hit browser limits
- Monitor storage usage in production apps

### 5. **Performance Considerations**
- sync() is async and may take time for large datasets
- Frequent sync() calls can impact performance
- Batch operations when possible
- **queueWrite() overhead**:
  - Additional latency from BroadcastChannel communication
  - Leader must process and acknowledge each request
  - Not suitable for high-frequency writes (prefer direct leader execution)
  - Best for occasional writes from follower tabs

## Testing

The transaction support is thoroughly tested with:

- **[✓]** **Explicit BEGIN/COMMIT transactions**
- **[✓]** **ROLLBACK transaction cancellation**
- **[✓]** **Implicit transaction auto-commit**
- **[✓]** **Cross-instance persistence verification**
- **[✓]** **Crash consistency guarantees**
- **[✓]** **Multiple database isolation**
- **[✓]** **Multi-tab transaction coordination**

Tests can be run with:
```bash
# WASM tests
wasm-pack test --chrome --headless

# Native tests
cargo test
cargo test --features fs_persist

# E2E tests
npm run test:e2e
```

## Best Practices

### 1. **Always Call sync() After Writes**
```javascript
// GOOD: Persist changes immediately
await db.execute('INSERT INTO users VALUES (1, "Alice")');
await db.sync();

// BAD: Changes lost on page refresh!
await db.execute('INSERT INTO users VALUES (1, "Alice")');
// Forgot to sync!
```

### 2. **Batch Operations Before Sync**
```javascript
// GOOD: Multiple operations, single sync
await db.execute('BEGIN TRANSACTION');
await db.execute('INSERT INTO users VALUES (1, "Alice")');
await db.execute('INSERT INTO users VALUES (2, "Bob")');
await db.execute('INSERT INTO users VALUES (3, "Charlie")');
await db.execute('COMMIT');
await db.sync(); // Single sync for all changes
```

### 3. **Handle Errors Gracefully**
```javascript
try {
  await db.execute('BEGIN TRANSACTION');
  await db.execute('INSERT INTO users VALUES (1, "Alice")');
  await db.execute('COMMIT');
  await db.sync();
} catch (error) {
  await db.execute('ROLLBACK');
  console.error('Transaction failed:', error);
}
```

### 4. **Sync Before Page Unload**
```javascript
window.addEventListener('beforeunload', async (e) => {
  if (hasUnsyncedChanges) {
    await db.sync();
  }
});
```

### 5. **Monitor Storage Usage**
- IndexedDB quota: typically 50MB-1GB per origin
- Check storage API for available space
- Implement data cleanup strategies for large datasets

## Multi-Tab Transactions

### Write Coordination
In multi-tab environments, only the leader tab can execute write operations (including transactions).

#### Option 1: Leader-Only Transactions
```javascript
const db = await Database.newDatabase('myapp');

// Check if leader before starting transaction
if (await db.isLeader()) {
  await db.execute('BEGIN TRANSACTION');
  await db.execute('INSERT INTO users VALUES (1, "Alice")');
  await db.execute('INSERT INTO orders VALUES (1, 1, 99.99)');
  await db.execute('COMMIT');
  await db.sync();
}
```

#### Option 2: Queue Transactional Writes
```javascript
// Queue individual writes from any tab
// Note: Each queued write is a separate transaction
await db.queueWrite('INSERT INTO users VALUES (1, "Alice")');
await db.queueWrite('INSERT INTO orders VALUES (1, 1, 99.99)');

// For multi-statement transactions, wait for leadership:
await db.waitForLeadership();
await db.execute('BEGIN TRANSACTION');
await db.execute('INSERT INTO users VALUES (1, "Alice")');
await db.execute('INSERT INTO orders VALUES (1, 1, 99.99)');
await db.execute('COMMIT');
await db.sync();
```

### Important Notes
- **queueWrite** executes each SQL statement as a separate transaction
- For atomic multi-statement transactions, the tab must be the leader
- Followers can wait for leadership with `waitForLeadership()` before starting transactions
- All tabs see committed changes after sync via BroadcastChannel notifications

## Related Resources

- [SQLite Transaction Documentation](https://www.sqlite.org/lang_transaction.html)
- [IndexedDB API Specification](https://www.w3.org/TR/IndexedDB/)
- [AbsurderSQL Main README](../README.md) - Project overview
- [Multi-Tab Coordination Guide](MULTI_TAB_GUIDE.md) - Complete multi-tab guide with advanced features
- [Benchmark Results](BENCHMARK.md) - Performance comparisons
- [Demo Guide](../examples/DEMO_GUIDE.md) - Interactive demos