In this article by Emilien Kenler and Federico Razzoli, author of the book MariaDB Essentials, he has explained in brief about transactions and operators.
(For more resources related to this topic, see here.)
A transaction is a sequence of SQL statements that are grouped into a single logical operation. Its purpose is to guarantee the integrity of data. If a transaction fails, no change will be applied to the databases. If a transaction succeeds, all the statements will succeed.
Take a look at the following example:
START TRANSACTION;
SELECT quantity FROM product WHERE id = 42;
UPDATE product
SET quantity = quantity - 10
WHERE id = 42;
UPDATE customer
SET money = money -
0(SELECT price FROM product WHERE id = 42)
WHERE id = 512;
INSERT INTO product_order
(product_id, quantity, customer_id)
VALUES (42, 10, 512);
COMMIT;
We haven’t yet discussed some of the statements used in this example. However, they are not important to understand transactions. This sequence of statements occur when a customer (whose id is 512) ordered a product (whose id is 42). As a consequence, we need to execute the following suboperations in our database:
These suboperations form a more complex operation. When a session is executing this operation, we do not want other connections to interfere. Consider the following scenarios:
This is something we definitely want to avoid. However, there is another situation that we want to avoid. Imagine that the server crashes immediately after the customer’s money is deducted. The order will not be written to the database, so the customer will end up paying for something he will not receive.
Fortunately, transactions prevent both these situations. They protect our database writes in two ways:
In this example, the transaction starts when we issue the START TRANSACTION command. Then, any number of operations can be performed. The COMMIT command makes the changes effective.
This does not mean that if a statement fails with an error, the transaction is always aborted. In many cases, the application will receive an error and will be free to decide whether the transaction should be aborted or not. To abort the current transaction, an application can execute the ROLLBACK command.
A transaction can consist of only one statement. This perfectly makes sense because the server could crash in the middle of the statement’s execution.
In many cases, we don’t want to group multiple statements in a transaction. When a transaction consists of only one statement, sending the START TRANSACTION and COMMIT statements can be annoying. For this reason, MariaDB has the autocommit mode.
By default, the autocommit mode is ON. Unless a START TRANSACTION command is explicitly used, the autocommit mode causes an implicit commit after each statement. Thus, every statement is executed in a separated transaction by default.
When the autocommit mode is OFF, a new transaction implicitly starts after each commit, and the COMMIT command needs be issued explicitly.
To turn the autocommit ON or OFF, we can use the @@autocommit server variable as follows:
follows:
MariaDB [mwa]> SET @@autocommit = OFF;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mwa]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
Transaction handling is not implemented in the core of MariaDB; instead, it is left to the storage engines. Many storage engines, such as MyISAM or MEMORY, do not implement it at all. Some of the transactional storage engines are:
In a sense, Aria tables are partially transactional. Although Aria ignores commands such as START TRANSACTION, COMMIT, and ROLLBACK, each statement is somewhat a transaction. In fact, if it writes, modifies, or deletes multiple rows, the operation completely succeeds or fails, which is similar to a transaction.
Only statements that modify data can be used in a transaction. Statements that modify a table structure (such as ALTER TABLE) implicitly commit the current transaction.
Sometimes, we may not be sure if a transaction is active or not. Usually, this happens because we are not sure if autocommit is set to ON or not or because we are not sure if the latest statement implicitly committed a transaction. In these cases, the @in_transaction variable can help us. Its value is 1 if a transaction is active and 0 if it is not. Here is an example:
MariaDB [mwa]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mwa]> SELECT @@in_transaction;
+------------------+
| @@in_transaction |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
MariaDB [mwa]> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [mwa]> SELECT @@in_transaction;
+------------------+
| @@in_transaction |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
InnoDB is optimized to execute a huge number of short transactions. If our databases are busy and performance is important to us, we should try to avoid big transactions in terms of the number of statements and execution time. This is particularly true if we have several concurrent connections that read the same tables.
In our examples, we have used several operators, such as equals (=), less-than and greater-than (<, >), and so on. Now, it is time to discuss operators in general and list the most important ones.
In general, an operator is a sign that takes one or more operands and returns a result. Several groups of operators exist in MariaDB. In this article, we will discuss the main types:
A comparison operator checks whether there is a certain relation between its operands. If the relationship exists, the operator returns 1; otherwise, it returns 0. For example, let’s take the equality operator that is probably used the most:
1 = 1 — returns 1: the equality relationship exists
1 = 0 — returns 0: no equality relationship here
In MariaDB, 1 and 0 are used in many contexts to indicate whether something is true or false. In fact, MariaDB does not have a Boolean data type, so TRUE and FALSE are merely used as aliases for 1 and 0:
TRUE = 1 — returns 1
FALSE = 0 — returns 1
TRUE = FALSE — returns 0
In a WHERE clause, a result of 0 or NULL prevents a row to be shown. All the numeric results other than 0, including negative numbers, are regarded as true in this context. Non-numeric values other than NULL need to be converted to numbers in order to be evaluated by the WHERE clause. Non-numeric strings are converted to 0, whereas numeric strings are treated as numbers. Dates are converted to nonzero numbers.Consider the following example:
WHERE 1 — is redundant; it shows all the rows
WHERE 0 — prevents all the rows from being shown
Now, let’s take a look at the following MariaDB comparison operators:
Operator | Description | Example |
= | This specifies equality | A = B |
!= | This indicates inequality | A != B |
<> | This is the synonym for != | A <> B |
< | This denotes less than | A < B |
> | This indicates greater than | A > B |
<= | This refers to less than or equals to | A <= B |
>= | This specifies greater than or equals to | A >= B |
IS NULL | This indicates that the operand is NULL | A IS NULL |
IS NOT NULL | The operand is not NULL | A IS NOT NULL |
<=> | This denotes that the operands are equal, or both are NULL | A <=> B |
BETWEEN … AND | This specifies that the left operand is within a range of values | A BETWEEN B AND C |
NOT BETWEEN … AND | This indicates that the left operand is outside the specified range | A NOT BETWEEN B AND C |
IN | This denotes that the left operand is one of the items in a given list | A IN (B, C, D) |
NOT IN | This indicates that the left operand is not in the given list | A NOT IN (B, C, D) |
Here are a couple of examples:
SELECT id FROM product WHERE price BETWEEN 100 AND 200;
DELETE FROM product WHERE id IN (100, 101, 102);
Special attention should be paid to NULL values. Almost all the preceding operators return NULL if any of their operands is NULL. The reason is quite clear, that is, as NULL represents an unknown value, any operation involving a NULL operand returns an unknown result.
However, there are some operators specifically designed to work with NULL values. IS NULL and IS NOT NULL checks whether the operand is NULL. The <=> operator is a shortcut for the following code:
a = b OR (a IS NULL AND b IS NULL)
MariaDB supports certain comparison operators that are specifically designed to work with string values. This does not mean that other operators does not work well with strings. For example, A = B perfectly works if A and B are strings. However, some particular comparisons only make sense with text values. Let’s take a look at them.
This operator is often used to check whether a string starts with a given sequence of characters, if it ends with that sequence, or if it contains the sequence. More generally, LIKE checks whether a string follows a given pattern. Its syntax is:
<string_value> LIKE <pattern>
The pattern is a string that can contain the following wildcard characters:
There is also a way to include these characters without their special meaning: the _ and % sequences represent the a_ and a% characters respectively.
For example, take a look at the following expressions:
my_text LIKE 'h_'
my_text LIKE 'h%'
The first expression returns 1 for ‘hi’, ‘ha’, or ‘ho’, but not for ‘hey’. The second expression returns 1 for all these strings, including ‘hey’.
By default, LIKE is case insensitive, meaning that ‘abc’ LIKE ‘ABC’ returns 1. Thus, it can be used to perform a case insensitive equality check. To make LIKE case sensitive, the following BINARY keyword can be used:
my_text LIKE BINARY your_text
The complement of LIKE is NOT LIKE, as shown in the following code:
<string_value> NOT LIKE <pattern>
Here are the most common uses for LIKE:
my_text LIKE 'my%' -- does my_text start with 'my'?
my_text LIKE '%my' -- does my_text end with 'my'?
my_text LIKE '%my%' -- does my_text contain 'my'?
More complex uses are possible for LIKE. For example, the following expression can be used to check whether mail is a valid e-mail address:
mail LIKE '_%@_%.__%'
The preceding code snippet checks whether mail contains at least one character, a ‘@’ character, at least one character, a dot, at least two characters in this order. In most cases, an invalid e-mail address will not pass this test.
Regular expressions are string patterns that contain a meta character with special meanings in order to perform match operations and determine whether a given string matches the given pattern or not.
The REGEXP operator is somewhat similar to LIKE. It checks whether a string matches a given pattern. However, REGEXP uses regular expressions with the syntax defined by the POSIX standard. Basically, this means that:
The regular expressions syntax is a complex topic, and it cannot be covered in this article. Developers can learn about regular expressions at www.regular-expressions.info.
The complement of REGEXP is NOT REGEXP.
Logical operators can be used to combine truth expressions that form a compound expression that can be true, false, or NULL. Depending on the truth values of its operands, a logical operator can return 1 or 0.
MariaDB supports the following logical operators:
NOT is the only logical operator that takes one operand. It inverts its truth value. If the operand is true, NOT returns 0, and if the operand is false, NOT returns 1. If the operand is NULL, NOT returns NULL.
Here is an example:
NOT 1 — returns 0
NOT 0 — returns 1
NOT 1 = 1 — returns 0
NOT 1 = NULL — returns NULL
NOT 1 <=> NULL — returns 0
AND returns 1 if both its operands are true and 0 in all other cases. Here is an example:
1 AND 1 — returns 1
0 AND 1 — returns 0
0 AND 0 — returns 0
OR returns 1 if at least one of its operators is true or 0 if both the operators are false. Here is an example:
1 OR 1 — returns 1
0 OR 1 — returns 1
0 OR 0 — returns 0
XOR stands for eXclusive OR. It is the least used logical operator. It returns 1 if only one of its operators is true or 0 if both the operands are true or false. Take a look at the following example:
1 XOR 1 — returns 0
1 XOR 0 — returns 1
0 XOR 1 –returns 1
0 XOR 0 — returns 0
A XOR B is the equivalent of the following expression:
(A OR B) AND NOT (A AND B)
Or:
(NOT A AND B) OR (A AND NOT B)
MariaDB supports the operators that are necessary to execute all the basic arithmetic operations. The supported arithmetic operators are:
Depending on the MariaDB configuration, remember that a division by 0 raises an error or returns NULL.
In addition, two more operators are useful for divisions:
Here is an example:
MariaDB [(none)]> SELECT 20 DIV 3 AS int_part, 20 MOD 3 AS modulus;
+----------+---------+
| int_part | modulus |
+----------+---------+
| 6 | 2 |
+----------+---------+
1 row in set (0.00 sec)
MariaDB does not blindly evaluate the expression from left to right. Every operator has a given precedence. The And operators that is evaluated before another one is said to have a higher precedence.
In general, arithmetic and string operators have a higher priority than logical operators. The precedence of arithmetic operators reflect their precedence in common mathematical expressions. It is very important to remember the precedence of logical operators (from the highest to the lowest):
MariaDB supports many operators, and we did not discuss all of them. Also, the exact precedence can slightly vary depending on the MariaDB configuration. The complete precedence can be found in the MariaDB KnowledgeBase, at https://mariadb.com/kb/en/mariadb/documentation/functions-and-operators/operator-precedence/.
Parenthesis can be used to force MariaDB to follow a certain order. They are also useful when we do not remember the exact precedence of the operators that we will use, as shown in the following code:
(NOT (a AND b)) OR c OR d
In this article you learned about the basic transactions and operators.
Further resources on this subject:
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…