Transaction Manager Postgresql Laravel Package

Getting Started

Minimal Steps

1. Installation

   composer require aeatech/transaction-manager-postgresql
   

   Ensure your config/database.php is configured for PostgreSQL.

2. Basic Setup Publish the config file (if available) and register the service provider in config/app.php:

   'providers' => [
       // ...
       Aeatech\TransactionManagerPostgresql\TransactionManagerPostgresqlServiceProvider::class,
   ],
   

3. First Use Case Use the transaction manager to wrap a database operation:

   use Aeatech\TransactionManagerPostgresql\TransactionManager;
   
   $manager = app(TransactionManager::class);
   
   $manager->transaction(function () {
       DB::table('users')->where('id', 1)->update(['name' => 'John Doe']);
       DB::table('posts')->where('user_id', 1)->delete();
   });
   

Implementation Patterns

Workflows

1. Basic Transactions Use the transaction() method to group database operations into a single transaction:

   $manager->transaction(function () {
       // Multiple DB operations here
   });
   

2. Nested Transactions The package supports nested transactions (if PostgreSQL's SAVEPOINT is utilized):

   $manager->transaction(function () {
       DB::table('users')->update(['active' => false]);
   
       $manager->transaction(function () {
           DB::table('logs')->insert(['message' => 'Nested operation']);
       });
   });
   

3. Custom Isolation Levels Specify transaction isolation levels (e.g., READ_COMMITTED, SERIALIZABLE):

   $manager->transaction(function () {
       // ...
   }, 'SERIALIZABLE');
   

4. Retry Logic Implement retry logic for transient failures:

   $manager->transaction(function () {
       // Retry on deadlock
       DB::retrying(3, function () {
           DB::table('accounts')->update(['balance' => 100]);
       });
   });
   

Integration Tips

• Laravel Events: Trigger events inside transactions to ensure atomicity:

  $manager->transaction(function () {
      event(new UserUpdated($user));
  });
  

• Eloquent Models: Use with Eloquent for ORM-level transactions:

  $manager->transaction(function () {
      $user = User::find(1);
      $user->posts()->delete();
  });
  

• Queue Jobs: Dispatch jobs within transactions for deferred processing:

  $manager->transaction(function () {
      User::find(1)->update(['status' => 'pending']);
      SendEmailJob::dispatch($user);
  });
  

Gotchas and Tips

Pitfalls

1. Connection Leaks Ensure the transaction manager uses the correct database connection. Explicitly bind the connection if needed:

   $manager->setConnection('pgsql_secondary');
   

2. Nested Transaction Limitations PostgreSQL does not natively support nested transactions (only savepoints). Verify behavior with complex nesting.

3. Long-Running Transactions Avoid holding transactions open for extended periods to prevent lock contention. Use commit() or rollback() explicitly if needed.

4. Error Handling Uncaught exceptions will auto-rollback. Use try-catch for granular control:

   try {
       $manager->transaction(function () {
           // Risky operation
       });
   } catch (\Exception $e) {
       // Log or handle manually
   }
   

Debugging

• Enable Pg Logging Configure PostgreSQL logging in postgresql.conf to trace transaction behavior:

  log_statement = 'all'
  log_min_duration_statement = 0
  

• Check for Deadlocks Use pg_stat_activity to identify blocked transactions:

  SELECT * FROM pg_stat_activity WHERE state = 'active';
  

Extension Points

1. Custom Transaction Handlers Extend the base TransactionManager class to add pre/post hooks:

   class CustomTransactionManager extends TransactionManager {
       protected function beforeTransaction() {
           // Custom logic
       }
   }
   

2. Transaction Metadata Attach metadata (e.g., user ID, context) to transactions for auditing:

   $manager->transaction(function () {
       // ...
   }, 'SERIALIZABLE', ['user_id' => auth()->id()]);
   

3. Fallback Mechanisms Implement fallback logic for non-PostgreSQL environments (e.g., MySQL):

   if (config('database.default') !== 'pgsql') {
       DB::beginTransaction();
       // ...
       DB::commit();
   }