while working on #50, i noticed a weird behavior within the library.

a query cannot be executed ( on the same connection ), unless all handles related to a specific transaction are released, however, the library doesn't offer a way to force release all handles.

Let's take the following example:

/**
 * @template T
 * @param Closure(Executor): T $operation
 * @return T
 */
function transactional(Link $link, Closure $operation): mixed {
  $trans = $link->beginTransaction();
  try {
    $result = $operation($trans);
    $trans->commit();
    return $result;
  } catch(Throwable $e) {
     $trans->rollback();
     throw $e;
  }
}

$link = ...;
$users = transactional($link, static fn($executor) => $executor->query('SELECT * FROM users'));

so far so good, we have a function that given a database link, and an operation, will execute the operation within a transaction, and return it's results.

however, let's take this a step further, simulating a more real-world usage of transactional function.

$users = transactional(
  $link,
  static fn($executor) => $executor->execute('SELECT * FROM forum_users WHERE role = ?', [User::ROLE_ADMIN]),
);

foreach($users as $user) {
  if (admin_has_been_active($user)) {
    continue;
  }

  if (!admin_warned_about_demotion($user)) {
    warn_admin_about_demotion($user);
    continue;
  }

  if (!admin_warned_about_demotion_over_a_week_ago($user)) {
    // hasn't been a week
    continue;
  }
  
  $link->execute('UPDATE users SET forum_users = ? WHERE id = ?', [User::ROLE_SUPER, $user['id']]);
}

here, we get the list of our forum admins, iterator over them, and if one of them admin has not been active, we warn them that their account is going demoted next week, if the have been warned over a week ago, we demote their account to super users.

However, this currently fails.

Since $users is a result set, this result set still has a reference to a handle that is associated with the transaction used to retrieve the users, while arguably the initial query shouldn't have been made within a transaction, remember that this is just an example.

What i suggest is the following:

if a transaction has been rolled-back, or committed, all handles should be force released, this can be done in one of the following ways:

1. force release and mark objects with a reference to any handle as invalid ( e.g iterating over users will fail ).
2. do all the work immediately ( e.g: $users needs a reference to the handle in order to fetch users while iterating, however, when a commit() or a rollback() is executed, we can force the result to fetch all users immediately, making it drop the reference to the handle, and still be valid.

I personally prefer the second solution, while it might be bad for memory when dealing with large queries, it is the most sane behavior in my opinion, as if the user is holding a reference to that object, they are probably still going to use it, if not, they can manually call dispose() on the result.