MySQL transaction processing function, one of MySQL basic operation notes

I. MySQL Transaction Processing Mechanism

Refer to the blog post: MySQL's transaction processing function provides a simple description of MySQL transaction processing and lock mechanism. MySQL transaction processing and lock statements

 

A Brief Introduction to transactions:

Generally, a transaction must meet four conditions:

Atomicity: when a transaction is executed, it must be "done in full or not ". That is to say, partial transaction execution is not allowed. Even if the transaction cannot be completed due to a fault, the impact on the database should be eliminated during rollback.

Consistency: transaction operations should change the database from a consistent state to another consistent state. For example, online shopping can constitute a transaction only when goods are delivered out of the warehouse and commodities are imported into the customer's shopping basket.

Isolation: if multiple transactions are executed concurrently, they should be executed independently.

Durability: A successfully executed transaction has a lasting effect on the database. Even if the database fails due to a fault or error, it should be able to be restored.

(PS) in MySQL, only InnoDB and bdb types can support transaction processing. Other types are not supported.

MySQL provides two transaction processing mechanisms:

The first type is implemented using begin, rollback, and commit. We recommend that you use this method.

Step 1: Start a transaction

Step 2: run the MySQL statement to check whether the execution is successful.

Step 3: roll back the transaction if the check fails.

Step 4: Repeat steps 2 and 3 based on transaction requirements.

Step 5: confirm the commit transaction.

// The following code has been tested and run on the MySQL console. No problem
Mysql> use test; database changedmysql> Create Table 'dbtest' (-> ID int (4)->) type = InnoDB; query OK, 0 rows affected, 1 warning (0.05 Sec) mysql> select * From dbtest->; empty set (0.01 Sec) mysql> begin; query OK, 0 rows affected (0.00 Sec) mysql> insert into dbtest value (5); query OK, 1 row affected (0.00 Sec) mysql> insert into dbtest value (6); query OK, 1 row affected (0.00 Sec) mysql> commit; query OK, 0 rows affected (0.00 Sec) mysql> select * From dbtest; + ------ + | ID | + ------ + | 5 | 6 | + ------ + 2 rows in SET (0.00 Sec) mysql> begin; query OK, 0 rows affected (0.00 Sec) mysql> insert into dbtest values (7); query OK, 1 row affected (0.00 Sec) mysql> rollback; query OK, 0 rows affected (0.00 Sec) mysql> select * From dbtest; + ------ + | ID | + ------ + | 5 | 6 | + ------ + 2 rows in SET (0.00 Sec) mysql>

/*** The following code is a similar implementation in PHP */$ handler = mysql_connect ("localhost", "root", ""); mysql_select_db ("task "); mysql_query ("begin"); // start transaction definition mysql_query ('start transaction'); If (! Mysql_query ("insert into trans (ID) values ('2')") {mysql_query ("roolback"); // determines if the execution fails to be rolled back} If (! Mysql_query ("insert into trans (ID) values ('4')") {mysql_query ("roolback "); // judge execution failure rollback} mysql_query ("commit"); // execute the transaction mysql_close ($ handler );

 

 

 

The second is to directly use set to change the MySQL automatic submission mode.

MySQL is automatically submitted by default, that is, when a query is submitted, it is executed directly. We can use

Set autocommit = 0; automatic submission prohibited

Set autocommit = 1; Enable automatic submission

To implement transaction processing.

Note: UseSet Autocommit=0In the future, all your SQL statements will be usedTransactionsProcessing, until you use commit to confirm or rollback to end, note that when you end thisTransactionsAt the same time, a newTransactions! In the first method, only the currentTransactions! Therefore, the first method is recommended.