PHP MySQL Transaction

Here's a little bit of knowledge of PHP operating MySQL business

You know, the default behavior of MySQL is to execute a COMMIT statement after each SQL statement executes, effectively independently of each statement as a single transaction. However, when working with transactions, you need to execute multiple SQL statements, there are two ways to accomplish a transaction for executing multiple SQL statements:

1. Turn off auto-commit

If set autocommit=0, that is, automatic commit is turned off, then any commit or ROLLBACK statement can trigger a transaction commit, if set autocommit=1, that is, automatic commit is turned on (the default value). Then it is necessary to declare the start of the transaction with begin or start transaction, and then the commit or the rollback statement to trigger the transaction commit.

Example:

[PHP]View Plaincopy

2. $db->query (' SET autocommit=0 ');
4. $db->query (' update member set money=money+ '. $money. ' where memberid= '.  $memberId);
6. $db->query (' insert into mem_log (money) VALUES ('. $money.  ') ');
8. $db->query (' commit ');
10. $db->query (' INSERT INTO Test (str) VALUES ('. Sql_encode ($msg). ')'); //The data cannot be inserted. Because autocommit=0

Therefore, when using this method, note that the transaction ends. The value to restore the autocommit.

[PHP]View Plaincopy

2. $db->query (' SET autocommit=0 ');
4. $db->query (' update member set money=money+ '. $money. ' where memberid= '.  $memberId);
6. $db->query (' insert into mem_log (money) VALUES ('. $money.  ') ');
8. $db->query (' commit ');
10. $db->query (' SET autocommit=1 ');
12. $db->query (' INSERT INTO Test (str) VALUES ('. Sql_encode ($msg). ')'); //After recovering autocommit=1, the statement can be successfully inserted

2. Show open a transaction

Shows that opening a transaction is simpler than the method above, does not need to set the value of autocommit, and does not restore the value of autocommit after the end of the transaction. That is, if you want to keep the autocommit switch open, the transaction is declared by begin or start transaction, and the autocommit switch is disabled until you end the transaction with commit or rollback, and the transaction ends, The autocommit switch will be opened.

[PHP]View Plaincopy

2. $db->query (' START TRANSACTION ');
4. $db->query (' update member set money=money+ '. $money. ' where memberid= '.  $memberId);
6. $db->query (' insert into mem_log (money) VALUES ('. $money.  ') ');
8. $db->query (' commit ');
10. $db->query (' INSERT INTO Test (str) VALUES ('. Sql_encode ($msg). ')'); //The data can be inserted

PHP MySQL Transaction