Commit / Rollback

[fomalhaut]

Hello.

In Php/Mysql, I want to insert rows into two inter-dependant tables T1 and T2. That is, when I insert 1 row into T1, I MUST insert the corresponding into T2.
I've written two prepare statements for these inserts : $ins1 and $ins2, refering the two tables.

$dbh->beginTransaction();
$res1 = $ins1->execute();             // this (try to) execute the insert in the first table
if (!$res1) {
  $dbh->rollback();            // an error was found: don't insert in the second table
}
else {
  $res2 = $ins2->execute();             // this execute the insert in the second table
  if (!$res2) {
    $dbh->rollback();            // an error was found: don't validate any inserts
  }
  else {
   $dbh->commit();               // tables have twice been updated (by inserts)
  }
} 

Am I right if I do that ?

Thanks for your answers.

[rkalhans]

Hello.

In Php/Mysql, I want to insert rows into two inter-dependant tables T1 and T2. That is, when I insert 1 row into T1, I MUST insert the corresponding into T2.

Haven't checked your code but the problem that you just mentioned can be solved by using MySql Triggers. This provides and efficient way of triggering insertion of row entries and you need not bother for the failure of any of them.

check this out

All the best.

[fomalhaut]

Hello

Thanks for your answer and for the link. But I don't understand how do I do the link between the NEW.column.of.the.second.table ans the PHP variables ?
Except the ident of the rows, the other columns can't be calculated from the column of the first table :

On my fisrt table, I have Ident, service. On my second table, I have Ident (in relation), label, and href, those are in php variables, they can't be calculated from Ident nor from service !

All the best

[rkalhans]

Hello
I don't understand how do I do the link between the NEW.column.of.the.second.table ans the PHP variables ?
Except the ident of the rows, the other columns can't be calculated from the column of the first table :

For the above you can try the following,
Using triggers create the corresponding row in the second table, without assigning any value (or a default value) to the non identical columns (means create a row using the identical columns in the two tables).
This will eliminate the need for an explicit commit or rollback.

you can then update the rest of the columns in the newly inserted row using an "UPDATE" query on the second table using php variables.

But you should make sure that the identical rows has a (or a set of rows) that may act as a primary key, because in an event of mutiple thread accessing the mysql table, and in an unlikely event that the two queries trigger the insertion of a row that contain same values of the identical columns, the update query cannot be executed to the required effect.

I am still not recommending the use of explicit rollback and commit using php because in that case you need to use semaphores (MUTEX LOCKS), which may lead to disastrous consequences if not used properly.

Hope this is useful,

[misson]

For the above you can try the following,
Using triggers create the corresponding row in the second table, without assigning any value (or a default value) to the non identical columns (means create a row using the identical columns in the two tables).
This will eliminate the need for an explicit commit or rollback.

If the other columns aren't allowed to be Null and don't have a default, then an insert on the second table isn't possible. If any default isn't appropriate, the insert isn't sound. In a properly normalized schema, the only fields that appear in both tables will be the primary key in the first, which will be a foreign key in the second; non-null, non-foreign key columns are quiet common. Practically speaking, this makes the trigger approach useless.

Furthermore, if a SELECT on the second table occurs between the creation and update of the row in the second table, the resulting row will contain mostly NULLs, which will most likely cause unexpected or incorrect results.

you can then update the rest of the columns in the newly inserted row using an "UPDATE" query on the second table using php variables.

The update on the second table won't have access to any auto-generated column values from the inserts, such as auto-incremented primary keys. Practically speaking, this means an additional SELECT is necessary. Depending on the schema, it might not be possible to SELECT the needed row from the second table.

But you should make sure that the identical rows has a (or a set of rows) that may act as a primary key, because in an event of mutiple thread accessing the mysql table, and in an unlikely event that the two queries trigger the insertion of a row that contain same values of the identical columns, the update query cannot be executed to the required effect.

The trigger you describe is no more helpful than transactions in this case.

I am still not recommending the use of explicit rollback and commit using php because in that case you need to use semaphores (MUTEX LOCKS), which may lead to disastrous consequences if not used properly.

Why do you think locking is necessary? Transactions ensure the action of multiple statements is atomic. Their primary purpose is to keep the DB in a consistent state, which is what the OP needs.

In short, use transactions, not triggers, when insertion of multiple rows (into any number of tables) needs to be all-or-nothing.

[fomalhaut]

Hello.

So, if I've understood, writting that will be correct :

try {
  $dbh->beginTransaction();
 $res1 = $ins1->execute();    // insert on first table
 $res2 = $ins2->execute();    // insert on second table
 $dbh->commit();              // inserts validated
 echo 'That is OK';
}
catch(Exception $err); {       // case of error
  $dbh->rollback();            // inserts not validated
  echo 'There si a problem:<br />';
  echo 'error: '.$err->getMessage().'<br />';
  echo 'n&deg;: '.$err->getCode();
} 

Isn't it ?

Thanks for advance.
</span></span>

[misson]

So, if I've understood, writting that will be correct : [...]

The code will most likely work, assuming you're using the PDO MySQL driver and you've set PDO::ERRMODE_EXCEPTION on $dbh. However, PDOStatement::execute officially returns False "on failure", while exceptions are thrown "on error". The difference isn't well defined, but there might be instances where execute returns False rather than throwing an exception. Looking at the source, this looks to be the case, though (in practice) those branches may not be reachable. Still, the safest thing is to test the result of the first $ins1->execute(), throwing a PDOException if it returns false.

One other issue with the code is that printing the result of PDOException::getMessage discloses too much information.

[fomalhaut]

OK, Misson, I've this :

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

try {
 $dbh->beginTransaction();
 $res1 = $ins1->execute();    // insert on first table
}
catch(Exception $err); {
  $dbh->rollback();
  echo 'insert 1 KO';
  ... code for ending ...
}
try {
 $res2 = $ins2->execute();    // insert on second table
}
catch(Exception $err); {
  $dbh->rollback();
  echo 'insert 2 KO';
  ... code for ending ...
}
$dbh->commit();              // inserts validated
echo 'All was OK';
} 

</span></span>
Do you think that works ?

Thanks for your time.

[misson]

You only need separate PDO exception handlers if you want to handle exceptions thrown by the different inserts differently. Stick with the second sample you posted, possibly adding an if around the $ins1->execute()

Edit: from taking a closer look at the source, the only times the MySQL driver will return False involve mistakes in the prepared statement parameters, which you're not using. You should be fine with:

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
...
try {
  $dbh->beginTransaction();
  // $query will hold the most recent query, in case of failure.
  $query =$ins1;
  $ins1->execute();
  $query =$ins2;
  $ins2->execute();    // insert on second table
  $dbh->commit();     // inserts validated
  echo 'That is OK';
} catch(PDOException $err); {       // case of error
  // $query refers to the failed query
  $dbh->rollback();            // inserts not validated

  ob_start();
  $query->debugDumpParams();
  $msg = $err . "\n\nquery: " . ob_get_contents();  
  ob_end_clean();

  echo '<p>There is an internal problem.';
  if (user is admin) {
    echo '</p><pre>', $msg,  '</pre>';
  } else {
    error_log($msg);
    error_log($msg, 1, $adminEmail);
    echo 'It's been logged, and we'll look into it.</p>';
  }
} 

If you find yourself needing to pass parameters to execute, then you might need return value checks for code maintenance.

[fomalhaut]

Thanks, Misson, I've just finished my tests, that works fine, doing exactly as you wrote ! It's OK.

Thank you very much again.