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

Understanding transactions

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:

• Check whether the desired quantity of products is available. If not, we should not proceed
• Decrease the available quantity of items for the product that is being bought
• Decrease the amount of money in the online account of our customer
• Register the order so that the product is delivered to our customer

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:

• Connection checks how many products with the ID 42 are available. Only one is available, but it is enough.
• Immediately after, the connection B checks the availability of the same product. It finds that one is available.
• Connection A decreases the quantity of the product. Now, it is 0.
• Connection B decreases the same number. Now, it is -1.
• Both connections create an order. Two persons will pay for the same product; however, only one is available.

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:

• During a transaction, relevant data is locked or copied. In both these cases, two connections will not be able to modify the same rows at the same time.
• The writes will not be made effective until the COMMIT command is issued. This means that if the server crashes during the transaction, all the suboperations will be rolled back. We will not have inconsistent data (such as a payment for a product that will not be delivered).

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.

The autocommit mode

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’s limitations in MariaDB

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:

• InnoDB;
• XtraDB;
• TokuDB.

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.

Working with operators

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:

• Comparison operators;
• String operators;
• Logical operators;
• Arithmetic operators.

Comparison operators

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)

String operators

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.

The LIKE operator and its variants

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:

• _ (underscore) means: This specifies any character
• %: This denotes any sequence of 0 or more 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.

Using regular expressions with the REGEXP operator and its variants

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:

• Many developers, but not all, already know their syntax
• REGEXP uses a very expressive syntax, so the patterns can be much more complex and detailed
• REGEXP is much slower than LIKE; this should be preferred when possible
  

  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

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;
• AND;
• OR;
• XOR

The NOT operator

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

The AND operator

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

The OR operator

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

The XOR operator

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)

Arithmetic operators

MariaDB supports the operators that are necessary to execute all the basic arithmetic operations. The supported arithmetic operators are:

• + for additions
• – for subtractions
• * for multiplication
• / for division

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:

• DIV: This returns the integer part of a division without any decimal part or reminder
• MOD or %: This returns the reminder of a division

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)

Operators precedence

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):

• NOT
• AND
• XOR
• OR

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

Summary

In this article you learned about the basic transactions and operators.

Resources for Article:

---

Further resources on this subject:

• Set Up MariaDB [Article]
• Installing MariaDB on Windows and Mac OS X [Article]
• Building a Web Application with PHP and MariaDB – Introduction to caching [Article]

---