directory

  • concept
  • Isolation and isolation level
  • Implementation of transaction isolation
  • Transaction startup mode
  • How MVCC works
  • conclusion

A concept,

What exactly is a transaction? I think when you study, you have a very vague concept of transactions. Let’s take a look at transactions with a classic example.

The bank transfers between the two accounts, transferring 1000 yuan from account A to account B. The system first reduces 1000 yuan from account A, and then adds 1000 yuan to account B. If all executions are successful, the database is in consistency.

If only the amount of account A is changed, but the amount of account B is not increased, the database is in an inconsistent state, and you need to cancel the previous operation.

In this process, there will be A series of operations, such as balance query, balance addition and subtraction, and balance update, etc. These operations must be executed as A whole, and all of them must be successful or fail. Account A cannot be deducted, but some operations failed during the process, resulting in account B’s failure to update the balance. So the user is not happy, the bank is not pit me?

A transaction is a set of database operations that either all succeed or all fail.

In MySQL, transaction support is implemented at the engine level. As you now know, MySQL is a multi-engine system, but not all engines support transactions.

For example, MySQL’s native MyISAM engine does not support transactions, which is one of the reasons MyISAM was replaced by InnoDB.

Next, we’ll take InnoDB as an example to tease apart MySQL’s specific implementation of transaction support.

Isolation and isolation level

When you think of transactions, you’ll definitely think of ACID (Atomicity, Consistency, Isolation, Durability) and the I, Isolation.

The concept of isolation level is developed to solve the problems of dirty read, non-repeatable read and phantom read when multiple transactions are executed on the database at the same time.

We know that the higher the isolation level, the less efficient it is, so in many cases we need to find a balance between the two.

The TRANSACTION isolation levels of the SQL standard include:

  1. Read uncommitted
  2. Read Committed
  3. Repeatable read
  4. Serializable

Let me explain one by one:

  1. Read uncommitted: Changes in a transaction, even if they are not committed, are visible to other transactions. Transactions can read uncommitted data, also known as dirty reads. This level causes many problems and is not significantly better than other isolation levels in terms of performance, but it lacks many of the benefits of other levels and is rarely used in practical applications or even implemented internally in some databases.

  2. Read Committed: From the start of a transaction until the commit, any changes made are not visible to other transactions. This level is sometimes called Nonrepeatable Read because the same query executed twice in the same transaction may get different results

  3. Repeatability: The results of multiple queries in the same transaction are consistent, which solves the problem of unrepeatable reads. This isolation level still does not solve the problem of Phantom Read, which occurs when a transaction reads a record in a range and another transaction inserts a new record in that range, resulting in Phantom rows when the previous transaction reads the record again

  4. Serialization: As the name implies, for the same row, write will add a write lock, read will add a read lock. When a read-write lock conflict occurs, the last accessed transaction must wait for the previous transaction to complete before execution can continue.

While read committed and repeatable reads may be difficult to understand in the above concepts, an example will be used below to illustrate this centralized isolation level. Given that there is only one column in table T and one row has the value 1, the following is the behavior of executing two transactions in chronological order.

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);
Copy the code

Next, we will explain the different return results of transaction A at different isolation levels, that is, what are the return values of V1, V2 and V3 in the figure respectively.

  1. If the isolation level is read uncommitted, the value of V1 is 2. Transaction B has not committed yet, but the result has already been seen by A. So V2 and V3 are also 2.

  2. If the isolation level is read commit, V1 is 1 and V2 has a value of 2. Transaction B’s updates are not seen by A until they are committed. So V3 is also going to be 2.

  3. If the isolation level is repeatable, V1, V2 are 1, and V3 are 2. The reason why V2 is still 1 is that the transaction must see consistent data during execution.

  4. If the isolation level is serialized, it will be locked while transaction B performs “change 1 to 2”. Transaction B cannot continue execution until transaction A commits. So from A’s point of view, V1 and V2 are equal to 1, and V3 is equal to 2.

In implementation, a view is created in the database and accessed based on the logical result of the view. At the repeatable read isolation level, this view is created when the transaction is started and is used for the entire life of the transaction.

At the read commit isolation level, this view is created at the start of each SQL statement execution. It is important to note that reading the uncommitted isolation level directly returns the latest value on the record, with no view concept; In the serialization isolation level, locking is used directly to avoid parallel access.

Note that the behavior of each database is different. The default isolation boundary of Oracle database is read commit. Therefore, when migrating between different database types, it is important to set MYSQL isolation level to read commit to ensure consistency of database isolation level.

This can be configured by setting the value of the startup parameter transaction-ISOLATION to read-committed. You can use Show variables to see the current values.

Each isolation level has its own usage scenario, which you will need to determine for your business. I think you might ask, when do you need a “repeatable” scenario? Let’s look at an example of data proofreading logic.

Suppose you are managing a personal bank account. One holds the balance at the end of each month and the other holds the statement details. At this time, you need to do data proofreading, that is, judge whether the difference between the balance of last month and the current balance is consistent with the statement details of this month. You want to be able to proofread without affecting your results if a user makes a new transaction.

This is where the repeatable read isolation level comes in handy. The view when a transaction is started can be considered static and not affected by updates to other transactions.

Implementation of transaction isolation

The next step is to explore how transaction isolation is implemented in terms of repeatability.

In MySQL, virtually every update record records a rollback operation. The latest value on the record can be rolled back to the value of the previous state.

If a value is changed from 1 to 2, 3, and 4 in order, the rollback log will have something like the following.

You can see that the current value is 4, and you can see that transactions started at different times have different read-views during query. As you can see, in views A, B, and C, the value of this record is 1, 2, and 4, respectively. The same record can have multiple versions in the system, which is the database multi-version Concurrency Control (MVCC).

For read-view A, to get to 1, the current value must be rolled back by all of the operations in the graph. At the same time, you can see that even if there is another transaction changing 4 to 5, this transaction will not conflict with the corresponding transaction of read-view A, B, and C.

Rollback logs cannot be kept forever, you may ask. When should they be deleted?

This is definitely not to be kept forever, only when no longer needed to delete. The system determines that these rollback logs are deleted when no transaction needs them.

So when does it stop? This is when there is no read-view older than the rollback log in the system.

Based on the above, let’s discuss why it is recommended that you avoid using long transactions.

Long transactions mean that there are old transaction views in the system. Since these transactions can access any data in the database at any time, any rollback records that may be used in the database must be retained until the transaction commits, which can lead to a large storage footprint.

In MySQL 5.5 and earlier, rollback logs are stored in ibData files along with the data dictionary, and the file does not get smaller even if long transactions are eventually committed and rollback segments are cleaned. I’ve seen only 20GB of data and 200GB of libraries for rollback segments. Eventually the entire library had to be rebuilt to clean up the rollback section.

In addition to the impact on rollback segments, long transactions also take up lock resources and can drag down entire libraries, which we’ll cover later when we talk about locking.

Iv. Transaction startup mode

MySQL transactions can be started in the following ways:

  1. Explicitly start a transaction statement,begin 或 start transaction. The accompanying commit statement iscommit, the rollback statement isrollback.
  2. set autocommit=0This command will turn off auto-commit for this thread. That means if you only perform oneselectStatement, the transaction starts and does not commit automatically. The transaction persists until you take the initiative to execute itcommit 或 rollbackStatement, or disconnect.

Some client connection frameworks will execute a set autocommit=0 command by default after a successful connection. This results in subsequent queries being in a transaction, which results in an unexpectedly long transaction if the connection is long.

Therefore, I recommend that you always start a transaction explicitly with set Autocommit =1.

But some developers struggle with the issue of one more interaction. For a business that requires frequent transactions, the second approach eliminates the need to actively execute BEGIN once at the beginning of each transaction, reducing the number of statements exchanged. If you have this concern, I recommend using commit work and chain syntax.

In the case of autoCOMMIT 1, the transaction is explicitly started with BEGIN and committed if commit is performed. If you commit work and chain, the transaction is committed and the next transaction is automatically started, eliminating the overhead of executing the BEGIN statement again. It also has the benefit of knowing explicitly whether each statement is in a transaction from a program development perspective.

You can query long transactions in the Innodb_trx table of the Information_SCHEMA library, such as the following statement, for transactions that last longer than 60 seconds.

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
Copy the code

5. Working principle of MVCC

At the repeatable read isolation level, a transaction “takes a snapshot” when it is started. Note that this snapshot is based on the entire database. If a database contains hundreds of gigabytes of data and I start a transaction, MYSQL will have to copy hundreds of gigabytes of data. This process is very slow. But why don’t we feel it 🈵️?

In fact, we don’t need to copy 100 gigabytes of data.

Let’s take a look at how this snapshot is implemented. Each transaction in InnoDB has a unique transaction ID called transaction ID. It is applied to InnoDB’s transaction system at the beginning of a transaction and is applied in a strict ascending order.

Each time a transaction updates the data, a new version of the data is generated and the transaction ID is assigned to the transaction ID of the data version, called Row trx_id. At the same time, the old version of the data is kept, and in the new version of the data, the information can be accessed directly. This also indicates that there may be multiple versions (rows) of a row in a data table, each with its own ROW_trx_id.

A graph is used to illustrate the state of a record after it is continuously updated by multiple transactions, as shown below:

The figure uses parentheses to represent four versions of a row. The latest version is V4 and the k value is 12. It was updated by a transaction with transaction ID 25, so its row trx_id is also 25.

Didn’t the previous article say that statement updates generate undo logs, you might ask? So, where’s the Undo log?

In fact, the three dotted arrows in Figure 2 are undo log; V1, V2, and V3 do not exist physically, but are calculated based on the current version and undo log. For example, when V2 is needed, V4 performs U3 and U2 in turn.

Now that we understand the concepts of multiple versions and row trx_id, let’s think about how InnoDB defines a “100 GB” snapshot.

According to the definition of repeatable read, when a transaction is started, the results of all committed transactions can be seen. Later, however, updates from other transactions are not visible to it while the transaction is executing.

Therefore, a transaction only needs to declare at startup that it recognizes a data version that was generated before I started it, based on the time I started it; If IT was generated after I started it, I don’t recognize it, I have to find the previous version of it.

Of course, if the “previous version” is not visible either, you have to move on. Also, if the transaction updates the data itself, it still needs to recognize it. As an implementation, InnoDB constructs an array for each transaction that holds all transaction ids that are currently “active” at the moment the transaction is started. “Active” means it’s started but not yet submitted.

The minimum value of transaction ids in the array is denoted as the low watermark, and the maximum value of transaction ids that have been created in the current system plus 1 is denoted as the high watermark.

This array of views, together with the high water level, constitutes a read-view of the current transaction. The visibility rule for the data version is based on the row trx_id of the data compared to the consistency view.

This view array divides all row trx_id into several different cases. As shown below:

Above is the database version visibility rule. For the moment the transaction is started, a data version of Row trx_id has the following possibilities:

  1. If it falls in green, it indicates that the version was either committed or generated by the current transaction itself, and this data is visible;

  2. If it falls in gray, it indicates that the version was generated by a transaction to be started in the future and is definitely not visible.

  3. If it’s in pink, there are two things

    • (a) If row trx_id is in an array, this version is not visible because it was generated by a transaction that has not yet committed;

    • (b) If row trx_id is not in the array, this version is generated by a committed transaction.

For example, for the data in Figure 2, if there is a transaction with a low water level of 21, then when it accesses the row, it calculates V3 from V4 through U3, so it looks like the row has a value of 11.

You see, with this declaration, any subsequent updates to the system are independent of what the transaction sees. The snapshot of the transaction is “static” because the version generated by subsequent updates must be in case 2 or 3(a) above, for which the new data version does not exist.

So there you have it, InnoDB takes advantage of the fact that all data has multiple versions and implements the ability to create snapshots in seconds.

Let’s consolidate our knowledge of MVCC with an example:

Here is an initialization statement for a table with only two rows.

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);
Copy the code

Begin /start transaction commands are not the starting point of a transaction, the transaction is actually started by the first statement that operates on the InnoDB table after they are executed. If you want to start a transaction immediately, use the start Transaction with consistent snapshot command.

Note also that our examples default to autoCOMMIT =1 unless otherwise specified.

In this example, transaction C does not explicitly use begin/commit, indicating that the UPDATE statement is itself a transaction and will be committed automatically when the statement completes. Transaction B queries after updating rows; Transaction A is queried in A read-only transaction and in chronological order after the query of transaction B.

Let’s take A look at the three transactions in the following figure and see what the statement of transaction A returns.

Answer: Is it A little strange that transaction B checks k for 3 while transaction A checks k for 1?

Next, we use hypothesis analysis to make the following assumptions:

  1. Before transaction A starts, there is only one active transaction ID 99 in the system.

  2. The version numbers of transactions A, B, and C are 100, 101, and 102 respectively, and there are only four transactions in the system.

  3. The row trx_id for (1,1) is 90 before three transactions start.

Thus, the view array for transaction A is [99,100], the view array for transaction B is [99,100,101], and the view array for transaction C is [99,100,101,102].

In order to facilitate our analysis, we will analyze it through a graph, as shown below:

Start transaction with consistent snapshot; The meaning is to create a consistency snapshot that lasts for the entire transaction starting from this statement. Therefore, at the read commit isolation level, this usage is meaningless and is equivalent to normal Start Transaction.

As you can see from the figure, the first effective update is transaction C, which changes the data from (1,1) to (1,2). At this point, the latest version of row trx_id for this data is 102, and the 90 version is history.

The second effective update is transaction B, which changes the data from (1,2) to (1,3). At this point, the latest version of the data (row trx_id) is 101, and 102 becomes the historical version.

You may have noticed that at the time of transaction A’s query, transaction B had not committed yet, but the version (1,3) it generated became the current version. But this version must be invisible to transaction A, otherwise it becomes A dirty read.

Ok, now transaction A is going to read the data, and its view array is [99,100]. Of course, the data is read from the current version. So, the flow of reading data for transaction A query looks like this:

  1. Row trx_id=101; row trx_id=101;

  2. Row trx_id=102; row trx_id=102; row trx_id=102;

  3. (1,1) row trx_id=90, which is lower than the low water level and is visible in green.

In this way, although the row of data is modified during the execution, transaction A sees the same result of this row of data no matter when it queries, so we call it consistent read.

This judgment rule was directly translated from code logic by me using some data and high performance MYSQL, but as you can see, it is cumbersome to use for human flesh analysis of visibility.

A data version, to a transactional view, has three cases in addition to its own updates being always visible:

  1. Version not submitted, not visible;

  2. The version is committed, but not visible, after the view is created;

  3. The version is committed and is visible before the view is created.

Now, we use this rule to determine the result of the query in Figure 4. The view array for the query statement of transaction A is generated when transaction A starts, at which point:

  • (1,3) has not been submitted, which belongs to case 1 and is not visible;

  • (1,2) although committed, but after the view array was created, belongs to case 2, invisible;

  • (1,1) is submitted before the view array is created, visible.

Do you see how much easier it is to analyze without comparing numbers and just using chronological order? So, we’re going to use this rule for the rest of the analysis.

Do you have a question: transaction B update statement, if read according to consistency, it seems that the result is wrong?

The view array for transaction B is created first, and then the view array for transaction C is committed.

Indeed, if transaction B queries the data once before updating, the value of k returned by the query is indeed 1.

However, when it comes time to update the data, it can no longer update the historical version, otherwise the update of transaction C will be lost.

Therefore, the set k=k+1 of transaction B is operated on the basis of (1,2), which uses such a rule: update data is read before write, and this read can only read the current value, called ** current read.

Therefore, when the current read data is (1,2), the update yields a new version of the data (1,3) whose row trx_id is 101. Therefore, when executing the transaction B query statement, the version number of the transaction B query statement is 101, and the version number of the latest data is 101, which is its own update and can be used directly. Therefore, the value of k obtained by the query is 3.

Here we mentioned a concept called current read. In fact, except for update statements, select statements, if locked, are read currently.

Select * from t where id=1 and lock in share mode or for update; select * from T where id=1 and lock in share mode The following two select statements add read locks (S locks, shared locks) and write locks (X locks, exclusive locks).

mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;
Copy the code

What if, instead of committing immediately, transaction C becomes the following transaction C ‘? As shown below:

The difference between transaction C ‘and transaction B is that the update statement is initiated before it commits. As mentioned earlier, although transaction C ‘has not yet committed, version (1,2) has been generated and is the latest version. So what happens to the update statement of transaction B?

At this point, our two-phase locking protocol will come into play. Transaction C ‘is not committed, that is, the write lock on version (1,2) has not been released. Transaction B, which is the current read, must read the latest version and must be locked, so it is locked and must wait until transaction C ‘releases the lock before it can continue with its current read.

So how is the repeatable read-ability of transactions back to the previous isolation boundaries implemented?

The core of repeatable reading is consistent Read. When a transaction updates data, only the current read can be used. If the row lock of the current record is occupied by another transaction, the lock wait is entered.

Read commit logic is similar to repeatable read logic, the main difference being:

  1. At the repeatable read isolation level, you only need to create a consistency view at the beginning of a transaction, which is shared by other queries in the transaction.

  2. At read commit isolation level, a new view is recalculated before each statement is executed.

At the read commit isolation level, what k should be checked by the query statement of transaction A and transaction B? As shown below:

You can see that the view array of the query statement of transaction A is created when the statement is executed, and the generation time of the timeline (1,2) and (1,3) are all before the creation time of the view array.

However, at this point :(1,3) has not been committed, which is case 1 and invisible; (1,2) submitted, belonging to case 3, visible. So the transaction A query returns k=2. Obviously, transaction B query result k=3.

Six, summarized

This article analyzes the transaction principle of MySQL from the bottom, hope to help you, finally don’t forget to like!!