This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

技多不压身，工到自然成：安省技工证书特训班，点击咨询报名！

Ad by

技多不压身，工到自然成：安省技工证书特训班，点击咨询报名！

Sounds reasonable! Please check this out.....

whereismyid(到处找ID)

IN one COM+ , there are lot's calls to stored procs.(all those stored procs are simple and similiar like above example: just one Insert statement). The order is following:
1. call sp1 (insert into table1. Format is same as my pre-post. Weird Tran for one sp)
2. call sp2 (insert into table2..)
3. call sp3..(insert into table3
4. Invoke another COM+ to contact outside third party ASP page via winHttp object
5. call sp4 (insert into table4)
...

Weird random issue is that there was only an added record in table2 and no in other tables.
This issue is very rarely happen. But it happened randomly.

Thought?

(#3865856@0)
Last Updated: 2007-8-13
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / 专业技术讨论 / T-SQL问题: 在STORED PROC 中就一简单的TRANSACTION INSERT .... -whereismyid(到处找ID); 2007-8-9 {246} (#3860526@0)
BEGIN TRANSACTION
INSERT [Order]
(MemberID,StaffName)
VALUES (@MemberID,@staffName)

IF (@@ERROR != 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
COMMIT TRANSACTION
RETURN SCOPE_IDENTITY()

什么情况下会ROLLBACK? (会有什么样的错误呢?)

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. -deep_blue(BLUE); 2007-8-13 {129} (#3865358@0)
Actually, one sql command is not necessary to use transaction. Even the command failed, you have nothing (and not need) rollback.

Sounds reasonable! Please check this out..... -whereismyid(到处找ID); 2007-8-13 {620} (#3865856@0)
IN one COM+ , there are lot's calls to stored procs.(all those stored procs are simple and similiar like above example: just one Insert statement). The order is following:
1. call sp1 (insert into table1. Format is same as my pre-post. Weird Tran for one sp)
2. call sp2 (insert into table2..)
3. call sp3..(insert into table3
4. Invoke another COM+ to contact outside third party ASP page via winHttp object
5. call sp4 (insert into table4)
...

Weird random issue is that there was only an added record in table2 and no in other tables.
This issue is very rarely happen. But it happened randomly.

Thought?

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)
As liquid and sunday8 suggested, it’s not good idea to put transaction in SPs. Using auto-commit/rollback functionality of COM+ is a better choice. Or as far as I know, you can also use explicitly commit/rollback functionality in COM+.

BTW, in your process, one thing you need to be very careful. In step 4, it calls outside ASP page over internet (or maybe intranet). If you put whole staff in one transaction, it might cause dead lock when in networking trouble. So, it the step is independent, put it in the beginning and start transaction after it.

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)

无非两种情况： -newkid(newkid); 2007-8-13 {305} (#3866801@0)
1.程序没执行到 2.执行了但出错
1.仔细研究你的处理逻辑, 哪种流程导致没有执行。如果三个SP写在一块就比较容易查错，毕竟几个INSERT离得很近
2.出错原因有很多：主键、外键出错、非空列插入了空、值溢出、后台约束校验不通过……
如果例外被捕获了，但没有正确处理，这个错误就被掩盖起来了。仔细查看DB EXCEPTION的处理。

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 procedure -sunday8(sunday8); 2007-8-13 {271} (#3865614@0)
if 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.

@Las Vegas

Sounds reasonable! Please check this out.....

Replies, comments and Discussions:

More Topics