<p>How much do you rely on database transactions?  </p>

<p>Do you prefer small or large transaction scopes ?  </p>

<p>Do you prefer client side transaction handling (e.g. TransactionScope in .NET) over server 
side transactions or vice-versa?    </p>

<p>What about nested transactions?  </p>

<p>Do you have some tips&amp;tricks related to transactions ?</p>

<p>Any gotchas you encountered working with transaction ?</p>

<p>All sort of answers are welcome.</p>

[{'answer_id': 39594, 'author': 'DevelopingChris', 'author_id': 1220, 'author_profile': 'https://Stackoverflow.com/users/1220', 'pm_score': 2, 'selected': False, 'text': '<p>I use transactions on every write operation to the database.<br>\nSo there are quite a few small "transactions" wrapped in a larger transaction and basically there is an outstanding transaction count in the nesting code. If there are any outstanding children when you end the parent, its all rolled back.  </p>\n\n<p>I prefer client-side transaction handling where available. If you are relegated to doing sps or other server side logical units of work, server side transactions are fine.</p>\n'}, {'answer_id': 39605, 'author': 'Simon Johnson', 'author_id': 854, 'author_profile': 'https://Stackoverflow.com/users/854', 'pm_score': 4, 'selected': False, 'text': "<p>I always wrap a transaction in a using statement.</p>\n\n<pre><code>using(IDbTransaction transaction )\n{\n// logic goes here.\n   transaction.Commit();\n}\n</code></pre>\n\n<p>Once the transaction moves out of scope, it is disposed. If the transaction is still active, it is rolled back. This behaviour fail-safes you from accidentally locking out the database. Even if an unhandled exception is thrown, the transaction will still rollback. </p>\n\n<p>In my code I actually omit explicit rollbacks and rely on the using statement to do the work for me. I only explicitly perform commits.  </p>\n\n<p>I've found this pattern has drastically reduced record locking issues.</p>\n"}, {'answer_id': 39613, 'author': 'Sara Chipps', 'author_id': 4140, 'author_profile': 'https://Stackoverflow.com/users/4140', 'pm_score': 3, 'selected': False, 'text': '<p>Personally, developing a website that is high traffic perfomance based, I stay away from database transactions whenever possible. Obviously they are neccessary, so I use an ORM, and page level object variables to minimize the number of server side calls I have to make. </p>\n\n<p>Nested transactions are an awesome way to minimize your calls, I steer in that direction whenever I can as long as they are quick queries that wont cause locking. NHibernate has been a savior in these cases. </p>\n'}, {'answer_id': 39859, 'author': 'Marcio Aguiar', 'author_id': 4213, 'author_profile': 'https://Stackoverflow.com/users/4213', 'pm_score': 2, 'selected': False, 'text': "<p>Wow! Lots of questions! </p>\n\n<p>Until a year ago I relied 100% on transactions. Now its only 98%. In special cases of high traffic websites (like Sara mentioned) and also high partitioned data, enforcing the need of distributed transactions, a transactionless architecture can be adopted. Now you'll have to code referential integrity in the application.</p>\n\n<p>Also, I like to manage transactions declaratively using annotations (I'm a Java guy) and aspects. That's a very clean way to determine transaction boundaries and it includes transaction propagation functionality.</p>\n"}, {'answer_id': 45958, 'author': 'oglester', 'author_id': 2017, 'author_profile': 'https://Stackoverflow.com/users/2017', 'pm_score': 2, 'selected': False, 'text': '<p>Just as an FYI... Nested transactions can be dangerous.  It simply increases the chances of getting deadlock.  So, though it is good and necessary, the way it is implemented is important in higher volume situation.</p>\n'}, {'answer_id': 861444, 'author': 'yanky', 'author_id': 288477, 'author_profile': 'https://Stackoverflow.com/users/288477', 'pm_score': 0, 'selected': False, 'text': '<p>As Sara Chipps said, transaction is overkill for high traffic applications. So we should avoid it as much as possible. In other words, we use a <a href="http://queue.acm.org/detail.cfm?id=1394128" rel="nofollow noreferrer">BASE architecture</a> rather than ACID. Ebay is a typical case. Distributed transaction is not used at all in Ebay architecture. But for <a href="http://queue.acm.org/detail.cfm?id=1466448" rel="nofollow noreferrer">eventual consistency</a>, you have to do some sort of trick on your own.</p>\n'}, {'answer_id': 1578860, 'author': 'gbn', 'author_id': 27535, 'author_profile': 'https://Stackoverflow.com/users/27535', 'pm_score': 2, 'selected': False, 'text': '<p>Server side transactions, 35,000 transactions per second, SQL Server: <a href="http://sqlblog.com/blogs/paul_nielsen/archive/2007/12/12/10-lessons-from-35k-tps.aspx" rel="nofollow noreferrer">10 lessons from 35K tps</a></p>\n\n<p>We only use server side transactions:</p>\n\n<ul>\n<li>can start later and finish sooner</li>\n<li>not distributed</li>\n<li>can do work before and after</li>\n<li>SET XACT_ABORT ON means immediate rollback on error</li>\n<li>client/OS/driver agnostic</li>\n</ul>\n\n<p>Other:</p>\n\n<ul>\n<li>we nest calls but use @@TRANCOUNT to detect already started TXNs</li>\n<li>each DB call is always atomic</li>\n</ul>\n\n<p>We deal with millions of INSERT rows per day (some batched via staging tables), full OLTP, no problems. Not 35k tps though.</p>\n'}]
['https://Stackoverflow.com/questions/39583', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1196/']