Chapter 6: Designing Transactions and Transaction Isolation

Overview

Transactions are one of the most important aspects when working with a database system. Transactions are used to isolate database users from each other to make each executed query consistent. The use of transactions can both improve the performance of queries as well as degrade performance. It is important to understand how the use of transactions will affect a specific query.

When designing transactions, you will need to make decisions such as:

• When to start and end your transactions

• Which transaction isolation level to use

• Which locking hints you should consider supplying to the query optimizer

All examples in this chapter reference objects in a schema called "Test." You can create this schema simply by executing the statement CREATE SCHEMA TEST; in a database of your choice.

Exam objectives in this chapter:

• Manage concurrency by selecting the appropriate transaction isolation levels.

• Design the locking granularity level.

• Design transaction scopes.

• Design code that uses transactions.