Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Deadlock for alter table (online DDL) and conflicting replace statement

Description

REPLACE statements changing the same row running in parallel on multiple nodes locking alter table with MDL.

Second node:

 Very similar issue happens on production system:

  1. There is an application thread executing several replace queries in transaction

  2. If commit returns error application re-sends last replace statement and executing commit statement

  3. One of these commit staled in "wsrep: initiating pre-commit for write set" (doing galera::Monitor<galera::ReplicatorSMM::ApplyOrder>::self_cancel)

  4. alter table statement (online index creation) is trying to get exclusive MDL.

How to Reproduce

I have a script:

and a cluster created from:

entrypoint.sh in my local directory is a modified one from percona/percona-xtradb-cluster:

I've commented out mysql_tzinfo_to_sql and added SET @@SESSION.wsrep_on=0; before SET @@SESSION.SQL_LOG_BIN=0;

On both nodes I've set global trx_commit=0, sync_binlog=0, autocommit=0

The table

filled the table with 8M records (numbers from 1-8M, pad = id).

when load.sh workload started, executed alter table test.t add key(pad); on the first node.
after 2k seconds killed commit in "wsrep: initiating replication for write set (-1) " state on the first node and after that commit (non-system thread) on the second node.

From this point I have a stale with MDL waiting

Environment

None

AFFECTED CS IDs

245144

Attachments

1

Smart Checklist

Activity

Show:

高橋嘉(SmartStyleサポート担当) September 20, 2019 at 5:04 AM

Hi Krunal,

 

Our environment is PXC 5.6.26, so I understand we should upgrade to 5.7.27. Thanks !

 

Krunal Bauskar September 20, 2019 at 3:27 AM
Edited

We have observed and fixed a bug similar to this though it popped up in different context but the symptoms look similar.

https://jira.percona.com/browse/PXC-2639

Said bug has been fixed in .7.27. Can you give it a try in your environment? I suspect you are likely hitting the same issue.

高橋嘉(SmartStyleサポート担当) September 20, 2019 at 1:35 AM

Hello team,

 

We faced the similar problem, how about fixing this bug ?

Nickolay Ihalainen February 27, 2019 at 8:06 AM

Hi,
For the record, I'm still able to reproduce lockup with online DDL, but not with ALGORITHM=COPY:

When alter table test.t add key(pad), algorithm=copy sent to the server all conflicting replace statements (replace test.t (id,pad) values(1000000,rand())) going to "Waiting for table metadata lock" on both PXC nodes without excessive load on disk subsystem.

Back to online DDL, before complete stale many certification warnings produced:

Krunal Bauskar February 21, 2019 at 10:53 AM

transactions waiting for certification are valid transactions. Killing them is inviting inconsistency. Other nodes that may have faster DDL execution (fast disk or whatever) may have completed apply of these transactions.

So I would not recommend random kill of transactions that are awaiting certification.

Done

Details

Assignee

Reporter

Time tracking

1d 5h 45m logged

Fix versions

Affects versions

Priority

Smart Checklist

Created January 29, 2019 at 7:41 AM
Updated March 6, 2024 at 10:18 PM
Resolved January 13, 2020 at 3:47 AM