Summary: in this tutorial, you will learn about MySQL transaction and how to use MySQL COMMIT statement and MySQL ROLLBACK statement to manage transactions in MySQL.

Introducing to MySQL Transaction

To understand what a transaction in MySQL is, let’s take a look at an example of adding a new sale order in our sample database. The steps of adding a sale order are as described as follows:

• Get latest sale order number from orders table, and use the next sale order number as the new sale order number.

• Insert a new sale order into orders table for a given customer

• Insert new sale order items into orderdetails table

• Get data from both table orders and orderdetails tables to confirm the changes

Now imagine what would happen to your data if one or more steps above fail because of database failure such as table lock security? If the step of adding order items into orderdetails table failed, you would have an empty sale order in your system without knowing it. Your data may not be integrity and the effort you have to spend to fix it is tremendous.

How do you solve this problem? That’s why the transaction processing comes to the rescue. MySQL transaction enables you to execute a set of MySQL operations to ensure that the database never contains the result of partial operations. In a set of operations, if one of them fails, the rollback occurs to restore the database. If no error occurred, the entire set of statements is committed to the database.

Using MySQL Transaction

Let’s review the most important MySQL transaction statements before we are using them for the adding sale order in the example above.

To start a transaction you use the START TRANSACTION statement. To undo MySQL statements you use the ROLLBACKstatement. Notice that there are several SQL statements you cannot use ROLLBACK such as:

CREATE / ALTER / DROP DATABASE CREATE /ALTER / DROP / RENAME / TRUNCATE TABLE CREATE / DROP INDEX CREATE / DROP EVENT CREATE / DROP FUNCTION CREATE / DROP PROCEDURE …

To write the changes into the database within a transaction you use the COMMIT statement. It is important to note that MySQL automatically commit the changes to the database by default. To force MySQL not to commit changes automatically, you need to use the following statement:

SET autocommit = 0;

MySQL transaction example

In order to use MySQL transaction, you first have to break your MySQL statements into logical portion and determine when data should be committed or rollback.

Let’s take a look an example of using MySQL transaction to add new sale order into our sample database above and add the transaction processing steps:

• Start a transaction using START TRANSACTION statement.

• Get latest sale order number from orders table, and use the next sale order number as the new sale order number.

• Insert a new sale order into orders table for a given customer.

• Insert new sale order items into orderdetails table.

• Commit changes using COMMIT statement.

• Get data from both table orders and orderdetails tables to confirm the changes.

The following  is the script that performs the above steps:

-- start a new transaction start transaction; -- get latest order number select @orderNumber := max(orderNUmber) from orders; -- set new order number set @orderNumber = @orderNumber + 1; -- insert a new order for customer 145 insert into orders(orderNumber,                    orderDate,                    requiredDate,                    shippedDate,                    status,                    customerNumber) values(@orderNumber,        now(),        date_add(now(), INTERVAL 5 DAY),        date_add(now(), INTERVAL 2 DAY), 'In Process', 145); -- insert 2 order line items insert into orderdetails(orderNumber,                          productCode,                          quantityOrdered,                          priceEach,                          orderLineNumber) values(@orderNumber,'S18_1749', 30, '136', 1), (@orderNumber,'S18_2248', 50, '55.09', 2); -- commit changes     commit; -- get the new inserted order select * from orders a  inner join orderdetails b on a.ordernumber = b.ordernumber where a.ordernumber = @ordernumber;

In this tutorial, you’ve learned how to use MySQL transaction statements including START TRANSACTION, COMMIT and ROLLBACK to manage transactions in MySQL to protect data integrity.

原文链接：http://outofmemory.cn/mysql/mysql-transaction