Uma tabela TRUNCATE ou DROP pode consumir muito tempo?

Quero dizer, poderia [TRUNCATE OR DROP TABLE] bloquear um ambiente de produção por vários minutos como DELETEs podem

Para Microsoft SQL Server, em casos raros sim.

Tanto TRUNCATE quanto DROP são operações "somente metadados", o que significa que envolvem apenas algumas alterações nas tabelas do catálogo interno. Portanto, eles nunca são intensivos em recursos. Mas ambos requerem bloqueios de modificação de esquema (Sch-M) na mesa.

Quando você solicita um bloqueio Sch-M, você deve esperar até que todos os bloqueios incompatíveis sejam liberados. Cada consulta que faz referência à tabela precisa de um bloqueio Sch-S incompatível. Depois de solicitar o bloqueio Sch-M, você aguardará a conclusão de todas as consultas em execução, qualquer nova consulta será bloqueada por trás de sua solicitação de bloqueio Sch-M.

Antes do SQL 2012, esse comportamento era um pouco diferente, pois as consultas NOLOCK/READ UNCOMMITTED podiam adquirir bloqueios Sch-S enquanto você aguarda seu Sch-M e, portanto, talvez seja necessário esperar para sempre.

Em ambos os casos, o resultado é que, se houver muitas consultas de longa duração na tabela, TRUNCATE ou DROP levarão algum tempo e bloquearão outras consultas enquanto aguardam.

Normalmente, isso não é grande coisa. Se você estiver DROPing ou TRUNCATEing em uma tabela, é improvável que outras sessões tenham consultas em execução ou precisem executar novas consultas que façam referência a ela.

Para cenários em que uma operação de metadados precisa ser executada em uma tabela ocupada, o SQL Server introduziu "Low Priority Schema Lock Waits" no SQL 2014. Isso permite que ALTER TABLE ou ALTER INDEX aguarde um bloqueio Sch-M sem bloquear novas solicitações. A sessão apenas aguarda uma pausa na atividade, um instante em que nenhuma sessão possui um bloqueio Sch-S e, em seguida, faz a alteração. Consulte Nova funcionalidade no SQL Server 2014 – Parte 3 – Baixa prioridade Aguarde uma explicação detalhada da funcionalidade, o histórico de como os bloqueios de esquema se comportavam antes do SQL 2012 e a motivação para o aprimoramento.

De qualquer forma, no caso improvável de você precisar de esperas de baixa prioridade para a tabela TRUNCATE, você pode replicá-la com ALTER TABLE … SWITCH para uma tabela de preparo, seguido de DROPping na tabela de preparo. POR EXEMPLO

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

• MyISAM -- DROPé como uma exclusão de arquivo. TRUNCATEexecuta a ação do arquivo C de remover todos os dados, mas deixando o arquivo existente.
• InnoDB com seu próprio tablespace (cf innodb_file_per_table) -- como uma exclusão de arquivo
• InnoDB sem seu próprio tablespace -- muitos blocos para liberar; isso pode levar tempo.

Uma queda de arquivo pode ser rápida ou não tão rápida, dependendo do sistema operacional. Alguns sistemas operacionais correm soltando os blocos ali mesmo; você tem que esperar. E, por ser uma operação do sistema operacional, isso pode interferir em praticamente qualquer outra coisa. Qual SO?

"Minutos" parece longo. Quantos minutos? Quantos GB?

Para MySQL, TRUCNATEé implementado como um arquivo DROP ... CREATE. Ele requer um bloqueio para o DROP, pelo qual ele aguardará. Ele não invoca ON DELETEgatilhos. Isso causa um commit implícito. Se é ou não atômico depende do mecanismo de armazenamento.

Para mais informações leia,

• https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html

Pode ser demorado? Sim. É menos seguro do que DELETE, mas mais rápido - sim, é uma otimização.