This blog post will cover a simple example of using a TRANSACTION
along with user-defined session variables to greatly reduce the chance for errors on our part when updating multiple database tables.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and MySQL 5.7.20 for these exercises.
The Requirement And Task
Suppose we have the table structure and data for the following three tables:
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| country_id | int(11) | NO | | NULL | |
| country_name | varchar(40) | YES | | NULL | |
| region_id | decimal(10,2) | YES | | NULL | |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
+------------+--------------+-----------+
| country_id | country_name | region_id |
+------------+--------------+-----------+
| 1 | USA | 11.50 |
| 3 | MEX | 13.50 |
| 6 | CHI | 16.50 |
| 2 | CAN | 21.50 |
| 311 | GER | 311.50 |
| 55 | CRO | 55.50 |
+------------+--------------+-----------+
6 rows in set (0.00 sec)
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| country_id | int(11) | NO | | NULL | |
| country_name | varchar(40) | NO | | NULL | |
| region_id | decimal(10,2) | YES | | NULL | |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
+------------+--------------+-----------+
| country_id | country_name | region_id |
+------------+--------------+-----------+
| 1 | USA | 11.50 |
| 3 | MEX | 13.50 |
| 6 | CHI | 16.50 |
| 2 | CAN | 21.50 |
| 311 | GER | 311.50 |
| 55 | CRO | 55.50 |
+------------+--------------+-----------+
6 rows in set (0.01 sec)
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| country_id | int(11) | NO | | NULL | |
| address | varchar(75) | YES | | NULL | |
| country_name | varchar(40) | YES | | NULL | |
| division | char(2) | YES | | NULL | |
| region_id | decimal(10,2) | YES | | NULL | |
+--------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
+------------+---------------------+--------------+----------+-----------+
| country_id | address | country_name | division | region_id |
+------------+---------------------+--------------+----------+-----------+
| 1 | 1212 Lane 5 | USA | NE | 11.50 |
| 2 | Wonderloo Blvd. | CAN | NW | 21.50 |
| 3 | Santa Road-2 | MEX | NW | 13.50 |
| 6 | Moore Run Ave | CHI | SW | 16.50 |
| 311 | Hwy 7-4BA Road-24 | GER | SE | 311.50 |
| 55 | 89BLVD-22 | CRO | NW | 55.50 |
| 311 | Hwy 711-47A Road-74 | GER | SW | 311.50 |
+------------+---------------------+--------------+----------+-----------+
7 rows in set (0.00 sec)
In this mock scenario, the country_id
column with value 311
needs to be changed to 310
for all tables containing this information.
Well, that’s simple enough. All of those updates can be run at once in the below session_var.sql
file, via batch mode, easy as pie:
That will do it, right?
Did you catch the error in that group of UPDATE
statements?
Look again.
Oops. This will cause problems.
Notice where SET country_id = 301
is highlighted with the cursor and surrounding black rectangle in the
SET country_id = 301
WHERE country_id = 311;
UPDATE
statement?
I have made a boo-boo here.
I should be setting country_id
to 310
, not 301
.
Unfortunately, an easy mistake like this can happen at any time.
How can we reduce the chances of making these types of mistakes?
With user-defined session variables and TRANSACTIONS
.
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Session Variables And Transactions
Have a look at the updated and improved session_var.sql
file now:
And when running the session_var.sql
file in batch mode:
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
Query OK, 0 rows affected (0.02 sec)
mysql>
All updates are successful.
How about a visual verification, then dive into what’s going on in the session_var.sql
file?
+------------+--------------+-----------+
| country_id | country_name | region_id |
+------------+--------------+-----------+
| 1 | USA | 11.50 |
| 3 | MEX | 13.50 |
| 6 | CHI | 16.50 |
| 2 | CAN | 21.50 |
| 310 | GER | 311.50 |
| 55 | CRO | 55.50 |
+------------+--------------+-----------+
6 rows in set (0.00 sec)
+------------+--------------+-----------+
| country_id | country_name | region_id |
+------------+--------------+-----------+
| 1 | USA | 11.50 |
| 3 | MEX | 13.50 |
| 6 | CHI | 16.50 |
| 2 | CAN | 21.50 |
| 310 | GER | 311.50 |
| 55 | CRO | 55.50 |
+------------+--------------+-----------+
6 rows in set (0.00 sec)
+------------+---------------------+--------------+----------+-----------+
| country_id | address | country_name | division | region_id |
+------------+---------------------+--------------+----------+-----------+
| 1 | 1212 Lane 5 | USA | NE | 11.50 |
| 2 | Wonderloo Blvd. | CAN | NW | 21.50 |
| 3 | Santa Road-2 | MEX | NW | 13.50 |
| 6 | Moore Run Ave | CHI | SW | 16.50 |
| 310 | Hwy 7-4BA Road-24 | GER | SE | 311.50 |
| 55 | 89BLVD-22 | CRO | NW | 55.50 |
| 310 | Hwy 711-47A Road-74 | GER | SW | 311.50 |
+------------+---------------------+--------------+----------+-----------+
7 rows in set (0.00 sec)
That’s what’s up. All updates went through.
User-Defined Session Variables
On lines 3 and 4 of the session_var.sql
file, the SET
statement is used to assign values to two variables, named outdated_ci
and updated_ci
.
SET
statement syntax is as follows:
Note: For assignment purposes, both the =
and =:
operators are permitted in SET
syntax.
The values 310
and 311
assigned to both variables, outdated_ci
and updated_ci
respectively, are those that need to be modified with the UPDATE
statements.
This provides us a level of consistency by passing in these variables to the statements themselves, versus hard coding each UPDATE
statement. Even should the variables be set with incorrect values during the assignment, (Which we hope we don’t!) at least their values would be consistent across all the affected tables.
These variables are specific to the current session only, meaning when this session is finished and exited, these variables are freed up and cease to exist any further. Throwing in a TRANSACTION
offers additional benefits as we will see below.
Transactions
By default, autocommit
in MySQL is enabled, meaning any changes made to tables (updates or deletes) are automatically written and cannot be reverted.
The START TRANSACTION;
statement on line 6, is but one means to effectively turn off autocommit
.
Once the START TRANSACTION;
command is executed, autocommit
remains disabled until either a COMMIT
(called at line 20) or ROLLBACK
command is executed.
Had ROLLBACK
instead of COMMIT
been used on line 20, all changes would have been reverted back to the state prior to attempting the first UPDATE
at line 8.
But why do this you ask?
By wrapping all the UPDATE
statements in this TRANSACTION
, either all tables will be updated at once when COMMIT
is called, or they won’t should any one of the UPDATE
statements fail. In layman’s terms, it’s basically an ‘All or nothing’ type of operation.
I sifted through the documentation and assembled below what I feel are key highlights. However, please peruse it yourself (I learned so much doing this myself!!) as well.
About User Variables
Reminders and gotchas:
- Are session specific and automatically freed or released, when the current session is finished or closed.
- When assigning values to a user variable by other means than
SET
, the:=
operator must be used instead of=
, which is used as a comparison operator for non-SET
statements.
Below is a similar example to that provided in the documentation for clarity:mysql> SET @v1 = 13, @v2 = 10;Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @v1, @v2, @v3 = @v1 + @v2;
+------+------+-----------------+
| @v1 | @v2 | @v3 = @v1 + @v2 |
+------+------+-----------------+
| 13 | 10 | NULL |
+------+------+-----------------+
1 row in set (0.00 sec)mysql> SELECT @v1, @v2, @v3 := @v1 + @v2;
+------+------+------------------+
| @v1 | @v2 | @v3 := @v1 + @v2 |
+------+------+------------------+
| 13 | 10 | 23 |
+------+------+------------------+
1 row in set (0.00 sec)Notice when using the
=
operator in theSELECT @v1, @v2, @v3 = @v1 + @v2;
SELECT
statement, the@v3
variable returnsNULL
due to having not been initialized. Yet, when setting the value of the@v3
variable to be the summation of@v1
and@v2
, with the:=
operator, the expected result is returned. - Integer, decimal, floating-point, binary or non-binary strings and the NULL value are available for user variable assignment. Any other type is converted to the appropriate permissible type from this list.
- User variables may not be used where a literal value is explicitly required. (ie.. The
LIMIT
clause of aSELECT
query)
About Transactions
Reminders and gotchas:
SET autocommit
can disable or enable autocommit mode for the current session. (ie..SET autocommit = 0;
disables autocommit)START TRANSACTION
implicitly disables autocommit mode, for a single series of statements.- If autocommit mode is disabled, you must use
COMMIT
to store changes to disk, orROLLBACK
to ignore them. - Generally, DDL (data definition language) statements cannot be rolled back (ie…
CREATE
,DROP
, andALTER
statements) START TRANSACTION
is standard SQL syntax.
Helpful Links
Ounce Of Prevention…
As that old saying goes, “Is worth a pound of cure.”
I thoroughly enjoyed writing this blog post. Deeply exploring user-defined session variables and transactions, I learned and absorbed many interesting and useful things surrounding them both. I truly hope you can take something I have provided here, and find use and meaning in it as well.
As always, explore the official full MySQL 5.7 Online Manual for an in-depth look.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
3 thoughts on “MySQL Transactions and User-defined session variables.”