Conclusion

In this chapter, you have learned that all data access in SQL Server is done through transactions. Every transaction conforms to the ACID rules and can be defined explicitly to correspond to business transactions. Every explicit transaction should also implement an error handler to define under which circumstances SQL Server should roll back the transaction.

You have seen how SQL Server can isolate transactions from each other through locking and row versioning and how it is possible to implement the best level of isolation for your transaction through defining isolation levels. Also, you have learned how important it is to choose the right isolation level to minimize blocking and deadlock problems in databases.

Chapter 10 Quick Reference

To	Do this
Start a transaction	BEGIN TRAN
Commit a transaction	COMMIT TRAN
Roll back a transaction	ROLLBACK TRAN
Trap for errors in a transaction	Use a TRY/CATCH statement. Use the RAISERROR statement to return an error message.
Turn on implicittransactions	SET IMPLICIT_TRANSACTIONS ON
Check the level of nested transactions	Use the @@TRANCOUNT function.
Monitor locks	Query the sys.dm_tran_locks view.
Set the isolation level	SET TRANSACTION ISOLATION LEVEL <isolation level>
Monitor blocking	Query the sys.dm_os_waiting_tasks view.
Minimize blocking	Keep the transaction as short as possible. Never request user input during a transaction. Consider using row versioning when reading data. Access the least amount of data possible while in a transaction. Use lower isolating transaction levels whenever possible.
Prevent and handle deadlocks	Follow the rules to minimize blocking. Always access objects in the same order within your transactions (an access order list should be defined for all tables in a database). Check for error 1205 within your error handler and resubmit the transaction when the error occurs. Add a procedure to your error handler to log the error details.