<p>In your applications, what's a "long time" to keep a transaction open before committing or rolling back?  Minutes?  Seconds?  Hours?</p>

<p>and on which database?</p>

transactions: minutes.
Cursors: 0seconds maximum, if you use a cursor we fire you.
This is not ridiculous when you consider we are in a high availability web environment, that has to run sql server, and we don't even allow stored procs because of inability to accurately version and maintain them. If we were using oracle maybe.