Many developers find this topic to be mysterious or confusing. Let’s try to shed some light on this subject.
What is a transaction and how is it managed in the database?
Why is my connection blocking others (my T-SQL statements completed hours ago)?
Simply put, from Microsoft’s .NET website:
“A transaction consists of a single command or a group of commands that execute as a package. Transactions allow you to combine multiple operations into a single unit of work. If a failure occurs at one point in the transaction, all of the updates can be rolled back to their pre-transaction state.
A transaction must conform to the ACID properties—atomicity, consistency, isolation, and durability—in order to guarantee data consistency. Most relational database systems, such as Microsoft SQL Server, support transactions by providing locking, logging, and transaction management facilities whenever a client application performs an update, insert, or delete operation.”
Transaction management is one of the most basic functions of the DBMS; to ensure the ACID properties are maintained for each and every access of the database. The DBMS uses transactions to make sure the database access requested by each connection conforms to the ACID properties mentioned above. This ensures the integrity of the data for everyone. Transactions protect your data reads and changes from everyone else (via record locking), gives you a vehicle to discard data updates if there is a problem (via rollback), and even maintains the reliability of your data in the event of a database failure (via the transaction log). Every DBMS has transactions and also has its own unique way of managing them. This posting will talk about the concepts of a transaction in a SQL Server environment.
Isolation level: Your isolation level defines how your transaction(s) interact with transactions from other user/connections. The default, read committed, is good for most database access so most of the time there is no need to change it. There are whole articles written about this so I won’t dive into this tangent, except to say that your choice of isolation level effects the type of locking that the database will use to manage your transaction. There is a link below to one short article if you want to know more about isolation levels.
There are two concepts that SQL Server uses to manage the transactions in each connection. First there is a concept of the commit mode in SQL Server. SQL Server is set to auto-commit by default. This means that SQL Server will automatically commit for each T-SQL statement executed. As a developer you don’t have to code or do anything. If you have 5 statements in a row, SQL will start a transaction and commit that transaction when each of the 5 statements executes (each is an independent transaction as well). This can be confusing to developers that have experience with other databases (like Oracle) since this mode doesn’t exist on many other platforms. If a statement encounters an error only that statement is rolled back. Note that you can turn off auto-commit by using a SET statement.
Next is the concept of the transaction type in SQL Server. There are 2 types of transactions, implicit and explicit. Again, the implicit transaction type is implied in the default auto-commit mode. However, you can also use a SET statement to set the implicit transaction type on and manage your transaction much like Oracle does. If the implicit transaction type is on (but auto-commit is off) then SQL Server automatically starts a transaction when the first T-SQL statement is executed but relies on the T-SQL code to close the transaction (with a rollback or commit). You are responsible for the closure of the transaction.
The explicit transaction is started and ended by the T-SQL code. By issuing a BEGIN TRANSACTION statement you open up an explicit transaction (and turn off auto-commit). Once open you must issue a ROLLBACK or COMMIT to complete and close the transaction. If you do not close your transaction the DBMS will hold all locks on all of your changes until you do close the transaction. That includes all index changes, system tables, etc. Your statements will complete and give you back any/all messages and there is no visual indication that a transaction is left open; SQL Server has no way of knowing if you have completed all of the work you intend for that transaction. If you leave the connection open SQL will hold the locks forever (blocking all other connections, including other connections from your own computer!). In T-SQL code you can check the transaction count; search @@TRAN_COUNT for examples to know if you need to close a transaction (helpful especially in TRY-CATCH blocks and when using nested transactions).
As far as performance is concerned there are pros and cons to using auto-commit vs. explicit transactions. For OLTP-type processing I don’t think you will see a difference. For larger batch-type processes, however, you can see large performance gains using explicit transactions due to differences in log writes/flushes. My best advice here is to do what makes the most sense for your situation. SQL Server makes it easy with auto-commit; use it when it makes sense. Use explicit transactions whenever the logic modifies multiple tables and/or many rows, both for ease in catching errors and for the performance of the database engine. Consideration also needs to be given to the number of transaction log records that you are creating in each transaction and the size of your transaction log.
Here are some links that provide more detail if you want to know more: