I'm just wondering what happens if you begin a transaction in a DB and forgot to commit or rollback it. Will the server be down? Lets say you left it for 3 days.
There are also users who are using it assuming that the other users did not know that there is an unclosed transaction (let's just assume that the users are just inserting data on the database). What are the consequences of this action?
Best Answer
Having an open transaction by itself will have almost no consequence. A simple
will, at worst, hold a few bytes of status values. No big deal.
Most programs will do actual work within the transaction and this is another matter. The point of a transaction is so you can be sure that several facts within the database are true simultaneously, despite there being other users writing to the same database concurrently.
Take the cannonical example of transferring money between bank accounts. The system must ensure that the source account exists, has sufficient funds, the destination account exists, and that both debit and credit happen or neither happens. It must guarantee this while other transactions happen, perhaps even between these two accounts. The system ensures this by taking locks on the tables concerned. What locks are taken, and how much of other peoples' work you see, is controlled by the transaction isolation level.
So if you do a lot of work there is a good chance other transactions will be queued waiting for the objects on which you hold locks. This will reduce the system's overall throughput. Eventually they will hit timeout limits and fail, which is a problem for overall system behaviour. If you use an optimistic isolation level your transaction may fail when you try a commit because of other's work.
Holding locks takes system resources. This is memory which the system cannot use to process other requests, reducing throughput.
If a lot of work has been performed the system may choose to perform lock escalation. Instead of locking individual rows the entire table will be locked. Then more concurrent users will be affected, system throughput will drop further and the application impact will be greater.
Data changes are written to the log file, as are the locks which protect them. These cannot be cleared from the log until the transaction commits. Hence very long transaction may cause log file bloat with its associated problems.
If the current work uses tempdb, which is likely for large workloads, the resources there may be tied up until the end of the transaction. In extreme cases this can cause other tasks to fail because there is no longer enough room for them. I have had cases where a poorly coded UPDATE filled tempdb so there was insufficient disk left for a report's SORT and the report failed.
If you choose to ROLLBACK the transaction, or the system fails and recovers, the time taken for the system to become available again will depend on how much work was performed. Simply having a transaction open will not affect the recovery time, it is how much work was performed. If the transaction was open but idle for an hour recovery will be almost instantaneous. If it was writing constantly for that hour the rule of thumb is that recovery time also will be about an hour.
As you can see long transaction can be problematic. For OLTP systems best practice is to have one database transaction per business transaction. For batch work process input in blocks, with frequent commits, and restart logic coded. Typically several thousand records can be processed inside a single DB transaction, but this should be tested for concurrency and resoruce consumption.
Do not be tempted to go to the other extreme and avoid transactions and locks entirely. If you need to maintain consistency within your data (and why else would you be using a database?) isolation levels and transactions serve a very important purpose. Learn about your options and decide what balance of concurrency and correctness you are prepared to live with for each part of your application.