É uma má prática sempre criar uma transação?

É uma má prática criar uma transação sempre?

Depende de que contexto você está falando aqui. Se for uma atualização, recomendo usar TRANSACTIONS explicitamente. Se for um SELECT, então NÃO (explicitamente).

Mas espere, há mais para entender primeiro: Tudo no sql server está contido em uma transação.

Quando a opção de sessão IMPLICIT_TRANSACTIONSé OFFe você especifica explicitamente begin trane commit/rollbackentão isso é comumente conhecido como uma Transação Explícita . Caso contrário, você obtém uma transação de confirmação automática.

Quando IMPLICIT_TRANSACTIONSé ONuma transação implícita é iniciada automaticamente ao executar um dos tipos de instrução documentados no artigo de livros online (por exemplo , SELECT/ UPDATE/ CREATE​​) e deve ser confirmada ou revertida explicitamente. A execução de um BEGIN TRANneste modo incrementaria @@TRANCOUNTe iniciaria outra transação "aninhada")

Para alternar em qual modo você está, você usaria

SET IMPLICIT_TRANSACTIONS ON

ou

SET IMPLICIT_TRANSACTIONS OFF

select @@OPTIONS & 2

se acima retornar 2, você está no modo de transação implícito. Se retornar 0, você está em autocommit.

quanto é o custo de criar uma transação quando não é realmente necessário?

As transações são necessárias para levar o banco de dados de um estado consistente para outro estado consistente. As transações não têm custo, pois não há alternativa às transações. Consulte: Usando níveis de isolamento baseados em controle de versão de linha

Mesmo se você estiver usando um nível de isolamento read_uncommitted. É uma prática ruim? porque não deve ter problemas com travamento.

O nível de isolamento READ_UNCOMMITED permitirá leituras sujas por definição, ou seja, uma transação poderá ver as alterações não confirmadas feitas por outra transação. O que esse nível de isolamento faz é relaxar a sobrecarga de bloqueio - método de aquisição de bloqueios para proteger a simultaneidade do banco de dados.

Você pode usar isso em um nível de conexão/consulta, para que não afete outras consultas.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Encontrei um artigo interessante de Jeff Atwood descrevendo Deadlocks devido ao Dining Philosophers Puzzle e descrevendo o nível de isolamento de instantâneos de leitura confirmada .

EDITAR:

Por curiosidade, fiz alguns testes medindo o impacto no T-log com contadores Perfmon como Log Bytes Flushed/Sec, Log Flush Waits/Sec (Nº de commits por segundo que estão aguardando a liberação do LOG para ocorrer) conforme gráfico abaixo:

Código de amostra :

create table testTran (id int, Name varchar(8))
go

-- 19 sec
-- Autocommit transaction
declare @i int
set @i = 0
while @i < 100000
begin 
insert into testTran values (1,'Kin Shah')
set @i = @i+1
end
---------------------------------------------------
-- 2 sec
-- Implicit transaction
SET IMPLICIT_TRANSACTIONS ON
declare @i int
set @i = 0
while @i < 100000
begin 
insert into testTran values (1,'Kin Shah')
set @i = @i+1
end
COMMIT;
SET IMPLICIT_TRANSACTIONS OFF


----------------------------------------------------
-- 2 sec
-- Explicit transaction
declare @i int
set @i = 0
BEGIN TRAN
WHILE @i < 100000
Begin
INSERT INTO testTran values (1,'Kin Shah')
set @i = @i+1
End
COMMIT TRAN

Transações de confirmação automática : (Editado conforme destacado por @TravisGan)

• A inserção levou 19 segundos.
• Cada Autocommit irá liberar o buffer T-log para o disco devido ao autocomit (depois de @TravisGan destacado, e eu não mencionei isso) .
• O processo CHECKPOINT será concluído rapidamente, pois a quantidade de buffer de log sujo necessário para ser liberado será menor, pois ele é executado em silêncio com frequência.

Transação IMPLÍCITA E Explícita:

• A inserção levou 2 segundos.
• Para transações EXPLICIT, os buffers de log serão liberados somente quando estiverem cheios.
• Ao contrário da transação Autocommit, na transação EXPLICIT, o processo CHECKPOINT levará mais tempo, pois terá mais buffers de log para liberar (lembre-se de que os buffers de log são liberados somente quando estão cheios).

Há um DMV sys.dm_tran_database_transactions que retornará informações sobre transações no nível do banco de dados.

Obviamente, este é mais um teste simplista para mostrar o impacto. Outros fatores, como subsistema de disco, configurações de crescimento automático do banco de dados, tamanho inicial do banco de dados, outros processos em execução no mesmo servidor\banco de dados, etc, também terão influência.

A partir dos testes acima, não há quase nenhuma diferença entre transações implícitas e explícitas.

Obrigado a @TravisGan por ajudar a adicionar mais à resposta.

Uma instrução SQL sempre é executada em uma transação. Se você não iniciar um explicitamente, cada instrução SQL será executada em uma transação própria.

A única opção é se você agrupar várias instruções em uma transação. As transações que abrangem várias instruções deixam bloqueios que prejudicam a simultaneidade. Portanto, "sempre" criar uma transação não é uma boa ideia. Você deve equilibrar o custo contra o benefício.

A questão é se um grupo de operações deve ser tratado como uma única ação. Em outras palavras, todas as operações devem ser concluídas e confirmadas com êxito ou nenhuma das operações pode ser confirmada. Se você tiver um cenário que exija a leitura de dados preliminares e a execução de atualizações com base nesses dados, a leitura inicial provavelmente deve fazer parte da transação. Nota: estou evitando Selecionar/Inserir/Atualizar de propósito. O escopo da transação pode realmente estar no nível do aplicativo e envolver várias operações de banco de dados. Pense em padrões clássicos, como Reserva de assento de avião ou Consulta/Retirada de saldo bancário. É preciso ter uma visão mais ampla do problema para garantir que todo o aplicativo produza dados confiáveis ​​e consistentes.