The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P94: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | Monitoring and Troubleshooting Chapter 10 447 Cashier 1 starts ringing in the first customer. The first customer buys books 1 2 and 3. Each book shows 10 copies in stock. The customer is purchasing 1 copy of each book. Cashier 2 starts ringing in the second customer. The second customer buys books 1 3 and 4. Each book shows 10 copies in stock. The customer is purchasing 1 copy of each book. Cashier 1 finishes the sale and the system updates the in stock amount to show 9 copies in stock. Cashier 2 finishes the sale and the system updates the in stock amount to show 9 copies in stock. The problem is that even though there were two copies sold the system only shows one copy sold. Now the in-stock amount is out of sync with what is actually in the store. There are a couple different solutions to this problem. One removes the concurrency issues without causing a work slowdown for the cashiers. Another removes the concurrency issue but causes a work slowdown. In the first there is a good deal of code change which has to be made to the client and the database in the second the solution requires minimal code change to the client and no change to the database. In the first solution instead of passing the ending total back to the database as 9 the system passes back that 1 has been sold. The database code must be changed to take the value being passed in and subtract it from the stored stock in hand. In the second solution you configure the cashiers computers and the server to support distributed transactions. Then you set the isolation level to a higher level of transaction from the default. This will cause the SQL Server to take a lock on the record that contains the stock data. The stock can then updated using the current technique. When the transaction is committed the system completes the update and commits the transaction to the database. The problem with this technique is that when the second cashier tries to ring up the second customer the cash register won t respond
