Phalcon Transactions and Nested Transactions

Transaction is supported in Phalcon as it is attached with PDO. To increase the performance of the database we can perform data manipulation. Below is the database structure on which transaction is applied:

Transaction example:

<?php

try {
    // Start a transaction
    $connection->begin();

    // Execute some SQL statements
    $connection->execute('DELETE 'company' WHERE 'id' = 101');
    $connection->execute('DELETE 'company' WHERE 'id' = 102');
    $connection->execute('DELETE 'company' WHERE 'id' = 103');

    // Commit if everything goes well
    $connection->commit();
} catch (Exception $e) {
    // An exception has occurred rollback the transaction
    $connection->rollback();
}

// Database 'Company' data deleted.

Nested Transaction Example:

<?php

try {
    // Start a transaction
    $connection->begin();

    // Execute some SQL statements
    $connection->execute('DELETE 'company' WHERE 'id' = 101');

    try {
        // Start a nested transaction
        $connection->begin();

        // Execute these SQL statements into the nested transaction
        $connection->execute('DELETE 'company' WHERE 'id' = 102');
        $connection->execute('DELETE 'company' WHERE 'id' = 103');

        // Create a save point
        $connection->commit();
    } catch (Exception $e) {
        // An error has occurred, release the nested transaction
        $connection->rollback();
    }

    // Continue, executing more SQL statements
    $connection->execute('DELETE 'company' WHERE 'id' = 104');

    // Commit if everything goes well
    $connection->commit();
} catch (Exception $e) {
    // An exception has occurred rollback the transaction
    $connection->rollback();
}

// Database 'Company' data deleted.

Event Name	Triggered	Break Operation
afterConnect	After a successfully connection to a database system	No
beforeQuery	Before send a SQL statement to the database system	Yes
afterQuery	After send a SQL statement to database system	No
beforeDisconnect	Before close a temporal database connection	No
beginTransaction	Before a transaction is going to be started	No
rollbackTransaction	Before a transaction is rollbacked	No
commitTransaction	Before a transaction is committed	No