This topic has been archived. It cannot be replied.
-
工作学习 / 专业技术讨论 / T-SQL问题: 在STORED PROC 中就一简单的TRANSACTION INSERT ....BEGIN TRANSACTION
INSERT [Order]
(MemberID,StaffName)
VALUES (@MemberID,@staffName)
IF (@@ERROR != 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
COMMIT TRANSACTION
RETURN SCOPE_IDENTITY()
什么情况下会ROLLBACK? (会有什么样的错误呢?)
-whereismyid(到处找ID);
2007-8-9
{246}
(#3860526@0)
-
PK/FK violation
-carolhu(晒太阳的大花猫);
2007-8-10
(#3860565@0)
-
大多数情况下, COM+ 调这个SP 都是运行正确, COMMIT!. 会有什么情况ROLLBACK吗?
-whereismyid(到处找ID);
2007-8-10
(#3860680@0)
-
If you use COM+, in most case, you should not use transaction control explicitly in SP, transactions are supposed to be handled by MSDTC.
-liquid(avaya);
2007-8-10
(#3860811@0)
-
It might be locked by other process and causes timeout.Actually, one sql command is not necessary to use transaction. Even the command failed, you have nothing (and not need) rollback.
-deep_blue(BLUE);
2007-8-13
{129}
(#3865358@0)
-
Sounds reasonable! Please check this out.....
-whereismyid(到处找ID);
2007-8-13
{620}
(#3865856@0)
-
Definitely you don't have transaction control setup for your COM+ components. If you need the operations in your example to be atom, you need to set transaction required on your first component in the call chain and rollback in case of error.
-liquid(avaya);
2007-8-13
(#3865939@0)
-
先不考虑BUSINESS LOGIC. HOW ABOUT REMOVING ALL TRAN STUFF FROM ALL SPs?
THIS WILL SOLVE RANDOM ISSUE I MENTIONED ABOVE?
-whereismyid(到处找ID);
2007-8-13
(#3866578@0)
-
My suggestion
-deep_blue(BLUE);
2007-8-13
{557}
(#3866243@0)
-
THIS COM+ NO TRAN. BUT TRICKY PART IS THAT ONLY SP2 WORK FINE WHEN THIS ISSUE HAPPEN.
-whereismyid(到处找ID);
2007-8-13
(#3866586@0)
-
sp1,sp2,sp3应该合为一个，否则就没必要用sp. 这样分开来看似很灵活但是出了问题很难查错。客户端不应该自动commit,应该仅在事务结束才手动commit.在调用外部链接之前应该把事务结束掉才不会占着数据库的锁。
-newkid(newkid);
2007-8-13
(#3866350@0)
-
YOU ARE RIGHT. BUT I WOULD LIKE TO HEAR YOUR THOUGHT ABOUT THIS ISSUE. WHY DID THIS ISSUE HAPPEN? THX
-whereismyid(到处找ID);
2007-8-13
(#3866582@0)
-
无非两种情况：1.程序没执行到 2.执行了但出错
1.仔细研究你的处理逻辑, 哪种流程导致没有执行。如果三个SP写在一块就比较容易查错，毕竟几个INSERT离得很近
2.出错原因有很多：主键、外键出错、非空列插入了空、值溢出、后台约束校验不通过……
如果例外被捕获了，但没有正确处理，这个错误就被掩盖起来了。仔细查看DB EXCEPTION的处理。
-newkid(newkid);
2007-8-13
{305}
(#3866801@0)
-
thank you. code was not wroten by me. there was no DB exception handling in COM+. so I just analyzed the code. I will add DB exception handling into eventlog later on
-whereismyid(到处找ID);
2007-8-13
(#3866930@0)
-
I wouldn't put transactions in a stored procedureif a stored procedure calls another store procedure and calls another one and so on. They all have transactions..(commit/rollback), and one of them fails. You will have fun to debug or maintain your app after it is released. :D
I would usually do it outside of the call.
-sunday8(sunday8);
2007-8-13
{271}
(#3865614@0)