Recently, I have set up my personal blog. Here is the link: Babble of tobe The article will be updated in blogs and public numbers first.

The so-called Transaction is to maintain the integrity of the database by ensuring that batch operations are either fully executed or not executed at all. Take A rotten Street example: A transfers 1000 yuan to B, and the corresponding SQL statement is: (no Transaction is explicitly defined)

UPDATE deposit_table set deposit = deposit - 1000 WHERE name = 'A';
UPDATE deposit_table set deposit = deposit + 1000 WHERE name = 'B';

The results after operation are as follows:

mysql> SELECT * FROM deposit_table;
+------+---------+
| name | deposit |
+------+---------+
| A    |    3000 |
| B    |    5000 |
+------+---------+

This may cause problems. For example, after the first statement is executed, the database crashes, and the final result may be as follows (after all, we will not simulate this kind of failure):

+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    5000 |
+------+---------+

A's 1000 yuan disappeared without any reason, which is definitely not appropriate. Transaction is to solve similar problems. If transaction is used to process transfer, the corresponding SQL is:

START TRANSACTION;
UPDATE deposit_table set deposit = deposit - 1000 WHERE name = 'A';
UPDATE deposit_table set deposit = deposit + 1000 WHERE name = 'B';
COMMIT;

Just adding START TRANSACTION and COMMIT before and after the original two SQL statements can ensure that the balance of A will not decrease even if the transfer operation fails.

It's not particularly appropriate to think about this example carefully, because the database fault recovery technology (to be discussed later) will affect the final result, and it's not easy to simulate this kind of fault, and the final result can only be guessed:) but I can't think of other more appropriate examples... If you have better examples, please leave a message for discussion.

Next, we will discuss some features and (some) implementation details of the transaction in detail.

ACID

• A: Atomicity (atomicity)
• C: Consistency
• 1: I solation
• D: Durability

Atomicity (atomicity)

First, we talk about two important concepts: commit and rollback. When we perform the commit operation, we will make permanent changes to the database, and perform the rollback operation, which means that the database will undo all the changes that are not committed. Note that the permanence here does not mean that the data will be flushed to the disk after the transaction is completed. Even if the data is not flushed to the disk, MySQL has a logging mechanism to ensure that the modification will not be lost.

Transaction is a unit of work that supports commit and rollback. Atomicity means that when a transaction makes multiple changes to the database, either all changes are successful when the transaction is committed, or all changes are revoked when the transaction is rolled back. This is Official documents However, some people seem to have misunderstood the commit statement. In fact, even if a statement in a transaction has an error, once you execute commit, the previous normal modifications will still be committed. MySQL will not automatically judge whether the SQL execution in the transaction is successful or not.

Let's take a look at commit and rollback with examples:

mysql> SELECT * FROM deposit_table;
+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
+------+---------+
2 rows in set (0.04 sec)

mysql> 
START TRANSACTION;
INSERT INTO deposit_table VALUES('C', 7000);
INSERT INTO deposit_table VALUES('D', 8000);
#Insert D again, because the primary key is unique, the statement will fail
INSERT INTO deposit_table VALUES('D', 9000);
COMMIT; #Submission of affairs

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

1062 - Duplicate entry 'D' for key 'PRIMARY'
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT * FROM deposit_table;
+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
| C    |    7000 |
| D    |    8000 |
+------+---------+
4 rows in set (0.04 sec)

We can see that during the execution of insert into deposit table values ('d ', 9000), because the previous statement has inserted D, the execution of this SQL statement failed, and 1062 - duplicate entry' d 'for key' primary 'error was reported, but after the COMMIT, the front modification was still submitted, which obviously did not meet our expectations.

Note: if you use Navicat's query interface, you will not be able to execute the COMMIT statement, only to the place where the error is reported. It is recommended to use the command line to execute.

So in practice, we need to determine whether to use ROLLBACK or COMMIT according to the error return value of MySQL. Like this:

# Create a stored procedure
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`()
BEGIN
	# Create a flag and set it to 1 in case of an error
	DECLARE err_flg INTEGER;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg = 1;

    START TRANSACTION;
        INSERT INTO deposit_table VALUES('C', 7000);
				INSERT INTO deposit_table VALUES('D', 8000);
				INSERT INTO deposit_table VALUES('D', 9000);
        
        # Error occurred, rollback transaction
        IF err_flg = 1 THEN
			SELECT 'SQL Err Invoked'; # Error message
            ROLLBACK;
			SELECT * FROM deposit_table;
		# No error occurred, submit directly
        ELSE
			SELECT 'TRANSACTION Success';
            COMMIT;
			SELECT * FROM deposit_table;
        END IF;
	
END

Next, we call the stored procedure:

mysql> call insert_test();
+-----------------+
| SQL Err Invoked |
+-----------------+
| SQL Err Invoked |
+-----------------+
1 row in set (0.04 sec)

+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
+------+---------+
2 rows in set (0.09 sec)

Query OK, 0 rows affected (0.00 sec)

In the result, the content of the SQL Err Invoked table printed with the error message has not been changed, which indicates that our ROLLBACK successfully rolled back the transaction and reached our expectation. If you use other languages to call MySQL's interface, you only need to get the error flag and execute ROLLBACK or COMMIT accordingly.

Consistency

Explanation on official website As follows:

The database remains in a consistent state at all times — after each commit or rollback, and while transactions are in progress. If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values.

After each commit or rollback and during the ongoing transaction, the database is always in a consistent state. If the relevant data is updated across multiple tables, the query will see all the old values or all the new values, rather than a mixture of the old and new values.

for instance:

# The definitions of tables a and b are omitted
START TRANSACTION;
UPDATE a SET name = 'a_new' WHERE name = 'a_old';
UPDATE b SET name = 'b_new' WHERE name = 'b_old';
COMMIT;

The consistency in this example means that if there is a query SELECT a.name, b.name FROM a, b at this time, the result will be either a old B old (indicating that the transaction has been rolled back or is in progress), or a new b new (indicating that the transaction has been successfully committed), instead of a old b new and a new B old.

Some blogs interpret consistency as "data conforms to constraints in the real world, such as uniqueness constraints." I personally prefer the interpretation of official documents. I have different opinions on this. It's not meaningful to tangle these concepts.

Isolation

Transaction isolation means that transactions cannot interfere with each other or see uncommitted data of each other. This isolation is achieved through a locking mechanism. We have also learned in the operating system that using locks often means a decrease in concurrency performance, because blocking or even deadlock may occur.

Of course, users can adjust the isolation level to sacrifice partial isolation to improve performance and concurrency when they are sure that transactions will not interfere with each other. As for which isolation level to use, you need to do trade off yourself.

Because isolation involves a lot of content, I will put it in the next article to explain in detail.

Durability

Transaction persistence means that once the commit operation is successful, the changes made by the transaction will not be lost due to some accidents, such as power failure, system crash and other potential threats. MySQL provides many mechanisms, such as logging technology, doublewrite buffer Wait.

I will write a separate article on MySQL log recovery technology. Here, I will talk about doublewrite buffer technology.

Although the technology is called buffer, the buffer is not in memory, but in disk. This may sound weird - since it's putting data on disk, why not write it directly to data file, but do more?

This is because InnoDB's Page Size is generally 16kb, and its data verification is also calculated for pages. In the process of brushing data into the disk, if there is power failure or other faults, the page may only write a part (partial page write). This situation cannot be solved by the redo log, because the physical operations recorded in the redo log are physical operations on the page. If the page itself is damaged, redo is meaningless. So we need a copy to restore the page when this happens.

Moreover, the buffer is written in sequence, and the overhead is much smaller than random read-write. Therefore, after double write, the performance is not reduced to the original 50%.

Common statements in transactions

• START TRANSACTION / BEGIN opens a transaction explicitly

• COMMIT commit transaction, permanently modify database

• SAVEPOINT creates a SAVEPOINT in a transaction

• Delete savepoint

• ROLLBACK rolls back the transaction, recalls all uncommitted changes, and the transaction will terminate

• ROLLBACK TO [SAVEPOINT] rolls back to the given savepoint, but the transaction does not terminate. In addition, the row lock after the savepoint will not be released. See details SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements:

  ​ InnoDB does not release the row locks that were stored in memory after the savepoint. (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.)

• SET TRANSACTION set transaction isolation level

• SET autocommit 0/1 auto commit (default auto commit)

Emphasize the autocommit parameter. By default, if you do not explicitly use START TRANSACTION / BEGIN, MySQL will treat each SQL sentence as an independent transaction. For example:

Original table structure:

mysql> SELECT * FROM deposit_table;
+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
+------+---------+
2 rows in set (0.04 sec)

New stored procedure (only START TRANSACTION deleted):

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`()
BEGIN
	#Routine body goes here...
	DECLARE err_flg INTEGER;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg = 1;

    # START TRANSACTION;
    INSERT INTO deposit_table VALUES('C', 7000);
	INSERT INTO deposit_table VALUES('D', 8000);
	INSERT INTO deposit_table VALUES('D', 9000);
        
        IF err_flg = 1 THEN
			SELECT 'SQL Err Invoked';
            ROLLBACK;
			SELECT * FROM deposit_table;
        ELSE
			SELECT 'TRANSACTION Success';
            COMMIT;
			SELECT * FROM deposit_table;
        END IF;
	
END

Result of call:

mysql> call insert_test();
+-----------------+
| SQL Err Invoked |
+-----------------+
| SQL Err Invoked |
+-----------------+
1 row in set (0.24 sec)

+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
| C    |    7000 |
| D    |    8000 |
+------+---------+
4 rows in set (0.28 sec)

Query OK, 0 rows affected (0.21 sec)

Here, we see that C and D are inserted into the deposit table even though ROLLBACK is executed. This is because there is no explicit indication of transactions. MySQL will conduct implicit transactions and automatically commit each modification, so it is unable to roll back.

There are so many basic concepts of transaction. I will talk about the isolation mechanism of transaction and the design of paradigm in the future. Please look forward!

I hope you will get something after reading my article, and look forward to your praise and forwarding!