Now you can commit or roll back the transaction. If you want to keep your tables in their original condition, roll back this transaction to undo all four deletes. If you had been doing this without the benefit of a transaction, and something happened in the middle, you would not have the opportunity to undo the deletes.

rollback
/* or */
commit

Grouping Statements in Transactions

Transactions give you the flexibility to group related transactions together in batches. The keyword I’d like you to notice in this statement is “related.” In the previous example, the rows in tables with foreign key relationships to the titles table definitely had an interest in succeeding or failing as a group with one another and with the deletion of the primary key in titles.

All this is a good thing. But there is too much of a good thing. Don’t group unrelated statements together inside the same transaction. If you wanted to delete a title and related rows in sales, roysched, and titleauthor, plus change an author’s address and update a publisher’s name, those are three separate operations; they should appear in three separate transactions.

Transactions should hold as little work as possible. The goal in writing transactions is to minimize the amount of time spent inside the transaction, while preserving the integrity of your data should a failure occur.

The reason you want to spend as little time in transactions as possible is due to the locking that occurs on the server.

Locking

To prevent two users from modifying the same data at the same time, the server allows the first user to request a page of data to have it. This locking is automatic and does not need to be specifically requested.

Why Is Locking Necessary?

In multiuser database systems, locking is a necessary evil. Consider this problem: a hundred telemarketers are all entering orders into an order entry system. Inventory is tracked in an inventory table. When an order is submitted, the order entry system must do the following things:

•  Deduct the quantity ordered from inventory.

•  Verify that there were enough items in inventory to satisfy the order. If not, roll back.

•  Insert a row in an invoice table.

•  Insert rows to inv_detail for each item ordered.

These operations would be a part of a single transaction. If enough items were not available to fill the order, no invoice should be created, and no rows should be added to the detail table, and, certainly, no deductions should be made from inventory.

Consider this timeline, which uses another kind of database that doesn’t use locking:

•  At 10:00 a.m., Bob takes an order for 10 blue widgets. He fills out his order entry screen and clicks on his Submit button.

•  At exactly the same time, Jane takes an order for 20 blue widgets.

•  Bob reads the inventory table, which has 25 widgets, and prepares to update it to deduct the 10 widgets he has on his order.

•  Jane checks the inventory table, which still has 25 widgets, and prepares to deduct 20.

What happens now? In some systems without locking, the last connection to write to the database dictates the value. So Bob will write back 25 – 10 = 15 widgets. Then Jane writes back 25 – 20 = 5 widgets. This is bad.

How Does Locking Help?

If these events were running on a SQL Server, when Bob first begins his update, his connection will acquire an exclusive lock on the page. If Jane tries to update the page, the server will put her connection to sleep for a few milliseconds, waiting for Bob to complete his work. After Bob updates the page, the lock is maintained until the transaction is committed. While Bob cleans up, writing to invoices and invoice detail, Jane’s connection must wait patiently.

Finally (all of this probably takes about a twentieth of a second, give or take a few milliseconds), Jane updates the inventory table, realizes there are not enough widgets, and undoes her work. The application program will tell Jane that there are not enough widgets.