Blog post Leaderboard MCP Deploy agents
Back to all reviewers

Ensure database transactional integrity

appwrite/appwrite
Based on 9 comments
PHP

When performing multiple related database operations, use transactions and proper error handling to maintain data consistency. Without proper safeguards, partial failures can leave your database in an inconsistent state with orphaned or mismatched records.

Database PHP

Reviewer Prompt

When performing multiple related database operations, use transactions and proper error handling to maintain data consistency. Without proper safeguards, partial failures can leave your database in an inconsistent state with orphaned or mismatched records.

Here are specific practices to follow:

  1. Wrap related operations in transactions when one operation depends on another:
// BAD: Operations can partially succeed, leaving orphaned metadata
$collection = $dbForProject->createDocument('collections', $metadata);
$dbForProject->createCollection('collection_' . $collection->getInternalId());

// GOOD: Use transaction to ensure atomicity
$dbForProject->withTransaction(function() use ($dbForProject, $metadata) {
    $collection = $dbForProject->createDocument('collections', $metadata);
    $dbForProject->createCollection('collection_' . $collection->getInternalId());
});
  1. Add rollback logic when transactions aren’t available:
// GOOD: Explicit rollback when second operation fails
try {
    $collection = $dbForProject->createDocument('collections', $metadata);
    try {
        $dbForProject->createCollection('collection_' . $collection->getInternalId());
    } catch (Exception $e) {
        // Clean up partial state
        $dbForProject->deleteDocument('collections', $collection->getId());
        throw $e;
    }
} catch (Exception $e) {
    // Handle and rethrow
}
  1. Return fresh data after mutations to prevent stale state:
// BAD: Returns stale data
$dbForProject->updateDocument('transactions', $id, ['operations' => $count + 1]);
return $response->dynamic($transaction); // Still has old count!

// GOOD: Return fresh data
$transaction = $dbForProject->updateDocument('transactions', $id, ['operations' => $count + 1]);
return $response->dynamic($transaction); // Has updated count

  1. Validate all mutation paths for operations like bulk creates, updates, increments, and decrements to ensure they’re properly processed in transactions.

  2. Use reference capture (&$variable) in database connection factories to properly reuse connections rather than creating new ones on each call.

Implementing these practices will help maintain database integrity, prevent orphaned records, and ensure your application data remains consistent even when operations fail.

9
Comments Analyzed
PHP
Primary Language
Database
Category

Source Discussions