Hướng dẫn tạo database trong mysql Informational, Transactional
This article is part of Robert Sheldon's continuing series on Learning MySQL. To see all of the items in the series, click here. Show MySQL transactions provide an effective method for executing multiple statements as a single unit, making it possible to safely modify data, while preventing concurrent users from updating the same data or overwriting changed data. If any statements within a transaction fail or do not achieve their desired results, the changes can be rolled back and the database returned to its original state, as it existed prior to starting the transaction. Otherwise, MySQL commits the changes to the database and then terminates the transaction. Transactions play a pivotal role in ensuring the integrity of MySQL data, which is why database developers should have a good foundation in how they work. There are many aspects to transactions, however, far more than can be covered adequately in a single article. The purpose of this article is to introduce you to the basics of transactions, so you have a better understanding of how to get starting using them when writing your SQL code. To this end, I provide you with several examples that demonstrate how MySQL transactions work and why they’re important. Although the examples are fairly basic, they should help you understand what it takes to create a transaction and what types of behavior you can expect. Transactions are an invaluable tool when trying to maintain data consistency and reliability across your database. The better you understand how to use them, the more effectively you can ensure the integrity of your data. Note: The examples in this article are based on a local instance of MySQL that hosts a very simple database and tables. The last section of the article— “Appendix: Preparing your MySQL environment”—provides information about how I set up my system and includes a SQL script for creating the database and tables on which the examples are based. Transaction basics in MySQLMySQL transactions maintain consistency across the database regardless of the complexities of your operations. They help achieve a state of compliance referred to as ACID, an acronym for atomicity, consistency, isolation, and durability:
In this article, I focus on how to define explicit transactions that run multiple SQL statements. I say “explicit” because MySQL also supports implicit transactions. By default, the database engine treats each SQL statement as an atomic unit that runs as its own transaction. For example, if you execute an You can override this behavior by running a A more common approach is to define explicit transactions, especially in stored programs such as functions, triggers, and stored procedures. Stored programs often use transactions to ensure that data changes are committed as an atomic operation. An explicit transaction temporarily disables autocommit, while providing greater control over your transactional logic. An explicit transaction typically includes the following three SQL statements:
The key to using these statements is to define the correct logic in your SQL code to ensure that your transactions protect against possible data integrity issues by committing or rolling back operations according to that logic. Processing queries in MySQLBefore I demonstrate how to define a transaction, I first want to show you how MySQL processes a set of statements that are not defined within an explicit transaction. The following stored procedure enables a user to add a new manufacturer and its first airplane to the database: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 DROP PROCEDURE IF EXISTS add_first_plane; DELIMITER // CREATE PROCEDURE add_first_plane( IN mfc_name VARCHAR(50), IN plane_name VARCHAR(50), IN engine_type VARCHAR(50)) BEGIN DECLARE mfc_id INT; DECLARE pln_id INT; INSERT INTO manufacturers (manufacturer) VALUES(mfc_name); SET mfc_id \= (SELECT manufacturer_id FROM manufacturers WHERE manufacturer \= mfc_name); INSERT INTO airplanes (plane, manufacturer_id, engine_type) VALUES (plane_name, mfc_id, engine_type); END// DELIMITER ; If you’ve been following along with this series, many of the elements in the procedure definition should be familiar to you. You can refer back to the article Working with MySQL Stored Procedures if something is unclear. In this case, the procedure definition defines three input parameters: The procedure also includes elements you might not have seen before. First, the Notice that you don’t precede the variable name with the at ( The variable declarations are then followed by an Next comes a The last statement in the procedure’s After you add the stored procedure to the database, you can use a CALL add_first_plane ('Airbus', 'A340-600', 'Jet'); When you run this statement, MySQL processes the procedure’s statements and commits the changes to the database one statement at a time. In this case, a row is first added to the SELECT * FROM manufacturers; SELECT * FROM airplanes; This returns the following two result sets: ![A screenshot of a computer Description automatically generated with low confidence](https://https://i0.wp.com/www.red-gate.com/simple-talk/wp-content/uploads/2023/05/a-screenshot-of-a-computer-description-automatica.png) And: Ideally, the `COMMIT`3 The problem is that the first ![A screenshot of a computer Description automatically generated with low confidence](https://https://i0.wp.com/www.red-gate.com/simple-talk/wp-content/uploads/2023/05/a-screenshot-of-a-computer-description-automatica-1.png) Typically, you’ll want to avoid such scenarios. Not only can they make the data confusing (while undermining the tenets of a relational model), but they also require you or someone else to fix the problem. In this case, the error occurred before inserting a duplicate row into the `START`9 table, so only one table needs to be corrected (although this is still one table too many). But the problem doesn’t stop there. Suppose some well-meaning DBA comes along and updates the name of the ALTER TABLE airplanes RENAME COLUMN plane TO airplane; Perhaps this was done for good reasons, but if the change was made without updating the stored procedure, you would run into additional issues when trying to run the procedure. For example, the following CALL add_first_plane ('Beechcraft', 'Baron 58', 'Piston'); If you try to run this statement, you’ll receive the following error: `ROLLBACK`6 Not surprisingly, the error resulted from trying to run the second In some cases, it might be okay for a row to be created when another statement fails. Usually though, you might not want a row added unless one was also added to the `START`9 table. Fortunately, you can address issues such as these by defining a transaction within your stored procedure. Creating a transaction in a stored programBefore we get into the details of adding a transaction, you might want to clean up your database so you’re starting with a clean state for the next example. One way to do this is to simply rerun the database creation script in the appendix. You can also either truncate or delete the data in the tables and change the With that in mind, let’s look at how to include a transaction in our stored procedure. The first step is to add the `mfc_name`3 `mfc_name`4 and `mfc_name`5 statements into the definition to form a block around some of the other SQL statements: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 DROP PROCEDURE IF EXISTS add_first_plane; DELIMITER // CREATE PROCEDURE add_first_plane( IN mfc_name VARCHAR(50), IN plane_name VARCHAR(50), IN engine_type VARCHAR(50)) BEGIN DECLARE mfc_id INT; DECLARE pln_id INT; DECLARE mfc_count INT; START TRANSACTION; SET mfc_count \= (SELECT COUNT(*) FROM manufacturers WHERE manufacturer LIKE CONCAT('%', mfc_name, '%') FOR SHARE); -- if manufacturer does not exist, add manufacturer; -- otherwise, roll back IF mfc_count \= 0 THEN INSERT INTO manufacturers (manufacturer) VALUES(mfc_name); SET mfc_id \= (SELECT manufacturer_id FROM manufacturers WHERE manufacturer \= mfc_name FOR SHARE); INSERT INTO airplanes (airplane, manufacturer_id, engine_type) VALUES (plane_name, mfc_id, engine_type); ELSE ROLLBACK; SELECT CONCAT('Manufacturer \'', mfc_name, '\' might already exist.') AS Warning; END IF; COMMIT; END// DELIMITER ; The `mfc_name`3 `mfc_name`4 statement begins the transaction after the variable declarations. The transaction remains active until it is committed or rolled back. I’ve also added the In addition to the One thing different about the If the Before going any further, I want to point out that not all SQL statements can be rolled back, particularly data definition language (DDL) statements such as `SET`01 `SET`02 or `SET`03 `SET`02. If you include such a statement in your transaction and a subsequent statement fails, the DDL changes are retained, preventing a full rollback. (More details here) The final `mfc_name`5 statement commits the changes to the database and terminates the transaction, unless the `plane_name`6 block was skipped and the statements in the `BEGIN…END`7 block ran, in which case the transaction was terminated by the `BEGIN…END`8 statement. Before starting these next steps, I will reset the tables we have been working with. SET SQL_SAFE_UPDATES \= 0; DELETE FROM airplanes; DELETE FROM manufacturers; SET SQL_SAFE_UPDATES \= 1; After you create this procedure, you can run the same `TRANSACTION`3 statement as earlier: CALL add_first_plane ('Airbus', 'A340-600', 'Jet'); The first time you run this statement, it inserts a row into each of the two tables, just like you saw in the previous example. However, if you run the statement a second time, it will return the following message, without adding a new row to either table: `SET`10 Now suppose our trusty DBA tries to change the column name in the `START`9 table once again: ALTER TABLE airplanes RENAME COLUMN airplane TO plane; After the table definition has been modified, you can try to run the stored procedure just like before: CALL add_first_plane ('Beechcraft', 'Baron 58', 'Piston'); Unfortunately, MySQL again returns error 1054 and inserts the row into the `START`0 table. (Execute the procedure again and you will get the error about manufacturer ‘Beechcraft’ might already existing.) This is because the procedure runs the `BEGIN…END`8 statement only if the `mfc_name`8 value does not equal `plane_name`9. If the variable does equal `plane_name`9, the statements that ran successfully are not rolled back, even if one of them generated an error. For this, we need to modify our procedure definition once more. Adding exception handling to a transactionException handling can help catch the type of error described above, as well as catch other types of errors. To include it to your procedure, you should add a `SET`17 statement that defines a handler for responding to one or more conditions. The statement will then carry out a specific action if one of those conditions occurs. It can also execute other SQL statements related to that action. For example, the following procedure includes a `SET`17 statement that specifies two conditions: `SET`19 and `SET`20: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 DROP PROCEDURE IF EXISTS add_first_plane; DELIMITER // CREATE PROCEDURE add_first_plane( IN mfc_name VARCHAR(50), IN plane_name VARCHAR(50), IN engine_type VARCHAR(50)) BEGIN DECLARE mfc_id INT; DECLARE pln_id INT; DECLARE mfc_count INT; -- roll back if an error occurs DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING BEGIN ROLLBACK; SELECT ('An error occurred. Contact your administrator.') AS Warning; END; START TRANSACTION; SET mfc_count \= (SELECT COUNT(*) FROM manufacturers WHERE manufacturer LIKE CONCAT('%', mfc_name, '%') FOR SHARE); -- if manufacturer does not exist, add manufacturer; -- otherwise, roll back IF mfc_count \= 0 THEN INSERT INTO manufacturers (manufacturer) VALUES(mfc_name); SET mfc_id \= (SELECT manufacturer_id FROM manufacturers WHERE manufacturer \= mfc_name FOR SHARE); INSERT INTO airplanes (plane, manufacturer_id, engine_type) VALUES (plane_name, mfc_id, engine_type); ELSE ROLLBACK; SELECT CONCAT('Manufacturer \'', mfc_name, '\' might already exist.') AS Warning; END IF; COMMIT; END// DELIMITER ; I’ve defined the `SET`17 statement after the other variable declarations. The statement includes `SET`22 as the action, which means that MySQL will terminate the procedure’s execution if one of the conditions is met. The first condition, `SET`19, is met if MySQL encounters an error when executing the procedure’s statements, and the second condition, `SET`20, is met of MySQL receives a warning when running the statements. The Before starting these next steps, I will reset the tables we have been working with. SET SQL_SAFE_UPDATES \= 0; DELETE FROM airplanes; DELETE FROM manufacturers; SET SQL_SAFE_UPDATES \= 1; Now, try to run the same `TRANSACTION`3 statements as in the previous examples, starting with the one that adds Airbus as a manufacturer and its first plane: CALL add_first_plane ('Airbus', 'A340-600', 'Jet'); When you run this statement the first time, MySQL should add the data as expected. When you run the statement the second time, MySQL should return the following message without adding a row to either table: `SET`10 All this is just like the previous example, but now let’s assume that our wayward DBA once again changes the ALTER TABLE airplanes RENAME COLUMN plane TO airplane; With the change in place, you then try to add the next manufacturer and airplane, just like you did previously: CALL add_first_plane ('Beechcraft', 'Baron 58', 'Piston'); This time around, MySQL returns the following message, without adding rows to either table: `SET`35 With the additional exception handling, the stored procedure should now be able to accommodate errors much more efficiently and without causing additional work for you or someone else. Working with MySQL transactionsIn this article, I’ve given you a basic overview of MySQL transactions so you can begin to understand how they work. Not surprisingly, there’s a lot more to transactions than what I can cover in a single article. You can, for example, create savepoints within a transaction and specifically roll back to them. You can also specify additional characteristics that control how the transaction runs, and you can set the transaction isolation level at a global or session level to better accommodate your workloads. For more information about transactions, see the MySQL topic Transactional and Locking Statements. In the meantime, what I’ve provided in this article should help you start building a foundation in MySQL transactions so you can begin incorporating them into your code, particularly your stored programs. Although the examples I’ve shown you are fairly basic, they demonstrate how to start, commit, and roll back a transaction—the three pillars of most transactions. From this foundation, you can start building transactions that incorporate more sophisticated statement logic. Just make sure you do your homework before journeying down that path. Appendix: Preparing your MySQL environmentWhen creating the examples for this article, I used a Mac computer that was set up with a local instance of MySQL 8.0.29 (Community Server edition). I also used MySQL Workbench to interface with MySQL. If you want to try out the examples in this article, you need only a minimal database setup, which includes the 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 DROP DATABASE IF EXISTS travel; CREATE DATABASE travel; USE travel; CREATE TABLE manufacturers ( manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT, manufacturer VARCHAR(50) NOT NULL, create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (manufacturer_id) ) ENGINE\=InnoDB AUTO_INCREMENT\=1001; CREATE TABLE airplanes ( plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT, plane VARCHAR(50) NOT NULL, manufacturer_id INT UNSIGNED NOT NULL, engine_type VARCHAR(10) NOT NULL, create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (plane_id), CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) REFERENCES manufacturers (manufacturer_id) ) ENGINE\=InnoDB AUTO_INCREMENT\=101; That’s all there is to the |