主页 / dba / 问题 / 335476
Accepted
DooDoo
Asked: 2024-02-03 02:53:24 +0800 CST

事务管理工作在实例或数据库下

  • 772

我记得大学时说过：

默认情况下，在一个事务下不能参与对 2 个或多个数据源进行插入、删除和更新

请考虑这段代码：

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

我无法创建一个插入在Northwind数据库上执行而另一个插入不执行的脚本TestDB，但回滚无法删除Northwind数据库上插入的行。

问题 1) 事务是在 a 下管理Instance还是在 a 下管理Database？我的意思是，给定实例中存在的多个数据库可以参与一个事务吗？

问题 2) 如果TestDB存在于 SQL Server 的另一个实例中（例如MyServer2），是否可以将其包含在当前实例的一个事务中（例如MyServer1）。

您能给我看一下示例代码吗？

谢谢

sql-server

2 个回答

  1. Best Answer

    我记得大学时说过：

    默认情况下，在一个事务下不能参与对 2 个或多个数据源进行插入、删除和更新

    这听起来像是断章取义的，因为按照目前的写法，没有任何其他细节，这是一个不正确的陈述。

    您可以将多个 DML 语句 ( INSERT、UPDATE、DELETE) 应用于同一事务下的多个数据源。在 SQL Server 中，此类事务只需围绕您想要在同一事务中参与的所有 DML 语句显式定义即可。例如：

    BEGIN TRANSACTION

UPDATE TableA
SET Column1 = 0;

INSERT INTO TableB (Column2)
VALUES (123);

ROLLBACK;

    在上面的示例中，两个不同的语句影响两个不同的表，并且都在事务结束时回滚。

    如果没有显式指定它们周围的事务，则每个语句都会作为其自己的事务以原子方式单独执行，而不是每个语句。

    我无法创建一个插入在 Northwind 数据库上执行的脚本，而另一个插入不在 TestDB 上执行，但回滚无法删除 Northwind 数据库上插入的行。

    抱歉，我不明白你在这里想说什么。您能否提供dbfiddle.uk的示例？

    问题1）事务是在实例下管理还是在数据库下管理？我的意思是，给定实例中存在的多个数据库可以参与一个事务吗？

    是的，多个数据库可以参与同一个事务。事务可以定义为您想要的粒度，也可以定义为宏观，并且可以包含任意数量的数据源，无论是否位于同一数据库中。

    问题 2) 如果 TestDB 存在于 SQL Server 的另一个实例（例如 MyServer2）中，是否可以将其包含在当前实例（例如 MyServer1）的一个事务中。

    是的，这就是所谓的分布式事务。

    您能给我看一下示例代码吗？

    当然，这里是上述分布式事务文档中示例的一些代码：

    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;

    在此示例中，删除发生在表的本地副本上JobCandidate，也发生在另一个 SQL Server 实例的远程副本上，所有这些都在同一事务中。如果发生错误并发生回滚，则这两项更改也将被撤消。

    请注意，为了使用分布式事务，需要在远程服务器上启用并正确配置Microsoft 分布式事务协调器 (MS DTC) 。

    • 1

  2. 如果您想做两项更改，并且第二项不应触及第一项 - 只需将它们分开即可。

    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

    事务在“会话”下管理。您连接到服务器，直到您断开连接 - 这就是一个会话。对该服务器上任意数量的数据库的任何更改都是一个事务。从技术上讲：您进行更改，它被写入日志的脏页中，您提交 - 应用于该服务器管理的所有存储设备的所有更改，您回滚 - 您在此事务中写入脏页的所有更改都将被放弃。事务中包含多少个数据库并不重要。

    如果您的一个表实际上是到另一台服务器的链接表，那么此类表的每个单独更新实际上都是该辅助服务器上且在事务机制之外的新会话。

    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

    是的，如果您有两个“数据源”，它们不能位于同一事务中。“数据源”通常定义从客户端到服务器的连接。如果您有两个“数据源”，则意味着两个连接，因此意味着两个独立的事务。

    • -2

相关问题