Início / dba / Perguntas / 335476
Accepted
DooDoo
Asked: 2024-02-03 02:53:24 +0800 CST

Trabalho de gerenciamento de transações em instância ou banco de dados

  • 772

Lembro-me da universidade que:

Por padrão, inserir, excluir e atualizar em 2 ou mais fontes de dados não pode ser participante de uma transação

Por favor, considere este código:

begin tran Insert2

begin try
    Insert into Northwind.dbo.Categories ([CategoryName], [Description]) 
    values ('New Category', 'Some Desc')
    
    Insert into [TestDB].[dbo].[tblRate]([Year], [Month], [Rate])
    values(1111, 1, null) <-- Failed because of null value

    commit tran Insert2
end try
begin catch
    rollback tran Insert2;
    throw;
End catch

Não consigo criar um script que execute um Insert no Northwindbanco de dados e outro Insert não execute, TestDBmas a reversão não pode excluir a linha inserida no Northwindbanco de dados.

Questão 1) As transações são gerenciadas sob um Instanceou gerenciadas sob um Database? Quero dizer, vários bancos de dados podem existir em uma determinada instância, o participante pode participar de uma transação?

Questão 2) Se TestDBexistir em outra instância do SQL Server (por exemplo MyServer2), é possível incluí-lo em uma transação com a instância atual (por exemplo MyServer1).

Você poderia me mostrar um código de exemplo?

Obrigado

sql-server

2 respostas

  1. Best Answer

    Lembro-me da universidade que:

    Por padrão, inserir, excluir e atualizar em 2 ou mais fontes de dados não pode ser participante de uma transação

    Parece que foi tirado do contexto, porque como está escrito atualmente, sem quaisquer outros detalhes, é uma afirmação incorreta.

    Você pode aplicar diversas instruções DML ( INSERT, UPDATE, DELETE) a diversas fontes de dados na mesma transação. No SQL Server, essa transação só precisa ser definida explicitamente em torno de todas as instruções DML que você deseja participar na mesma transação. Por exemplo:

    BEGIN TRANSACTION

UPDATE TableA
SET Column1 = 0;

INSERT INTO TableB (Column2)
VALUES (123);

ROLLBACK;

    No exemplo acima, duas instruções diferentes afetam duas tabelas diferentes e ambas são revertidas no final da transação.

    Sem especificar explicitamente uma transação em torno deles, cada instrução é executada atomicamente como sua própria transação, individualmente, por instrução.

    Não consigo criar um script que um Insert execute no banco de dados Northwind e outro Insert não execute no TestDB, mas a reversão não pode excluir a linha inserida no banco de dados Northwind.

    Desculpe, não entendo o que você está tentando dizer aqui. Você pode fornecer um exemplo com dbfiddle.uk , talvez?

    Questão 1) As transações são gerenciadas em uma instância ou em um banco de dados? Quero dizer, vários bancos de dados podem existir em uma determinada instância, o participante pode participar de uma transação?

    Sim, vários bancos de dados podem participar da mesma transação. Uma transação pode ser definida para ser tão granular quanto você desejar, ou tão macroscópica quanto você desejar, e incluir qualquer número de fontes de dados, dentro do mesmo banco de dados ou não.

    Questão 2) Se TestDB existir em outra instância do SQL Server (por exemplo MyServer2), é possível incluí-lo em uma transação com a instância atual (por exemplo MyServer1).

    Sim, isso é chamado de transação distribuída .

    Você poderia me mostrar um código de exemplo?

    Claro, aqui está um código de um exemplo nos documentos mencionados acima sobre transações distribuídas:

    USE AdventureWorks2022;
GO

BEGIN DISTRIBUTED TRANSACTION;

-- Delete candidate from local instance.
DELETE AdventureWorks2022.HumanResources.JobCandidate
    WHERE JobCandidateID = 13;

-- Delete candidate from remote instance.
DELETE RemoteServer.AdventureWorks2022.HumanResources.JobCandidate
    WHERE JobCandidateID = 13;

COMMIT TRANSACTION;

    Neste exemplo, uma exclusão ocorre na cópia local da tabela JobCandidatee também na cópia remota dela de outra instância do SQL Server, tudo dentro da mesma transação. Se ocorresse um erro e ocorresse uma reversão, ambas as alterações também seriam desfeitas.

    Observe que, para usar transações distribuídas, o Microsoft Distributed Transaction Coordinator (MS DTC) precisa estar habilitado e configurado corretamente no servidor remoto.

    • 1

  2. Se você quiser fazer duas alterações e a segunda não deve tocar na primeira - basta separá-las.

    Insert into Northwind.dbo.Categories ([CategoryName], [Description]) 
    values ('New Category', 'Some Desc')

commit -- and the changes to Northwind are permanent

Insert into [TestDB].[dbo].[tblRate]([Year], [Month], [Rate])
    values(1111, 1, null) <-- Failed because of null value
-- if command failed - it rolled back automatically, no need for rollback

    A transação é gerenciada em “sessão”. Você se conecta ao servidor e até desconectar - isso é uma sessão. Quaisquer alterações em qualquer número de bancos de dados nesse servidor são uma única transação. Tecnicamente: você faz uma alteração, ela é gravada nas páginas sujas do log, você confirma - todas as alterações aplicadas a todos os dispositivos de armazenamento gerenciados por esse servidor, você reverte - todas as alterações escritas nas páginas sujas por você nesta transação são abandonadas. Não importa quantos bancos de dados estão incluídos na transação.

    Se uma de suas tabelas for na verdade uma tabela vinculada a um servidor diferente, cada atualização individual dessa tabela será na verdade uma nova sessão nesse servidor secundário e fora do mecanismo de transação.

    begin tran
update tbl set f1=1 -- success
update [srv2].db.dbo.tbl2 set primary_key=1 -- failed and autorolled back
commit -- change to local tbl commited

begin tran
update tbl set f1=1 -- success
update [srv2].db.dbo.tbl2 set f2=1 -- success and autocommit
rollback -- change to tbl rolled back, but change to tbl2 on secondary server stays

    E sim, se você tiver duas "fontes de dados", elas não poderão estar na mesma transação. A "fonte de dados" geralmente define uma conexão de um cliente a um servidor. E se você tiver duas “fontes de dados” isso implica duas conexões e, portanto, duas transações independentes.

    • -2

relate perguntas