在对存储过程进行单元测试时回滚嵌套事务
我正在尝试为一些 SQL Server 存储过程和函数编写一些集成测试。我想要一个数据库，其中包含一组已知的测试数据，然后将每个测试包装在一个事务中，完成后回滚，以便测试实际上是独立的。
存储过程/函数可以执行任何操作，从相当简单的联接查询，到具有多层联接的复杂过滤，再到将数据插入到多个表中。
有几个存储过程实际上使用事务 - 因此这些存储过程更难测试。我将展示执行的整体代码的示例，但请记住，这通常位于两个不同的位置（测试设置/拆卸和实际的存储过程）。对于这个示例，我还使用了一个非常简单的临时表：
CREATE TABLE #test (
val nvarchar(500)
)
示例：
-- for this example, just ensuring that the table is empty
delete from #test
go
-- begin of test setup code --
begin transaction
go
-- end of test setup code --
-- begin of code under test --
insert into #test values('aaaa')
begin transaction
go
insert into #test values('bbbbb')
rollback transaction
go
insert into #test values('ccccc')
-- Example select #1:
select * from #test
-- end of code under test --
-- begin of test teardown --
rollback transaction
go
-- end of test teardown
-- checking that #temp is still empty, like it was before test
-- Example select #2:
select * from #test
这里的问题是，在“Example select #1”中，我希望“aaaa”和“cccc”出现在表中，但实际上只有“ cccc”位于表中，因为 SQL Server 实际上回滚了所有事务（请参阅 http://abdulaleemkhan.blogspot.com/2006/07/nested-t-sql-transactions.html）。此外，第二次回滚会导致错误，尽管可以通过以下方式避免这种情况：
-- begin of test teardown --
if @@trancount > 0
begin
rollback transaction
end
go
-- end of test teardown
它没有解决真正的问题：在“Example select #2”中，我们仍然在表中得到“cccc”——它不再得到由于没有活动的事务而回滚。
有办法解决这个问题吗？对于此类测试有更好的策略吗？
注意：我不确定代码库在回滚后是否执行过任何操作（插入“cccc”部分）——但如果它执行过，无论是有意还是无意，测试都有可能以奇怪的方式中断因为另一次测试可能会留下意外的数据。
有点类似于 包含 TRY CATCH ROLLBACK 模式的嵌套存储过程？但这里提出的问题没有真正的解决方案。
I am trying to write some integration tests for some SQL server stored procedures and functions. I'd like to have a database that has a known set of test data in it, and then wrap each test in a transaction, rolling it back when complete so that tests are effectively independent.
The stored procedures/functions do anything from fairly simple join queries, to complex filtering with many layers of joins, to insertion of data to multiple tables.
There are a couple stored procedures that actually use transactions - and so these are harder to test. I'll show an example of the overall code that is executed, but keep in mind this would normally be in two different spots (test setup/teardown, and the actual stored procedure). For this sample, I'm also using a very simple temp table:
CREATE TABLE #test (
val nvarchar(500)
)
Example:
-- for this example, just ensuring that the table is empty
delete from #test
go
-- begin of test setup code --
begin transaction
go
-- end of test setup code --
-- begin of code under test --
insert into #test values('aaaa')
begin transaction
go
insert into #test values('bbbbb')
rollback transaction
go
insert into #test values('ccccc')
-- Example select #1:
select * from #test
-- end of code under test --
-- begin of test teardown --
rollback transaction
go
-- end of test teardown
-- checking that #temp is still empty, like it was before test
-- Example select #2:
select * from #test
The problem here is that at "Example select #1", I would expect "aaaa" and "cccc" to be in the table, but actually only "cccc" is in the table, as SQL Server actually rolls back ALL transactions (see http://abdulaleemkhan.blogspot.com/2006/07/nested-t-sql-transactions.html). In addition, the second rollback causes an error, and although this can be avoided with:
-- begin of test teardown --
if @@trancount > 0
begin
rollback transaction
end
go
-- end of test teardown
it doesn't solve the real problem: at "Example select #2", we still get "cccc" in the table -- it no longer gets rolled back because there is no transaction active.
Is there a way around this? Is there a better strategy for this type of testing?
Note: I'm not sure if the codebase ever does do anything after rollback or not (the insert 'cccc' part) -- but if it ever does, either intentionally or accidentally, it would be possible for tests to break in strange ways as unexpected data can be left over from another test.
Somewhat similar to Nested stored procedures containing TRY CATCH ROLLBACK pattern? but there is no real solution to the problem posed here.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
代码中的回滚不会嵌套。他们将所有内容回滚到第一个开始事务。
对于每个 BEGIN TRANSACTION，@@trancount 都会增加 1，但是，任何 ROLLBACK 都会将 @@trancount 设置回零。
如果要回滚事务的一部分，则需要使用 TRANSACTION 保存点。您可以在 BOL 中查找它们，以获取我无法在此处输入的更多信息。
http://msdn.microsoft.com/en-us/library/ms188378。 ASPX
the rollbacks in your code don't nest. they rollback everything back to the first BEGIN TRANSACTION.
for every BEGIN TRANSACTION, @@trancount gets incremented by one, however, any ROLLBACK sets @@trancount back to zero.
if you want to rollback a portion of a transaction, you need to use TRANSACTION savepoints. you can look them up in BOL, for more info than I can enter here.
http://msdn.microsoft.com/en-us/library/ms188378.aspx
不。其一，您不会实际测试功能，因为在现实世界中，过程将提交。其次，这一点更为重要，您将遇到大量错误的失败，并且需要实施解决方法来读取脏数据，因为您实际上没有提交，并且无法进行任何正确的验证。
相反，使用众所周知的集进行数据库备份，然后在测试前快速恢复它。将测试分组到套件中，这些套件都可以在全新的数据库恢复上运行，而不会相互影响，因此可以减少所需的恢复次数。
您还可以使用数据库快照，在套件启动时拍摄快照，然后在每次测试之前从快照还原数据库，请参阅 如何：将数据库恢复为数据库快照 (Transact-SQL)。
或者结合两种方法：套件设置（即单元测试@class方法）从.bak文件恢复数据库并创建快照，然后每个测试从快照恢复数据库。
Don't. For one, you won't actually test the functionality, because in the real world the procedures will commit. Second, and this is far more important, you will get a gazilion false failures and need to implement workarounds to read dirty data because you don't actually commit, and you cannot do any proper verification.
Instead have a database backup with the well know set, then quickly restore it before the test. Group tests into suites that can all run on a fresh database restore without affecting each other, so you reduce the number of restores needed.
You can also use database snapshots, take a snapshot a the suite startup, then restore the database from the snapshot before each test, see How to: Revert a Database to a Database Snapshot (Transact-SQL).
Or combine the two methods: suite setup (ie. unit test @class method) restores the database from .bak file and creates a snapshot, then each test restores the database from the snapshot.
我对这种设置有类似的问题，我的看法是创建一个“SetupTest”脚本和一个“ClearTest”脚本，在测试执行之前和之后运行。除非您在这里谈论大量数据 - 这会使测试执行太慢，否则这应该可以很好地工作并使测试可重复，因为您知道每次运行该测试套件时，您都会得到正确的结果等待执行的数据。
I had similar issues with that kind of setup and my take on that was to create a "SetupTest" script and a "ClearTest" script to run before and after test execution. Unless you're talking about a huge volume of data here - which would make the test execution too slow, this should work well and make the tests repeatable, because you know that everytime you're running that test suite, you will have the correct data awaiting to be executed.