[MDEV-18845] Introduce alter_lock to allow refusing non-online ALTER TABLE Created: 2019-03-07  Updated: 2023-11-30

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: Marko Mäkelä Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-13134 Introduce ALTER TABLE attributes ALGO... Closed
relates to MDEV-16329 Engine-independent online ALTER TABLE Closed
relates to MDEV-20590 Introduce a file format constraint to... Closed

 Description   

With MDEV-16329, the majority of ALTER TABLE operations would be possible while allowing concurrent modifications to the table. Notable exceptions include ADD … AUTO_INCREMENT and DROP PRIMARY KEY without ADD PRIMARY KEY.

It could be convenient to introduce an option that allows ALTER TABLE to behave like ALTER ONLINE TABLE: refuse the operation if it would lock the table. This would be in line with the SET alter_algorithm that was introduced in MDEV-13134: allow table-rebuilding or non-instantaneous ALTER TABLE to be refused unless an explicit ALGORITHM clause is specified by the user.

The proposal is to introduce a parameter with 3 values:

-- the current situation (LOCK=DEFAULT): choose the weakest available of LOCK=SHARED and LOCK=NONE
 
SET alter_lock=DEFAULT;
 
-- disable any non-online ALTER (return an error unless explicit LOCK=DEFAULT or LOCK=SHARED is specified)
 
SET alter_lock=NONE;
 
-- disable ALTER ONLINE TABLE (lock the table; avoid allocating any logs)
 
SET alter_lock=SHARED;

There should be no need to implicitly add LOCK=EXCLUSIVE to each ALTER TABLE statement. That locking mode is rather useless.

 Comments   
Comment by Matt White (Inactive) [ 2019-03-07 ]

What should the default value for this variable be?

Comment by Federico Razzoli [ 2019-04-15 ]

I believe that disabling any ALTER TABLE by default would be annoying and confusing for many users, and could simply break existing tools. 'DEFAULT' seems to me very reasonable.

Comment by Matt White (Inactive) [ 2019-08-06 ]

Inadvertently taking a potentially critical table offline by issuing an ALTER that implicitly gets a table lock seems orders of magnitude more annoying than having to hit the up arrow key and add a "LOCK=".

Comment by Marko Mäkelä [ 2020-02-26 ]

As part of designing this change, we should evaluate and address the comments in MDEV-20590 that were expressed about the MDEV-13134 alter_algorithm.
It is also worth noting that with MDEV-16329, LOCK=NONE will be compatible with ALGORITHM=COPY.

Comment by Johnathan Davis [ 2022-11-09 ]

The choice of DEFAULT here is a little confusing since SET has a naming collision with its own semantics of what DEFAULT means:
https://mariadb.com/kb/en/set/#default

What name would we give this choice that also happens to be the DEFAULT? alter_lock=AS_NEEDED has a clear meaning, but I'd welcome a better name.

When the LOCK= syntax is omitted from an ALTER, Xpand has an implicit strategy of LOCK=NONE, whereas MariaDB Server's default is LOCK=DEFAULT, so I'd expect each database to have a distinct outcome for what alter_lock=DEFAULT means.

https://mariadb.com/kb/en/alter-table/#lock

Comment by Federico Razzoli [ 2022-11-09 ]

Some variables, like wsrep_sst_receive_address, have an AUTO value.

Comment by Johnathan Davis [ 2022-11-09 ]

Thank you for the feedback! I actually think AUTO works really well here.

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