Re: MySQL -- finding whether there's a transaction started

Bogdan Stancescu wrote:

Hello list, I'm developing a library and would need to know if the code calling my library has already started a MySQL transaction or not. I want to know whether I should start one or use savepoints instead -- starting a transaction if one is already in progress commits the existing transaction, and setting a savepoint silently fails outside transactions. And I was unable to find any non-destructive way of retrieving that information -- is there any?

I don't think mysql has any way of finding that out. If you're using an abstraction layer, it's easy enough in code - though rollback's are a little harder - should they do a complete rollback or just to a savepoint? class db { .... private $transaction_count = 0; public function startTransaction() {

    if ($this->transaction_count == 0) {
      mysql_query("BEGIN");
    } else {
      mysql_query("SAVEPOINT");
    }
    $this->transaction_count++;

} public function commitTransaction() {

    // you can't commit a transaction if there's more than one "open"
    // so just decrement the counter
    if ($this->transaction_count > 1) {
      $this->transaction_count--;
      return;
    }
    $this->transaction_count = 0;
    mysql_query("COMMIT");

} } Now you can just call $db->startTransaction(); and $db->commitTransaction(); and it'll handle the stuff for you. -- Postgresql & php tutorials http://www.designmagick.com/