Summary

Today, you learned how to perform statements in transactions. Transactions are groups of statements that must either succeed or fail as a group. You learned why SQL Server needs locking and how locking affects performance. Deadlock occurs when two or more processes want data locked by each other. The server resolves deadlock by terminating one of the processes to allow the other to continue.

Q&A

Q Can I set user priorities so that some users requesting locks go to the head of the line, while others have to wait?

A No. This is an often requested feature, however, and may be implemented in a future release.

Q Is there an exclusive page-level lock optimizer hint?

A No, there is not. You can acquire update locks, instead, or an exclusive table lock.

Q What happens if I begin a transaction, perform some changes on a table, and log out before committing or rolling back?

A If you lose your connection to the server for any reason while a transaction is active, the server assumes some type of failure occurred and automatically rolls back the transaction.

Q What cardinal sins should I be sure to avoid when dealing with transactions?

A Never allow user interaction during a transaction. Don’t perform unrelated work inside the same transaction. Don’t nest transactions.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  If I submit the query SELECT * FROM AUTHORS, what locks are likely to result?

2.  How about this query:

select  *
from      authors a join titleauthor ta on a.au_id = ta.au_id
             join titles t on ta.title_id = t.title_id

3.  What are the four transaction control statements, and what do they do?

Exercise

Today’s exercise is composed of a single task.

You have been assigned the job of making some changes in the pubs database. Your task list reads as follows:

•  Delete Ann Dull from the authors table. If she has any books in titles, assign their authorship to another author who has books for that publisher.

•  The Ringers got divorced. Anne Ringer has requested that her name be changed to Anne Slotsky. Also, there was a book on which the Ringers collaborated. Part of the divorce settlement was for Mr. Ringer to pay Mrs. Ringer for her book rights. Please remove Mrs. Ringer from this book.

Your assignment is to accomplish all tasks in the list and to group related statements together inside transactions. Be careful not to put unrelated work in the same transaction.