MySQL Transactions and User-defined session variables.

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:

mysql> DESC country_new;
+--------------+---------------+------+-----+---------+-------+
| 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)
mysql> SELECT * FROM country_new;
+------------+--------------+-----------+
| 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)
mysql> DESC dup_countries;
+--------------+---------------+------+-----+---------+-------+
| 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)
mysql> SELECT * FROM dup_countries;
+------------+--------------+-----------+
| 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)
mysql> DESC world_locations;
+--------------+---------------+------+-----+---------+-------+
| 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)
mysql> SELECT * FROM world_locations;
+------------+---------------------+--------------+----------+-----------+
| 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:

Mysql Update Statements in batch file
That will do it, right?
Did you catch the error in that group of UPDATE statements?
Look again.

Mysql Update statement with error
Oops. This will cause problems.
Notice where SET country_id = 301 is highlighted with the cursor and surrounding black rectangle in the

UPDATE world_locations
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:

Mysql Transactions and user variables
And when running the session_var.sql file in batch mode:

mysql> source /home/myUser/myFolder/session_var.sql
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?

mysql> SELECT * FROM country_new;
+------------+--------------+-----------+
| 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)
mysql> SELECT * FROM dup_countries;
+------------+--------------+-----------+
| 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)
mysql> SELECT * FROM world_locations;
+------------+---------------------+--------------+----------+-----------+
| 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:

SET @variable_name = value or expression;

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 the SELECT @v1, @v2, @v3 = @v1 + @v2; SELECT statement, the @v3 variable returns NULL 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 a SELECT 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 COMMITto store changes to disk, or ROLLBACK to ignore them.
  • Generally, DDL (data definition language) statements cannot be rolled back (ie…CREATE, DROP, and ALTER 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

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.”

  1. Pingback: Understanding MySQL’s IF() function – with examples. | Digital Owl's Prose
  2. Pingback: Useful String Function – REPLACE in MySQL with examples. | Digital Owl's Prose
  3. Pingback: Utilize parameters with MySQL Stored Procedure by examples. | Digital Owl's Prose

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.