Asked  7 Months ago    Answers:  5   Viewed   26 times

In a PHP script working with a mysql database, I recently had the need to use a transaction at a point that happened to be inside another transaction. All my tests seem to indicate this is working out fine, but I can't find any documentation on this usage.

I want to be sure - are transactions within transactions valid in mysql? If so, is there a way to find out how many levels deep you are in nested transactions? (ie. how many rollbacks it would take to return to normal)

Thanks in advance, Brian

 Answers

81

This page of the manual might interest you : 12.3.3. Statements That Cause an Implicit Commit; quoting a few sentences :

The statements listed in this section (and any synonyms for them) implicitly end a transaction, as if you had done a COMMIT before executing the statement.

And, a bit farther in the page :

Transaction-control and locking statements. BEGIN, LOCK TABLES, SET autocommit = 1 (if the value is not already 1), START TRANSACTION, UNLOCK TABLES.

See also this paragraph :

Transactions cannot be nested.
This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.

Wednesday, March 31, 2021
 
Viralk
answered 7 Months ago
25

Your loop can be optimized by pulling the prepare and bind_param statements out of the loop.

$value = null;
$mysqli->autocommit(FALSE);
$sql  = "INSERT INTO temp (`fund_id`) VALUES (?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $value);
foreach ($pdata as $value) {
    $stmt->execute();
}
$mysqli->commit();

You have turned off autocommit with your autocommit(FALSE) line and therefore don't need to use the START TRANSACTION statement.

Wednesday, March 31, 2021
 
penpen
answered 7 Months ago
39

The idea I generally use when working with transactions looks like this (semi-pseudo-code):

try {
    // First of all, let's begin a transaction
    $db->beginTransaction();
    
    // A set of queries; if one fails, an exception should be thrown
    $db->query('first query');
    $db->query('second query');
    $db->query('third query');
    
    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed, and we can commit the transaction
    $db->commit();
} catch (Throwable $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $db->rollback();
    throw $e; // but the error must be handled anyway
}

Note that, with this idea, if a query fails, an Exception must be thrown:
  • PDO can do that, depending on how you configure it
    • See PDO::setAttribute
    • and PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION
  • else, with some other API, you might have to test the result of the function used to execute a query, and throw an exception yourself.

Unfortunately, there is no magic involved. You cannot just put an instruction somewhere and have transactions done automatically: you still have to specific which group of queries must be executed in a transaction.

For example, quite often you'll have a couple of queries before the transaction (before the begin) and another couple of queries after the transaction (after either commit or rollback) and you'll want those queries executed no matter what happened (or not) in the transaction.

Tuesday, June 1, 2021
 
braindamage
answered 5 Months ago
20

Many database ADO providers (such as Oracle ODP.NET) do indeed begin distributed transactions when you use TransactionScope to transact across multiple connections - even when they share the same connection string.

Some providers, (like SQL2008 in .NET 3.5+) recognizes when a new connection is created in a transaction scope that refers to the same connection string, and will not result in DTC work. But any variance in the connection string (such as tuning parameters) may preclude this from occuring - and the behavior will revert to using a distributed transaction.

Unfortunately, the only reliable means of ensuring your transactions will work together without creating a distributed transaction is to pass the connection object (or the IDbTransaction) to methods that need to "continue" on the same transaction.

Sometimes it helps to elevate the connection to a member of the class in which you're doing the work, but this can create awkward situations - and complicates controlling the lifetime and disposal of the connection object (since it generally precludes use of the using statement).

Wednesday, June 30, 2021
 
Crontab
answered 4 Months ago
48

You're looking for Transactional NTFS, introduced by Windows Vista.

Here is a managed wrapper.

Thursday, July 1, 2021
 
ShadowZzz
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :