Case Study: Doug s Car Wash and Donuts

Case Study: Doug's Car Wash and Donuts

SCENARIO

ESSENCE OF THE CASE

Here are the essential elements of the case:

• Create a robust SQL batch that can insert the data into the database, so it doesn't get in there in little pieces that don't link together.

• Design the inserts and updates to avoid contention with the reporting that Doug is trying to run.

Doug is starting a new career with a great business idea: a car wash and donut stand, so people can stop in on their way to work, get some donuts, and get their car washed at the same time. He's using some little wireless gadgets to have the attendants put in the name and address of his customers, so they can win free donuts. The problem is that the wireless connections seem to drop a lot, probably due to all the electrical motors in the car wash, and he's getting partial information into his database. He's also having problems with deadlock errors, between the attendants at the donut stand and the ones at the car wash entrance . What can Doug do to keep his dreams alive ?

ANALYSIS

First, build a transaction that encapsulates all the inserts and updates; that way if the connection fails mid-stream, the data updates automatically roll back. Write the application, so that the transactions encapsulate only the UPDATE and INSERT statements. That way the critical time ”the time when the application is transmitting the statements that change data ”is kept to a minimum.

Then, make sure that all the different transaction types access tables in the same order. This prevents deadlocks and helps the customers get out the door quickly.