Share some small tests on using PDO to operate transactions in PHP

We will not explain more about the transaction issues, and we will go into more depth when learning the related content of MySQL in the future. understanding. Today we are mainly doing some small tests on operating transactions in PDO, and we may be able to find some interesting content.

What happens when using transactions on MyISAM?

First of all, I believe that anyone who has learned a little bit about MySQL knows that the two commonly used table types in MySQL are InnoDB and MyISAM. Of course, we will not talk about all their differences today, but one difference is the most obvious, that is, MyISAM does not support transactions. So, what happens if we perform transaction operations on MyISAM within a PDO operation?

// myisam
try {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->beginTransaction();
    $pdo->exec("insert into tran_myisam (name, age) values ('Joe', 12)");
    $pdo->exec("insert into tran_myisam2 (name, age) values ('Joe', 12, 33)");

    // sleep(30);
    $pdo->commit();

} catch (Exception $e) {
    $pdo->rollBack();
    echo "Failed: " . $e->getMessage(), PHP_EOL;
}

tran_myisam and tran_myisam2 tables are both MyISAM type tables. In this code, we deliberately wrote the wrong insertion statement of tran_myisam2 and let it go into the catch. The actual execution result is that the error message is output normally and the data of the tran_myisam table is also inserted. In other words, transaction operations on MyISAM tables have no effect. Of course, PDO will not actively report errors. If we make the second SQL statement a normal statement, PDO will only end normally without any errors or prompts.

// innodb
try {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->beginTransaction();
    $pdo->exec("insert into tran_innodb (name, age) values ('Joe', 12)");
    $pdo->exec("insert into tran_innodb2 (name, age) values ('Joe', 12, 3)");
    // sleep(30);
    $pdo->commit();

} catch (Exception $e) {
    $pdo->rollBack();
    echo "Failed: " . $e->getMessage(), PHP_EOL;
}

We can open the comment of sleep(30); this line of code, that is, pause for 30 seconds before the transaction is committed, and then view the infomation_schema.INNODB_TRX table in MySQL. What is shown in this table is the transaction being executed. When the InnoDB type table is executed, you can see a record of the transaction being executed, but you will not see any information in the MyISAM type table.

What will happen if the transaction is not committed or rolled back?

Suppose we forget to write commit() and no error is reported. Will this statement be executed successfully? Just like the code below.

try {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->beginTransaction();
    $pdo->exec("insert into tran_innodb (name, age) values ('Joe', 12)");
    $pdo->exec("insert into tran_innodb2 (name, age) values ('Joe', 12)");

    // 忘记写 $pdo->commit(); 了
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Failed: " . $e->getMessage(), PHP_EOL;
}

PHP will roll back this transaction after the script execution ends, which is actually when the $pdo object is destroyed. In other words, the SQL statement here will not be executed. However, try not to do this, because in a formal environment, our code is very complex and may not be destructed successfully. In this case, there may be transactions that take a long time. The final result is that MySQL's IPQS will be extremely high, and it is difficult to find the reason. Therefore, when using transactions, we must remember that commit() and rollBack() are our brothers and must not be left behind.

The previous transaction was not committed or rolled back. Will the next transaction be executed?

Similarly, we will continue to expand on the basis of the previous question. If two transactions are executed sequentially, and the first transaction is not committed or rolled back, can the next transaction still be executed?

// innodb
try {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->beginTransaction();
    $pdo->exec("insert into tran_innodb (name, age) values ('Joe', 12)");
    $pdo->exec("insert into tran_innodb2 (name, age) values ('Joe', 12)");
    // 忘记写 $pdo->commit(); 了
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Failed: " . $e->getMessage(), PHP_EOL;
}

// innodb
try {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->beginTransaction();
    $pdo->exec("insert into tran_innodb (name, age) values ('BW', 12)");
    $pdo->exec("insert into tran_innodb2 (name, age) values ('BW', 12)");

    // sleep(30);
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Failed: " . $e->getMessage(), PHP_EOL; // Failed: There is already an active transaction
}

We can see that the second transaction directly reports an error, the content is: "There is an existing active transaction here." That is to say, if the previous transaction is not committed or rolled back, the second transaction cannot be executed.

Summary

Today we just learned and tested a few minor issues related to transactions. However, although the problems are small, they may cause serious online accidents. Everyone must be careful when developing careful. We will study the details of transactions when we study MySQL in depth in the future.

测试代码：
https://github.com/zhangyue0503/dev-blog/blob/master/php/202008/source/PHP中使用PDO操作事务的一些小测试.php

Recommended learning: "PHP Video Tutorial"