Table D3 Transaction Isolation Levels




The pending changes from one transaction cannot be overwritten by another, but they are visible and the rows aren't locked otherwise.


Dirty reads are possible, meaning that changes made by other transactions that haven't been committed yet are visible.


Changes made by other transactions aren't visible, but locks aren't held, so changes committed by one transaction can result in nonrepeatable reads from others or the removal of records that were previously retrieved.


Locks are placed on all data used in a query, so no other transaction can modify a row until a transaction that reads the row commits.


Provides the maximum protection possible. All data that meets the criteria for a query is locked as long as the transaction for that query is uncommitted. This means that no other transaction can add, delete, or modify records that would affect the outcome of the original query if it was repeated from scratch after those changes were made.


Makes a copy of the data that a transaction will read and continues to use that copy to provide repeatable reads, even though the actual persisted data isn't locked for other updates. This level is only available for SQL Server 2005.

Once the transaction is created, you need to associate it with any commands you want to enlist within the transaction scope. After that, you can execute the commands. If everything turns out the way you want, you should call Commit on the transaction object to make all the changes done within the transaction permanent. If anything goes wrong or an exception gets thrown, you should Rollback the transaction to prevent any changes made by the commands enlisted within the command from becoming permanent. The best way to make sure this pattern is followed is with a try-catch block as shown in Listing D.9.

Explicitly roll back transactions

If you close a connection on which you have begun a transaction and you haven't called Commit on that transaction yet, the transaction will be automatically rolled back. However, I recommend that you always make it explicit by calling Rollback yourself (typically in an exception-handlingcatch block) to make it clear when and where aRollback is occurring.

Closing Connections

If you explicitly open a connection in your code, always wrap the code that opens the connection and executes the queries in a try block. Follow this with afinally block, and close the connection in the finally block. This way, whether the method exits normally or because of an uncaught exception, the connection will be closed and freed up for other clients to use.

If you don't intend to use the connection object again, you can also enclose the use of the connection object in a using block, which will call Dispose on the connection when it leaves the block. The implementation ofDispose on a connection object calls Close for you, and theusing statement automatically generates the try-finally block for you. This ensures that it is done safely in the face of exceptions.

In addition to the try-catch, you can see that because the connection was explicitly opened inListing D.9. I made sure to close it in the finally block, so that no matter what, the connection gets closed before I leave the method. A couple of other new things you see here but that haven't been discussed yet are the ExecuteScalarand ExecuteNonQuery methods on the SqICommand objects. ExecuteScalaris a convenience method for queries that are expected to return a single row with a single column as their result set. The method will extract the value in that column for you and pass it back as a return value from the method. It is returned as an object reference, so you will have to cast to the expected type, as shown in Listing D.9. The ExecuteNonQuery method is for executing commands for which you don't expect any returned rows, such as update, insert, and delete queries.

0 0

Post a comment