×

Loading...
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

@Chicago

My suggestion

deep_blue(BLUE)
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.
(#3866243@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.

More Topics

  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
  • 扔硬币的实验研究
    • 枫下论坛主坛 /  工作学习 /  专业技术讨论