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


Do this

Start a transaction


Commit a transaction


Roll back a transaction


Trap for errors in a transaction

Use a TRY/CATCH statement. Use the RAISERROR statement to return an error message.

Turn on implicittransactions


Check the level of nested transactions

Use the @@TRANCOUNT function.

Monitor locks

Query the sys.dm_tran_locks view.

Set the 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.

Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: