[MDEV-16306] TRUNCATE waits for metadata lock on the tables when a SELECT is executing on it Created: 2018-05-28 Updated: 2019-03-08 |
|
| Status: | Open |
| Project: | MariaDB Server |
| Component/s: | Data Definition - Alter Table, Locking, Storage Engine - InnoDB |
| Fix Version/s: | None |
| Type: | Task | Priority: | Major |
| Reporter: | Faisal Saeed (Inactive) | Assignee: | Unassigned |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | SELECT, TRUNCATE | ||
| Issue Links: |
|
||||||||
| 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. |
| Comments |
| Comment by Marko Mäkelä [ 2018-05-28 ] |
|
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 ( |
| Comment by Marko Mäkelä [ 2018-09-26 ] |
|
|
| Comment by Sergei Golubchik [ 2019-01-26 ] |
|
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. |
| Comment by Faisal Saeed (Inactive) [ 2019-02-07 ] |
|
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 |
| Comment by Sergei Golubchik [ 2019-02-07 ] |
|
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. |