Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
A ticket was raised earlier as a MariaDB bug that when a SELECT is executing on a table, parallel TRUNCATE waits for the Metadata Lock. After some explanation of this behavior and successful reproduction of this in their test environment, It was categorized as not a BUG.
However, they still want this TRUNCATE behaviour changed and requesting it as a new FEATURE where they could pass an additional parameter to the TRUNCATE statement and it will ignore all the LOCKS on the table and proceed with its DROP/RECREATE, aborting all SELECT's that are using this table. This is how Oracle does it.
This according to them is critical as it impacts their daily operations.
Attachments
Issue Links
- relates to
-
MDEV-13564 TRUNCATE TABLE and undo tablespace truncation are not compatible with Mariabackup
-
- Closed
-
Activity
MDEV-13564 in 10.3.10 and 10.2.19 implements TRUNCATE TABLE internally inside InnoDB as a combination of RENAME TABLE, CREATE TABLE and deferred DROP TABLE. With that in place, implementing a less locking TRUNCATE variant should only require some changes in the SQL layer, outside InnoDB.
Does TRUNCATE in Oracle really aborts all SELECT's? Do you have a link to the manual that confirms it? I wasn't able to find that in Oracle's documentation.
In MariaDB TRUNCATE is just a regular DDL statement, like any other, if we do this "abort running SELECT's" behavior, it probably should work for all DDLs.
Hi Sergei, when the customer reported this behavior I installed Oracle on my laptop and tested it and it did confirm that TRUNCATE killed the running SQL statement. I was actually shocked to see it myself!
If you want, I can retest it and share the results again?
Here is what I found, on Oracle forums, these users noticed the same behavior, for me it said "Table nolonger exists on a long running SELECT but for these folks data was still fetched even the table was dropped and purged"
https://community.oracle.com/thread/2445621?start=15&tstart=0
I personally see it as a Oracle bug. And quite illogical behavior too, for example DROP TABLE doesn't abort the running SELECT, DELETE doesn't abort the running SELECT, why should TRUNCATE do it? There's no reason to explain it on a user level, without going into implementation details, such as segments, caches, recycle bins, and whatnot.
We don't have to be bug-compatible with Oracle.
I think that there are two parts of this feature request. The first part is the metadata locking, which takes place in the SQL layer.
The second part is the implementation of TRUNCATE TABLE inside storage engines. When it comes to InnoDB, it should be technically feasible to relax the locking requirements. If InnoDB internally implemented TRUNCATE as a RENAME TABLE, CREATE TABLE and deferred DROP TABLE, any concurrently running SELECT would not be a problem; the SELECT could keep running, and once all handles are closed, the original table could be dropped. This would also make InnoDB more backup-friendly (
MDEV-13564). Implementing this would depend onMDEV-14717, which is present in MariaDB 10.3.