SQL Server - 事务中一条语句的结果立即对下一条语句可见？
这
use test;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
EXEC sp_RENAME 'table1.asd' , 'ads', 'COLUMN';
INSERT INTO table1 (ads) VALUES (12);
COMMIT
是一个简单的例子，演示了我想做的事情。
我想以某种方式更改表并在一个事务中执行插入/删除（或对表的其他修改）。
问题是 sp_RENAME 的结果永远不会立即对 INSERT 语句可见。我尝试过不同的事务隔离级别 - 它总是相同的（因此事务永远不会提交）。
通常我只会在单独的批次中使用 GO 语句，但我需要在一个批次中这样做，因为......
我真正的任务是编写一个脚本，将身份和 FK 添加到表中（这需要使用以下命令创建另一个表）新模式，从旧模式执行身份插入，重命名表并应用约束）。我需要谨慎行事 - 如果过程的任何部分失败，我必须回滚整个事务。这就是为什么我想做这样的事情：
BEGIN TRAN
--some statement
IF (@@ERROR <> 0) GOTO ERR_HANDLER
-- some other statement
IF (@@ERROR <> 0) GOTO ERR_HANDLER
COMMIT TRAN
RETURN 0
ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1
由于标签只能在批处理内工作，所以我不能使用 GO 语句。
那么我怎样才能：
- 使语句（即ALTER TABLE、sp_RENAME）立即生效？
或者
- 以其他方式编写整个解决方案，以便在生产数据库中安全运行？
This:
use test;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
EXEC sp_RENAME 'table1.asd' , 'ads', 'COLUMN';
INSERT INTO table1 (ads) VALUES (12);
COMMIT
is a simple example that demonstrates what I would like to do.
I want to alter the table in some way and perform inserts/deletes in one transaction (or other modifications to the table).
The problem is that the results from sp_RENAME are never immediately visible to the INSERT statement. I've played with different transaction isolation levels - it's always the same (therefore the transaction never commits).
Normally I would just use GO statements for this to be in separate batches, but I need that in one batch, because...
My real task is to write a script that adds identity and FK to a table (this requires creating another table with the new schema, performing identity inserts from the old one, renaming the table and applying constraints). I need to play it safe - if any part of the procedure fails I have to rollback the whole transaction. This is why I wanted to do something like this:
BEGIN TRAN
--some statement
IF (@@ERROR <> 0) GOTO ERR_HANDLER
-- some other statement
IF (@@ERROR <> 0) GOTO ERR_HANDLER
COMMIT TRAN
RETURN 0
ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1
Since labels work only inside a batch I cannot use GO statements.
So how can I:
- make statements(ie. ALTER TABLE, sp_RENAME) have an immediate effect ?
or
- write the whole solution some other way so that it is safe to run in production DB ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
问题在于，当遇到对重命名列的引用时，批处理的解析会失败，因此整个批处理永远不会被执行 - 并不是说事务的效果不可见。
您可以将引用列的新名称的语句放在
EXEC('')块中，以将编译推迟到重命名列之后。The issue is that the parsing of the batch fails when it encounters the reference to the renamed column so the entire batch never gets executed - not that the effects of the transaction are not visible.
You can put your statements referencing the new name of the column in an
EXEC('')block to defer compilation until after the column is renamed.