SQL Azure Connection and Transaction

91 views Asked by At

I am using SQL Azure for a Saas based asp.net application. I have avoided transactions as much as possible to prevent deadlocks for end-users. This is due to my lack of knowledge in DB Design, but I thought avoiding transaction is atleast one sure way of reducing table and row locks and therefore contention and deadlocks issues in a database being used for SaaS.

So I am not using any form of explicit database transaction in .net. Now suppose I open a SQLConnection in .net and run a insert statement that inserts information in Table1, then without closing the connection I also insert in Table2 and Table3. Will all of these inserts be part of an implicit transaction.

1

There are 1 answers

0
usr On BEST ANSWER

No, multi-statement transactions are opened explicitly. I believe there are some esoteric settings you can use to open one implicitly but I consider those to be severe design mistakes and evil.

I don't understand why you want this behavior. Rather, open a transaction explicitly. There is no behavioral difference caused by the way transactions are opened.

but I thought avoiding transaction is atleast one sure way of reducing table and row locks and therefore contention and deadlocks issues in a database being used for SaaS.

That is heuristically true. I rather recommend that you make yourself familiar with the basics of locking and so on. You can gain a lot of development productivity by using transactions right. Basic transaction use is actually very simple. Turn on snapshot isolation. Probability is about 99% that you will want this.