3

I have to implement Transactions in my code. I have the following options:-

  1. TransactionScope from C# code:- I will use this case if i have some logical code in transaction along with my Database calls.Transaction rolls back and locks are released if command timeouts are there.
  2. Having Explicit transactions in SP:- In case of command timeout, Transactions remain opens and locks are not released.

Has any one of you faced similar issues. Please suggest. Also tell me setting XACT_ABORT_ON will help in the second case.

Rohit Raghuvansi
  • 2,824
  • 8
  • 46
  • 74

2 Answers2

-1

If your stored procedure is not an "interface procedure" (called by third-party outside your code) I recommend using TransactionScope or creating SqlTransaction in code for your procedure call. Handling transactions in code is much easier. You may read this article: http://www.code-magazine.com/Article.aspx?quickid=0305111 - as you can see if you start using transactions in stored procedures things may get unnecesarily complicated.

empi
  • 15,755
  • 8
  • 62
  • 78
  • TXNs in stored procs don't use MSDTC: MSDTC add overhead. You can use SET XACT_ABORT etc to make things behave as expected – gbn Jun 23 '11 at 14:29
-1
  • I always use SET XACT_ABORT ON
  • Server side transactions don't require MSDTC and have less overhead
  • TRY/CATCH makes the stored procs more reliable

See my answer here for a safe stored proc template which will deal with nested transactions too: Nested stored procedures containing TRY CATCH ROLLBACK pattern?

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • TransactionScope in sql server 2008 does not cause escalation to MSDTC. – empi Jun 23 '11 at 14:57
  • 1
    @empi: "usually" would be better rather then "do not": http://stackoverflow.com/q/1690892/27535 Either way, use the database engine for what it's designed for. – gbn Jun 23 '11 at 15:20
  • @gbn: Still, you can use SqlTransaction if you see problems with TransactionScope. However, I think it is easier to handle this in c# code. Let's take your example - you have to include exactly the same try-catch pattern in every procedure and I don't think it's a good idea. – empi Jun 23 '11 at 15:54
  • @empi: And then what if you have more than one client? Your database will outlive the client application*s* too. Implement this once, in the database... – gbn Jun 23 '11 at 15:57
  • @gbn: I don't want to treat database as a common interface to many clients. I'm working with a huge app that basically does this and it gives me only headaches. Working with decent ORM and implementing logic in c# not in sql is way to go for me. I think that using database only as a persistence layer simplifies most use cases. – empi Jun 23 '11 at 16:01
  • @empi: I'd break the legs of any client code monkey who thought his ORM would scale to my 40k INSERTs per second: your huge is different to my huge (scale or focus). I suppose you can do DRI better in c# too...? – gbn Jun 23 '11 at 16:08
  • @gbn: I bet that most applications are business applications that perform mostly simple CRUD and what matters most is code maintainability. I don't see that many apps that need to insert 40k records every second. I'm not saying that you should do e.g. BI in Hibernate, but I'm saying that most of business requirements may be solved better using good ORM than writing SPs. – empi Jun 23 '11 at 16:21