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 customerInsert new sale order items into
orderdetails
tableGet data from both table
orders
andorderdetails
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 ROLLBACK
statement. 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
andorderdetails
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.
共同學習,寫下你的評論
評論加載中...
作者其他優質文章