Nesting Transactions

Transactions can be nested, but you should avoid nesting if possible. Transaction nesting is usually caused by stored procedures beginning a transaction and then calling another procedure, which also begins a transaction. There is a global variable, @@trancount, that will tell you the current transaction level.

When the transaction count reaches zero, the transaction is committed. Nesting transactions is dangerous because only the outermost commit manages to save work. The person who wrote the inside transaction intended for work to be committed, and it hasn’t been.

Table 13.1 shows the four transaction statements and their effect on the transaction count.

A naked rollback, or a rollback that does not name a savepoint, undoes all pending work. Although you would never nest transactions purposely, consider the effects of this next example. In the left margin, I’ve noted the transaction count that will result after the statement runs.

0    /* @@Trancount starts at 0 */
1    begin tran
1       insert sales values..
2        begin tran
2           update items set...
1        commit tran
0    commit tran

The first COMMIT doesn’t really do anything. After the first COMMIT, you might think that the UPDATE is committed and the INSERT is pending, but that isn’t so. If a ROLLBACK TRAN were executed between the two COMMITS, both the INSERT and the UPDATE would be rolled back.

Nested transactions don’t buy you anything, but they do cause headaches. Sometimes, if different people are writing procedures with transaction handling, nesting comes into play as an accidental consequence. Keep an eye out for it, and be aware that only the last COMMIT really matters.

Transactions and Batches

On Day 11, I talked about batches. Recall that a batch is a group of SQL statements that is compiled and executed as a group. Local variables last only for the duration of a batch. But transactions last across batches, until they are committed or rolled back.

This has good and bad implications. On the good side, it means that you have more flexibility in keeping a transaction open across multiple batches. You could, for example, begin a transaction, make a few changes, observe the effects of the changes, and undo it later. This isn’t done in production systems, but during development it’s nice to have this ability.

On the bad side, you must be especially wary of holding transactions open for long periods of time. This is because when a transaction is pending, the pages of data on which affected rows exist are locked. I’ll talk more about locking in the section titled “Locking.” What it means, though, is that no other connections can access locked data, and if you hold transactions open for too long, it will cause horrendous performance problems as people queue up for a shot at the same data.

Transaction Example with Multiple Batches

This example doesn’t affect the integrity of the pubs database. Although you will be deleting rows, the example completes by rolling back the transaction and undoing the deletes.

If you wanted to delete a title from the titles table, you would also need to delete rows in other tables that referenced the title. This is called a cascading delete. This work is usually done automatically, inside a trigger, but for this example we’ll perform the work interactively.

To delete a title, which has a primary key on the title_id column, you would first need to delete the matching rows from any table containing a foreign key on title_id. There are three tables containing a foreign key on title_id: roysched, titleauthor, and sales.

Here is the list of tasks that you will need to execute:

•  First, begin a transaction. The transaction will ensure that the deletes you perform can be undone at the end. If you were really deleting these rows, the transaction would ensure that all deletes of foreign keys occurred only if there were no problems with any statements.

•  Delete rows from the three related tables containing the title’s title_id. It does not matter from which foreign key table the rows are deleted first.

•  Delete the row from titles.

•  I will issue a ROLLBACK TRANSACTION as the last step, but if you wanted to make the delete permanent, you would issue a COMMIT TRANSACTION instead.

First, issue a BEGIN TRANSACTION and execute the following statement:

begin tran

This command did not return data, and did not return any rows.

This statement begins a transaction. Until a COMMIT or ROLLBACK is issued, the transaction will remain open, and the effects of all statements will be delayed until one of those two commands are received.

Now, start deleting rows from related tables. The title ID I want to delete is BU1032. I will delete from roysched first:

delete     roysched
where      title_id = ‘BU1032’

2   row(s)    affected

The deletion from the royalty schedule table is performed inside a transaction. If there is a problem here or later on, you can roll back the transaction and recover the deleted rows.

Your transaction is still active. Now, delete the rows in sales and titleauthor:

delete        titleauthor
where      title_id = ‘BU1032’
delete     sales
where      title_id = ‘BU1032’

2   row(s)    affected
2   row(s)    affected

These deletes are part of the same transaction as the first delete. As before, if there is a problem anywhere, you can roll back the transaction and restore the tables to their state before the transaction began. After deleting these rows, you are free to delete the primary key in the titles table:

delete        titles
where       title_id = ‘BU1032’