2

A couple of months ago, I discovered in a (not so well maintained) database a totally useless table containing 430 million unindexed rows, growing by about 80 000 rows per day.

It was a top candidate for removal. However, I never got the opportunity to achieve this (a.k.a. not the priority). I did not even have the opportunity to try to delete it in a weekly refreshed dev database.

I was wondering whether a simple TRUNCATE or DROP could ever be very time-consuming? I mean, could this operation block a production environment for several minute like DELETEs can when they are not well written. Or are these safe operations?

  • Yes both the commands can be blocked on prod environment, it would be advisable to run it when nothing is accessing the table. A very good read on This SE Thread – Shanky Jul 17 '18 at 13:51
  • That is not my question. I think you missed the point. I would - of course - perform the TRUNCATE/DROP when I am sure nothing is accessing the table. – Jérôme Verstrynge Jul 17 '18 at 14:26
  • 1
    Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product and the behaviour of TRUNCATE is quite different between different DBMS products and a DELETE statement usually doesn't "block" anything other than UPDATEs or other DELETEs. – a_horse_with_no_name Jul 17 '18 at 14:29
  • It was a MySql database. – Jérôme Verstrynge Jul 17 '18 at 14:31
  • 1
    @JérômeVerstrynge - Why did you accept the MSSql Answer? (You can move the checkmark to another Answer.) – Rick James Jul 18 '18 at 1:05
2

I mean, could [TRUNCATE OR DROP TABLE] block a production environment for several minute like DELETEs can

For Microsoft SQL Server, in rare cases yes.

Both TRUNCATE and DROP are "metadata only" operations, meaning that they just involve a few changes to the internal catalog tables. So they are never resource-intensive. But they both require Schema Modification locks (Sch-M) on the table.

When you request a Sch-M lock you have to wait until all incompatible locks are released. Every query that references the table needs an incompatible Sch-S lock. After you request the Sch-M lock, you will wait for all running queries to complete, any new query will be blocked behind your Sch-M lock request.

Before SQL 2012 this behavior was slightly different, as NOLOCK/READ UNCOMMITTED queries were able to acquire Sch-S locks while you wait for your Sch-M, and so you might have to wait forever.

In either case the result is that if there are lots of long-running queries against the table, the TRUNCATE or DROP will both take some time, and block other queries while it waits.

Normally this isn't a big deal. If you are DROPing or TRUNCATEing a table, it's unlikely that other sessions will have running queries against it, or need to run new queries that reference it.

For scenarios where a metadata operation needs to be performed on a busy table SQL Server introduced "Low Priority Schema Lock Waits" in SQL 2014. This allows ALTER TABLE or ALTER INDEX to wait for a Sch-M lock without blocking new requests. The session just waits for a lull in the activity, an instant where no session owns a Sch-S lock, and then makes the change. See New functionality in SQL Server 2014 – Part 3 – Low Priority Wait for a detailed explanation of the functionality, the history of how Schema locks behaved before SQL 2012, and the motivation for the enhancement.

Anyway in the unlikely event that you need low priority waits for TRUNCATE table, you can replicate it with ALTER TABLE … SWITCH to a staging table, followed by DROPping the staging table. EG

create table t_stage ...;

alter table t   
switch to t_stage
with ( wait_at_low_priority ( max_duration = 4 minutes, abort_after_wait = self ) )  

drop table t_stage
  • 4
    OP said he has MySQL database. – Shanky Jul 17 '18 at 14:47
  • 2
    The question wasn't originally tagged as MySQL, and since there is no generic answer to this question, I just explained the SQL Server behavior. – David Browne - Microsoft Jul 20 '18 at 18:49
3
  • MyISAM -- DROP is like a file delete. TRUNCATE performs the C file action of removing all the data but leaving the file in existence.
  • InnoDB with its own tablespace (cf innodb_file_per_table) -- like a file delete
  • InnoDB without its own tablespace -- lots of blocks to free up; this could take time.

A file drop can be fast or not so fast, depending on the Operating system. Some OS's run around freeing up the blocks right then and there; you have to wait. And, because it is an OS operation, this may interfere with virtually anything else. What OS?

"Minutes" seems long. How many minutes? How many GB?

  • The minutes comment wasn't a statement that he had tried this and things had been blocked for minutes, but the question - could things be blocked for that long? – RDFozz Jul 20 '18 at 18:49
  • 1
    @RDFozz - I have seen an annoying long number of seconds, but I did not stop what I was doing long enough to record the OS, the filesystem, HDD/SSD, and the size of the file being deleted. I suspect a terabyte could take "minutes" on certain filesystems with HDD. – Rick James Jul 20 '18 at 18:59
2

For MySQL, TRUCNATE is implemented as a DROP ... CREATE. It requires a lock for the DROP, which it will wait for. It does not invoke ON DELETE triggers. It causes an implicit commit. Whether or not it's atomic depends on storage engine.

For more information read,

Can it be time consuming? Yes. Is it less-safe than DELETE, but faster -- yes it's an optimization.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.