[MDEV-13697] DB_TRX_ID is not always reset when the history is removed Created: 2017-09-01  Updated: 2021-07-12  Resolved: 2018-04-16

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.3.1
Fix Version/s: 10.3.7

Type: Bug Priority: Major
Reporter: Marko Mäkelä Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: performance

Issue Links:
Blocks
is blocked by MDEV-12288 Reset DB_TRX_ID when the history is r... Closed
is blocked by MDEV-13536 DB_TRX_ID is not actually being reset... Closed
Problem/Incident
causes MDEV-16143 Assertion failure in rec_offs_get_n_a... Closed
Relates
relates to MDEV-13603 innodb_fast_shutdown=0 may fail to pu... Closed
relates to MDEV-13654 Various crashes due to DB_TRX_ID mism... Closed
relates to MDEV-18706 ER_LOCK_DEADLOCK on concurrent read a... Stalled
Sprint: 10.3.6-1

 Description   

When an InnoDB transaction is about to update a record, two steps will be performed before the modification of the clustered index tree can start:

  1. the transaction must be able to acquire an exclusive lock on the record
  2. writing an undo log record for rolling back the operation must succeed

This bug is about the undo log processing. We can get to that phase only if the record was last modified by a committed transaction.

In the scenario of this bug, that committed transaction would still exist in the system in the purge queue, and the record would still carry a nonzero DB_TRX_ID:

diff --git a/mysql-test/suite/innodb/t/dml_purge.test b/mysql-test/suite/innodb/t/dml_purge.test
 
index 93b7f56111c..07d071517ff 100644
 
--- a/mysql-test/suite/innodb/t/dml_purge.test
 
+++ b/mysql-test/suite/innodb/t/dml_purge.test
 
@@ -15,11 +15,23 @@ SET GLOBAL innodb_purge_rseg_truncate_frequency = 1;
 
 CREATE TABLE t1(a INT PRIMARY KEY, b INT NOT NULL)
 
 ROW_FORMAT=REDUNDANT ENGINE=InnoDB;
 
 
+--connect (prevent_purge,localhost,root)
 
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
 
+
 
+--connection default
 
 INSERT INTO t1 VALUES(1,2),(3,4);
 
 UPDATE t1 SET b=-3 WHERE a=3;
 
 
+--connect (con1,localhost,root)
 
+BEGIN;
 
+UPDATE t1 SET b=4 WHERE a=3;
 
+--disconnect prevent_purge
 
+
 
+--connection default
 
 # Initiate a full purge, which should reset all DB_TRX_ID.
 
 --source include/wait_all_purged.inc
 
+--disconnect con1
 
+--source include/wait_all_purged.inc
 
 
 FLUSH TABLE t1 FOR EXPORT;
 
 # The following is based on innodb.table_flags:

If the old transaction is purgeable, then we should write DB_TRX_ID=0 to the undo log record, so that in case our transaction is rolled back, the history will be reset at rollback.
With the above modification to the test, there will be an unwanted result difference:

@@ -19,8 +29,8 @@
 
  DB_ROLL_PTR=0x80000000000000,
 
  b=0x80000002)
 
 header=0x000018090074 (a=0x80000003,
 
- DB_TRX_ID=0x000000000000,
 
- DB_ROLL_PTR=0x80000000000000,
 
+ DB_TRX_ID=0x000000000026,
 
+ DB_ROLL_PTR=0x04000001370317,
 
  b=0x7ffffffd)
 
 header=0x030008030000 (a=0x73757072656d756d00)
 
 UNLOCK TABLES;

In this report, there used to be a different test demonstrating a bug that affects all InnoDB versions since MySQL 3.23.49:
MDEV-13603 innodb_fast_shutdown=0 may fail to purge all history

 Comments   
Comment by Marko Mäkelä [ 2018-03-16 ]

In freshly inserted clustered index leaf page records, InnoDB should write DB_ROLL_PTR=0x80000000000000, because there never is a need to dereference the DB_ROLL_PTR. (Rollback should look up index records based on undo log records.)

Generated at Thu Feb 08 08:07:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.