Implementing Transactions :: SQL Server 2012

You have just been assigned to a new project as the database developer on the team. The application will use stored procedures for performing some of the financial operations. You have decided to use T-SQL transactions. Answer the following questions about what you would recommend in the specified situations.
  1. In some transactions that update tables, after a session reads a particular value from another table, it is critical that the other table’s value not change until the transaction is finished. What is the appropriate transaction isolation level to accomplish this?

  2. You will use T-SQL scripts to deploy new objects such as tables, views, or T-SQL code to the database. If any kind of T-SQL error occurs, you want the entire deployment script to quit. How can you accomplish this without adding complex error handling?

  3. One of the stored procedures will transfer money from one account to another. During that transfer period, neither account can have any data changed, inserted, or deleted for the range of values read by the transaction. What is the appropriate transaction isolation level to accomplish this?
Answers
  1. To ensure that whenever data is read in a transaction the data will not change until the end of the transaction, you can use the REPEATABLE READ transaction isolation level. This is the least restrictive level that will satisfy the requirements.

  2. When you deploy new database objects by using T-SQL scripts, you can wrap the batches in a single transaction and use SET XACT_ABORT ON right after the BEGIN TRANSACTION statement. Then if any T-SQL error occurs, the entire transaction will abort and you will not have to add complex error handling.

  3. To ensure that, for the range of values read by the transaction, none of the rows being read can be changed and that no new rows may be inserted and none deleted, you can use the SERIALIZABLE isolation level. This is the most restrictive isolation level and can lead to a lot of blocking, so you need to ensure that the transactions complete as quickly as possible.