在 SQL Server 2005 中模拟自治事务

发布于 2024-10-12 19:19:16 字数 642 浏览 3 评论 0原文

即使我的事务回滚，我也需要将一些日志数据保留在不同的表中。

我已经知道在 SQL Server 中不可能做这样的事情

begin tran t1
insert ...
insert ...
select ...
       begin tran t2
         insert into log
       commit tran t2
rollback tran t1
select * from log -- IS EMPTY ALWAYS

，所以我尝试破解 SQL Server，我疯狂的 CLR 将以 XML 格式将 LOG 所需的数据导出到本地服务器磁盘。 CLR 代码非常简单：

File.WriteAllText(fileName, xmlLog.Value.ToString());

在我将其发布到生产基地之前，我很想听听您对这项技术的看法。

这里有几个问题：

在 SQL Server 2005 中是否有其他更好的方法来完成自治事务
当 SQL Server 执行 CLR 时保持事务未提交怎么可能会不好（SQL 写入的数据量相对较小，大约 50 - 60 个 3 个整数的记录）和 4 个浮筒）

原文

I have needs to keep some of log data in different tables even my transaction is rolled back.

I already learned that in SQL Server it is impossible do something like this

begin tran t1
insert ...
insert ...
select ...
       begin tran t2
         insert into log
       commit tran t2
rollback tran t1
select * from log -- IS EMPTY ALWAYS

So I try hacking SQL Server that I madded CLR which is going to export data need for LOG to local server disk in XML format. CLR Code is simple as it can be:

File.WriteAllText(fileName, xmlLog.Value.ToString());

Before I release this in production bases Ill love to hear your toughs about this technique.

Here are few questions:

Is there other better way to accomplish autonomous transaction in SQL Server 2005
How can be bad holding my transaction uncommitted while SQL Server is executing CLR (amount of data written by SQL is relative small about 50 - 60 records of 3 integers and 4 floats)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吐个泡泡 2024-10-19 19:19:16

我建议使用表变量，因为它不受事务的影响（这是问题下方马丁指出的博客中列出的方法之一）。考虑这样做，这将在 SQL Server 2005 中工作：

DECLARE @TempLog TABLE (FieldList...)

BEGIN TRY

    BEGIN TRAN

    INSERT...

    INSERT INTO @TempLog (FieldList...) VALUES (@Variables or StaticValues...)

    INSERT...

    INSERT INTO @TempLog (FieldList...) VALUES (@Variables or StaticValues...)

    COMMIT TRAN

END TRY
BEGIN CATCH

    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK TRAN
    END

    /* Maybe add a Log message to note that we ran into an error */
    INSERT INTO @TempLog (FieldList...) VALUES (@Variables or StaticValues...)

END CATCH

INSERT INTO RealLogTable (FieldList...)
    SELECT FieldsList
    FROM @TempLog

请注意，虽然我们利用表变量不是事务的一部分这一事实，但这确实会产生一种潜在的情况，即此代码执行 COMMIT 但出现错误（或服务器在 INSERT INTO RealLogTable 之前，您将丢失已插入数据的日志记录。此时，由于有数据但没有插入 RealLogTable 的记录，因此会出现断开连接。但这只是能够绕过交易的明显权衡。

I would suggest using a Table Variable as it is not affected by the Transaction (this is one of the methods listed in the blog noted by Martin below the question). Consider doing this, which will work in SQL Server 2005:

DECLARE @TempLog TABLE (FieldList...)

BEGIN TRY

    BEGIN TRAN

    INSERT...

    INSERT INTO @TempLog (FieldList...) VALUES (@Variables or StaticValues...)

    INSERT...

    INSERT INTO @TempLog (FieldList...) VALUES (@Variables or StaticValues...)

    COMMIT TRAN

END TRY
BEGIN CATCH

    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK TRAN
    END

    /* Maybe add a Log message to note that we ran into an error */
    INSERT INTO @TempLog (FieldList...) VALUES (@Variables or StaticValues...)

END CATCH

INSERT INTO RealLogTable (FieldList...)
    SELECT FieldsList
    FROM @TempLog

Please note that while we are making use of the fact that Table Variables are not part of the transaction, that does create a potential situation where this code does a COMMIT but errors (or server crashes) before the INSERT INTO RealLogTable and you will have lost the logging for the data that did make it in. At this point there would be a disconnect as there is data but no record of it being inserted as far as RealLogTable is concerned. But this is just the obvious trade-off for being able to bypass the Transaction.

回复收藏 0 原文

~没有更多了~