Databases Reference
In-Depth Information
YOU TRY IT
Transaction Management
You are a database administrator for a company whose
core busiess is retail sales. Users access the database
through various, relatively small, database applica-
tions. There are also times when you or another admin-
istrator need to run ad hoc statements and queries
from a command line. The database supports hun-
dreds of users, most of whom are working and poten-
tially accessing the database at the same time. The
number of users is expected to increase significantly
over the next year.
use a third-party account application that uses SQL
Server as its data store. It also has a custom Web ap-
plication that enables customers to access account in-
formation, transfer account balances, and make loan
payments online.
A procedure runs each morning and checks pay-
ment due dates for loans. For customers who have
opted to do so, it will deduct the payment amount from
a savings or checking account and make the cus-
tomer's payment automatically. Information about the
payment is written to a tracking table for verification.
1.
Why are transactions necessary when transfer-
ring money between accounts?
1.
Explain the potential impact of the usage
changes to blocked transactions.
2.
You need to ensure maximum transaction isola-
tion during automated loan payments. How can
you do this? Under what conditions might auto-
matic payments become blocked?
2.
How does having the transactions used by ap-
plications access resources in the same order
help to prevent deadlocks? What is the possible
impact on blocked transactions?
3.
Some of the savings and loan's reporting re-
quirements built into the Web application are
summary reports based on the specific moment
in time when the report is run. How can you en-
sure this?
3.
Why is concurrent transaction processing im-
portant in this environment?
4.
What one action can you take to increase the
relative isolation between transactions? What
are the potential benefits of this, if any? Potential
disadvantages?
4.
The reporting module of the third-party account-
ing application defaults to the read committed
transaction isolation level. Reporting is consid-
ered a low-priority function. Some reports must
make multiple reads of the source tables to col-
lect the information needed. How can you justify
not using a higher isolation level?
Concurrent Transactions
A small Midwestern savings and loan provides three
basic types of services to its customers—various types
of savings accounts, various checking account op-
tions, and loans. Most of the internal account functions
367
