Week 2

Day 13
Transactions

Transactions enable you to group statements together so that they succeed or fail as a group. Today, I talk about the following issues related to transactions:

•  What is a transaction?

•  How transactions and batches are related

•  Grouping statements into transactions—what belongs, what doesn’t

•  Transactions and locking

•  Controlling locking with DML (data modification language—an impressive-sounding phrase that means, “insert, update, and delete”)

•  Optimizer locking hints

•  Deadlock and how to minimize it

What Is a Transaction?

Consider a sales database with two related tables: inventory and sales_detail. The inventory table keeps track of how many widgets are in inventory, and the sales_detail table tracks how many widgets were sold on a particular invoice. When a sale is conducted, you need to subtract units from the inventory table and add them to the sales_detail table. Assuming an invoice record has already been created, here is what those statements might look like without transactions:

A transaction is a group of statements that must either succeed or fail as a group.

update     inventory
set       qty = qty - 1
where     item_id = 2012
insert      sales_detail(item_id, inv_id, datesold, qty)
            values(2012, 308, CURRENT_TIMESTAMP, 1)

These two statements have the effect of updating a row in the inventory table to adjust the current inventory, and then adding a row into the sales_detail table. In the sales_detail table, I used the niladic function CURRENT_TIMESTAMP to indicate the current time for the datesold column.

What would happen if there were an error after the update had completed, but before the insert occurs? There are lots of things that could happen to cause a problem, such as a network failure, a disk drive failure, client power outage, being out of space, and so on. If a problem prevented the insert from occurring, the inventory table would be out of whack. If these statements are couched inside a transaction, though, the server will ensure that if either statement fails to complete, any work that is done will be undone.

Here is how the two statements look inside a transaction:

begin transaction
update inventory
set    qty = qty - 1
where  item_id = 2012
       insert sales_detail(item_id. inv_id, datesold, qty)
       values(2012, 308, CURRENT_TIMESTAMP, 1)
commit transaction

Two transaction control statements were added to this code: BEGIN and COMMIT TRANSACTION. If something were to happen that prevented the transaction from completing, the server would ensure that the pieces of the transaction that completed were “rolled back,” or undone.

Transaction Control Statements

There are four basic transaction control commands:

•  BEGIN TRAN[SACTION] begins a new transaction.

•  COMMIT [TRAN[SACTION]] commits the transaction. Committing a transaction means, “I’m done; please make the changes permanent.”

•  ROLLBACK [TRAN[SACTION]] [SAVEPOINT] rolls back the transaction. This undoes all pending transactional modifications. If you have named a savepoint, you may roll back to the savepoint instead, to partially undo a transaction.

•  SAVE TRAN[SACTION] <NAME> marks a savepoint to which a transaction can be partially rolled back.

Implicit Transactions

Until now, you’ve issued data modification language—an INSERT, UPDATE, or DELETE statement—as islands unto themselves. Each statement affected some number of rows, and succeeded or failed all on its own. What you didn’t know, though, is that each of these statements was actually a part of its own transaction. When you issued an insert, such as

insert item(descr, qty) values(“Blue Widget”, 100)

it actually had an implicit begin/commit around, such as the following:

<begin tran>
insert item(descr, qty) values(“Blue Widget”, 100)
<commit tran>

---

Note:  Once a transaction has been committed, the change is permanent and cannot be undone. You can always delete an inserted row, of course, or issue a new update, but aside from redoing the work, there is no way to undo a committed transaction.

---

Error Checking in Transactions

Error checking is especially important when running transactions. Usually, this error checking is done inside stored procedures, where an error won’t have a human to interpret it. But I’d like to introduce you to it here, anyway.

After each data modification statement inside a transaction, check for an error by testing the global variable @@error. Here is the example from earlier, with error handling:

begin     transaction
     update  inventory
   set    qty = qty - 1
   where  item_id = 2012
   if @@error <> 0 begin
          rollback tran
         return
   end

       insert sales_detail(item_id, inv_id, datesold, qty)
       values(2012, 308, CURRENT_TIMESTAMP, 1)
   if @@error <> 0 begin
          rollback tran
         return
   end
commit transaction

If the server just up and dies in the middle, the error checking doesn’t help. When the server restarts, it will walk through the transaction log and undo any uncommitted transactions. Here, the error checking will catch “soft” errors. Some examples of errors that might get caught here include permissions problems, a table that no longer existed, or a deadlock problem. (Deadlock is discussed at the end of today, when locking is discussed.)

Transaction flow doesn’t affect program flow. After a ROLLBACK executes, processing continues with the next statement. If there is an error after the UPDATE statement, the code executed after the error rolls back the transaction and then exits the batch. If the return was not included, processing would continue with the INSERT statement.

If an error occurs after the second statement executes, the ROLLBACK undoes all work since the BEGIN transaction, including both the UPDATE and the INSERT.