Shulou(Shulou.com)06/01 Report--

Walker science and technology Luo Xiaobo

1.2.3. The role of using the WITH CONSISTENT SNAPSHOT clause

When the START TRANSACTION statement uses the WITH CONSISTENT SNAPSHOT clause, a consistent read is initiated for the transaction (this clause applies only to InnoDB). Its behavior is the same as that of a SELECT statement after the execution of a START TRANSACTION statement (it gets a transaction number and occupies a pit in the read view, but no locks are requested). The WITH CONSISTENT SNAPSHOT clause does not automatically modify the current transaction isolation level. Because the WITH CONSISTENT SNAPSHOT clause requires that it be automatically enabled under the RR isolation level, consistent snapshots are enabled only when the current isolation level is RR, and the WITH CONSISTENT SNAPSHOT clause is ignored in non-RR isolation levels. Starting from MySQL 5.7.2, a warning is generated when the WITH CONSISTENT SNAPSHOT clause is ignored (similar to the warning message mentioned in the previous mysqldump and innobackupex backup process).

To gain a clearer understanding of the role of mysqldump in using the WITH CONSISTENT SNAPSHOT clause during backup, let's demonstrate what happens with or without the WITH CONSISTENT SNAPSHOT clause.

Open two sessions and manipulate the same table

From the comparison of the table above, you can see:

The function of the WITH CONSISTENT SNAPSHOT clause is equivalent to the START TRANSACTION+ SELECT statement, the purpose is to immediately add the transaction to the read view of the mvcc at the moment the transaction is started, as if the read view was fixed at the beginning of the transaction, so that the DML of other subsequent transactions will not affect the query results of the transaction, which is called consistent reading.

If you do not use the WITH CONSISTENT SNAPSHOT clause, after explicitly opening a transaction using the START TRANSACTION statement, and before executing the SELECT statement, if another transaction initiates a DML operation during this period of time, it will cause the data read by the transaction to query the table to be inconsistent with the transaction start time.

1.2.4. Use savepoint to set the role of the rollback point

As we all know, SAVEPOINT is set to roll back data that changed at this point, but mysqldump backups only use select statements to make queries, so why use savepoint? What do you need to roll back? Please see the following analysis:

The SAVEPOINT 'identifier' statement that sets a named transaction SavePoint (rollback point) for the transaction, and the string is the identifier of the transaction SavePoint.

The ROLLBACK TO SAVEPOINT statement rolls back the transaction to the location of the specified SavePoint without terminating the transaction. The modified row data of the current transaction after the rollback point will be undone (Note: InnoDB will not release the row locks of these modified and revoked rows, note that the modified row locks are stored in memory), and the newly inserted row data will also be undone after the SavePoint is set (Note: these lock information is stored on the transaction ID in the row data. These row locks are not stored separately in memory, in which case the row locks are released after the newly inserted row data is rolled back. In addition, after rolling back to a SavePoint, savepoints that are set later than that SavePoint will be deleted.

The ROLLBACK TO SAVEPOINT statement also has the effect of releasing MDL locks held by the transaction after the SavePoint is set, which is the key point where mysqldump needs to use SavePoint.

To better understand the role of mysqldump in using SAVEPOINT sp + ROLLBACK TO SAVEPOINT sp statements during backup, let's use two sessions to demonstrate what happens with and without a SavePoint?

From the above comparison, we can see that:

The purpose of mysqldump using savepoint is that when an explicitly opened transaction rolls back to the SavePoint, in addition to rolling back the data changes, it also releases the MDL locks acquired by the select statements after the SavePoint, so that the DDL statements of other sessions can be executed normally. For mysqldump, after the execution of the select statement, the data of the table has been backed up, and there is no need to continue to hold the MDL lock. Using savepoint achieves the purpose of releasing the MDL lock after the completion of the select execution. (note: in a transaction, executing the select * statement will not have a data row lock, but it will hold the MDL lock of the table.

The with consistent snapshot clause is very important for mysqldump to achieve consistent backup, not only for data consistency, but also for the table definition to keep the transaction open when using this clause. Therefore, you can see from the above comparison that after using the with consistent snapshot clause to start a consistent snapshot transaction, once the table structure definition is changed, the transaction will not be able to query the table repeatedly.

From the demonstration above, we can also see that after a transaction is explicitly opened using the with consistent snapshot clause, if the transaction does not do anything to any table, no lock is obtained at this time, so if other transactions perform DDL operations on the table before the transaction performs operations on the table, it will cause the transaction to no longer query the table, and the report structure will change. Of course, if a query is executed against a table immediately after explicitly opening a transaction, the DDL of other sessions will block; when the transaction releases the MDL lock on the table using the savepoint implementation, other sessions are allowed to execute DDL, but after the DDL statement is executed, the transaction can no longer execute queries against the table. Of course, if the with consistent snapshot clause is not used, changes to the table definition made by DDL performed by other sessions will not affect the transaction from repeatedly executing queries against the table.

In the next article "how much do you know about the backup process of mysqldump and innobackupex (3)" we will continue to introduce "those pits about mysqldump". The wonderful content should not be missed, please look forward to it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.