Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
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.
Attachments
Issue Links
- relates to
-
MDEV-13134 Introduce ALTER TABLE attributes ALGORITHM=NOCOPY and ALGORITHM=INSTANT
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
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.
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=".
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.
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.
Some variables, like wsrep_sst_receive_address, have an AUTO value.
Thank you for the feedback! I actually think AUTO works really well here.
What should the default value for this variable be?