×

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

My suggestion

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.
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 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? (会有什么样的错误呢?)
    • PK/FK violation
      • 大多数情况下, COM+ 调这个SP 都是运行正确, COMMIT!. 会有什么情况ROLLBACK吗?
        • If you use COM+, in most case, you should not use transaction control explicitly in SP, transactions are supposed to be handled by MSDTC.
          • 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.
            • Sounds reasonable! Please check this out.....
              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.
                • 先不考虑BUSINESS LOGIC. HOW ABOUT REMOVING ALL TRAN STUFF FROM ALL SPs? THIS WILL SOLVE RANDOM ISSUE I MENTIONED ABOVE?
              • My suggestion
                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.
              • sp1,sp2,sp3应该合为一个,否则就没必要用sp. 这样分开来看似很灵活但是出了问题很难查错。客户端不应该自动commit,应该仅在事务结束才手动commit.在调用外部链接之前应该把事务结束掉才不会占着数据库的锁。
                • YOU ARE RIGHT. BUT I WOULD LIKE TO HEAR YOUR THOUGHT ABOUT THIS ISSUE. WHY DID THIS ISSUE HAPPEN? THX
                  • 无非两种情况:
                    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
    • I wouldn't put transactions in a stored procedure
      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.