Skip to:

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

Add option to set lock timeouts distinctly for various statements

Description

The problem given in

https://forums.percona.com/t/pt-online-schema-change-exiting-due-to-errors-while-restoring-triggers-dbd-db-do-failed-lock-wait-timeout-exceeded-try-restarting-transaction/8911/11

shows that getting a table lock to finalize the migration should be more patient, otherwise lock may never be obtained.

 

I’m not alluding to mysql’s concept of priority.
It's a new concept of distinct tolerance to timeout among ptosc statements.

Row copies and most throttled work can certainly use 1s lock timeout.
However, terminating the migration with a “broad-hard-to-get full table write lock” should be willing to wait A LOT longer for a lock, so that it stands a chance of acquiring it. Otherwise, it’s very unlikely that a table lock would be obtainable in 1s on a table that sees incessant concurrent queries.

(also: reminder on drop_swap loosing triggers (PT-1919)).

Environment

None

Smart Checklist

Activity

Show:

bob August 6, 2023 at 12:38 AM

It's been a while since my report, and it's not all pros and no cons.

IIRC, while a table write lock is waiting to be granted, the subsequent queries are waiting behind it. That means a long timeout would have significant impact for what is supposed to be a online shema change. I suspect the 1s short timeout is still too short, but I wouldn't recommend using a timeout longer than the delay one is willing to add to all concurrent queries (ex: 30s).

There is no easy fix to avoid impacting concurrent queries. It's online ok, but not impactless. There is a point at which the DBA will want to finish the migration even if it means killing queries. Automated migrations are even more aggressive and failing for lack of availability is rarely enough a reason to abort.

james wang August 5, 2023 at 6:17 PM

any workaround for version 3.0.13 please?  thanks

Lalit Choudhary February 18, 2021 at 11:38 AM

Hi Bob,

Thank you for the Improvement report.

Details

Assignee

Reporter

Priority

Smart Checklist

Created January 29, 2021 at 7:40 PM
Updated May 17, 2024 at 3:06 PM