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.
- 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?
- 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?
- 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
- 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.
- 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.
- 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.