[MDEV-13697] DB_TRX_ID is not always reset when the history is removed - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3.1
Fix Version/s: 10.3.7
Component/s: Storage Engine - InnoDB
Labels:
- performance

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:

the transaction must be able to acquire an exclusive lock on the record
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

Attachments

Issue Links

causes

MDEV-16143 Assertion failure in rec_offs_get_n_alloc() due to row_trx_id_offset()

Closed

is blocked by

MDEV-12288 Reset DB_TRX_ID when the history is removed, to speed up MVCC

Closed

MDEV-13536 DB_TRX_ID is not actually being reset when the history is removed

Closed

relates to

MDEV-13603 innodb_fast_shutdown=0 may fail to purge all history

Closed

MDEV-13654 Various crashes due to DB_TRX_ID mismatch in table-rebuilding ALTER TABLE…LOCK=NONE

Closed

MDEV-18706 ER_LOCK_DEADLOCK on concurrent read and insert into already locked gap

In Review

(1 relates to)

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä created issue - 2017-09-01 11:26

Marko Mäkelä made changes - 2017-09-01 11:26

Field	Original Value	New Value
Link		This issue is blocked by ~~MDEV-12288~~ [ ~~MDEV-12288~~ ]

Marko Mäkelä made changes - 2017-09-01 11:26

Link

This issue is blocked by ~~MDEV-13536~~ [ ~~MDEV-13536~~ ]

Marko Mäkelä made changes - 2017-09-01 11:26

Status

Open [ 1 ]

Confirmed [ 10101 ]

Marko Mäkelä made changes - 2017-09-01 11:29

Link

This issue relates to ~~MDEV-13654~~ [ ~~MDEV-13654~~ ]

Marko Mäkelä made changes - 2017-10-24 14:45

Link

This issue relates to ~~MDEV-13603~~ [ ~~MDEV-13603~~ ]

Marko Mäkelä made changes - 2018-03-20 14:52

Sprint

10.3.6 [ 237 ]

Marko Mäkelä made changes - 2018-04-08 15:26

Comment

[ The problem that is demonstrated with the patch in the Description appears to be that slow shutdown is not always running purge to completion.

If I apply the patch to the, adjust result, and run with
{code:sh}
./mtr --mysqld=--debug=d,purge --mem --parallel=4 --repeat=10 innodb.dml_purge,4k{,,,}
{code}
then it will fail, and in the failed run, there will be messages
{quote}
?func: purge: reset DB_TRX_ID=0x…
{quote}
corresponding to the DB_TRX_ID that are shown in the result difference, but these messages were issued after server restart and not during the slow shutdown, as purge is supposed to be run.

If I change the test to avoid restart (use {{wait_all_purged.inc}} and {{FLUSH TABLE t1 FOR EXPORT}}), it does not fail for me. ]

Marko Mäkelä made changes - 2018-04-12 04:08

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:
# the transaction must be able to acquire an exclusive lock on the record
# 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.

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.
TODO: Write a test case for this scenario.

If the old transaction is accessible in some transaction’s read view, then we must write the old DB_TRX_ID,DB_ROLL_PTR to the undo log record, so that the other read views can still correctly reach the correct old version of the record.
The following test patch demonstrates that currently, the DB_TRX_ID will never be reset:
{code:diff}
diff --git a/mysql-test/suite/innodb/t/dml_purge.test b/mysql-test/suite/innodb/t/dml_purge.test
index 720acfb1577..fdab8687886 100644
--- a/mysql-test/suite/innodb/t/dml_purge.test
+++ b/mysql-test/suite/innodb/t/dml_purge.test
@@ -13,9 +13,20 @@ 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 (con1,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;

+--connection con1
+UPDATE t1 SET b=4 WHERE a=3;
+COMMIT;
+--disconnect con1
+
+--connection default
# Initiate a full purge, which should reset all DB_TRX_ID.
SET GLOBAL innodb_fast_shutdown=0;
--source include/shutdown_mysqld.inc
{code}
With this change to the test, the DB_TRX_ID,DB_ROLL_PTR will not be reset by purge.

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:
# the transaction must be able to acquire an exclusive lock on the record
# 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:
{code:diff}
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:
{code}

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:
{code:diff}
@@ -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;
{code}

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

Marko Mäkelä made changes - 2018-04-16 03:08

issue.field.resolutiondate

2018-04-16 03:08:19.0

2018-04-16 03:08:19.255

Marko Mäkelä made changes - 2018-04-16 03:08

Fix Version/s		10.3.6 [ 23003 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

Marko Mäkelä made changes - 2018-04-17 05:23

Fix Version/s		10.3.7 [ 23005 ]
Fix Version/s	10.3.6 [ 23003 ]

Marko Mäkelä made changes - 2018-05-15 19:51

Link

This issue causes ~~MDEV-16143~~ [ ~~MDEV-16143~~ ]

Aleksey Midenkov made changes - 2021-07-12 20:35

Link

This issue relates to MDEV-18706 [ MDEV-18706 ]

Sergei Golubchik made changes - 2021-12-06 21:45

Workflow

MariaDB v3 [ 82349 ]

MariaDB v4 [ 152740 ]

People

Assignee:: Marko Mäkelä

Reporter:: Marko Mäkelä

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2017-09-01 11:26

Updated:: 2021-07-12 20:35

Resolved:: 2018-04-16 03:08

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration