php--PDO transactions and automatic submission

Connected through PDO. Before starting to query, you must first understand how PDO manages transactions. Transactions support four major characteristics (ACID): Atomicity, Consistency, Isolation and Durability. In layman's terms, any operation performed within a transaction, even if performed in stages, is guaranteed to be safely applied to the database and committed without interference from other connections. Transaction operations can also be automatically undone on request (assuming they haven't been committed yet), making it easier to handle errors in scripts.

Transactions are usually implemented by "accumulating" a batch of changes and then making them effective at the same time; the advantage of doing this is that it can greatly improve the efficiency of these changes. In other words, transactions can make scripts faster and potentially more robust (although transactions need to be used correctly to gain such benefits).

Unfortunately, not every database supports transactions, so PDO needs to run in so-called "autocommit" mode when the connection is first opened. Autocommit mode means that each query run has its own implicit transaction if the database supports it, or none if the database does not support transactions. If a transaction is required, it must be started using the PDO::beginTransaction() method. If the underlying driver does not support transactions, a PDOException is thrown (this is a serious error condition regardless of error handling settings). Once a transaction is started, it can be completed with PDO::commit() or PDO::rollBack(), depending on whether the code in the transaction ran successfully.

PDO only checks whether it has transaction processing capabilities at the driver layer. If some runtime condition means that a transaction is not available, and the database service accepts a request to start a transaction, PDO::beginTransaction() will still return TRUE without error.

Trying to use transactions in a MyISAM data table in a MySQL database is a good example.

When the script ends or the connection is about to be closed, if there is an unfinished transaction, PDO will automatically roll back the transaction. This safety measure helps avoid inconsistencies if the script terminates unexpectedly - if the transaction is not committed explicitly, it is assumed that something went wrong, so a rollback is performed to keep the data safe.

Automatic rollback can only occur after starting a transaction through PDO::beginTransaction(). If you manually issue a query to start a transaction, PDO has no way of knowing it and can't roll it back if necessary.

Example #1 Executing batch processing in a transaction

In the following example, assume that a set of entries is created for a new employee and assigned an ID of 23. In addition to registering the person's basic data, it is also necessary to record his salary. It's simple to complete the two updates separately, but by enclosing them in PDO::beginTransaction() and PDO::commit() calls, you can ensure that others cannot see the changes until they are completed. If an error occurs, the catch block rolls back all changes that occurred since the transaction started and prints an error message.

<?php
    try {
        $dbh = new PDO(&#39;odbc:SAMPLE&#39;, &#39;db2inst1&#39;, &#39;ibmdb2&#39;,
            array(PDO::ATTR_PERSISTENT => true));
        echo "Connected\n";
    } catch (Exception $e) {
        die("Unable to connect: " . $e->getMessage());
    }
    try {
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $dbh->beginTransaction();
        $dbh->exec("insert into staff (id, first, last) values (23, &#39;Joe&#39;, &#39;Bloggs&#39;)");
        $dbh->exec("insert into salarychange (id, amount, changedate)
            values (23, 50000, NOW())");
        $dbh->commit();
    } catch (Exception $e) {
        $dbh->rollBack();
        echo "Failed: " . $e->getMessage();
    }
?>

is not limited to making changes within a transaction, you can also issue complex queries to extract data, and you can use that information to build more changes and queries; when the transaction is active, you can ensure that others cannot do it while the operation is in progress. Make changes.