More About Transactions and Composite Transact-SQL Constructs - Typical Transaction-related Problems
(Page 6 of 6 )
Transactions are a powerful weapon in the hands of a programmer, but improper use can cause substantial damage. I will try to forewarn you of some typical problems.
A Never-ending Story
The worst thing that you can do is to explicitly open a transaction and then forget to close it. All changes sent to the database through that connection will become part of that transaction; resources normally released at the end of a transaction are held indefinitely; other users cannot access resources; and eventually, your server chokes.
Spanning a Transaction over Batches
A transaction can span batches. SQL Server counts transactions over the connection, so it is “legal” to issue two batches like this over one connection:
Begin Transaction
update dbo.Inventory
set Lease = 0
where LeaseScheduleId = 141
Go
update dbo.LeaseSchedule
Set PeriodicTotalAmount = 0
where ScheduleId = 141
Commit Transaction
Go
However, I cannot think of any justification for doing so, and you significantly increase the probability of error. For example, you could easily forget to finish the transaction.
TIP
There are some cases in which it is justified for a transaction to span batches; for example, when a DDL statement must be in a separate batch.
Rollback Before Begin
Sometimes you might set your error handling so that all errors that occur in a stored procedure are treated in the same way. Naturally, you will include a statement to roll back the transaction. If an error occurs before the transaction starts, the stored procedure will jump to the error handling code and another error will occur:
Create Procedure dbo.ap_LeaseShedule_Clear_1
-- Set value of Lease of all equipment associated
-- with expired Lease Schedule to 0
-- Set total amount of Lease Schedule to 0.
@intLeaseScheduleId int
As
-- Verify that lease has expired
If GetDate() < (select EndDate
from dbo.LeaseSchedule
where ScheduleId = @intLeaseScheduleId)
Raiserror ('Specified lease schedule has not expired yet!', 16,1)
-- If error occurs here,
-- server will execute Rollback before transaction is started!
if @@Error <> 0 goto PROBLEM
Begin Transaction
-- Set value of Lease of all equipment associated
-- with expired Lease Schedule to 0
update dbo.Inventory
set Lease = 0
where LeaseScheduleId = @intLeaseScheduleId
if @@Error <> 0 goto PROBLEM
-- Set total amount of Lease Schedule to 0
update dbo.LeaseSchedule
Set PeriodicTotalAmount = 0
where ScheduleId = @intLeaseScheduleId
if @@Error <> 0 goto PROBLEM
commit transaction
return 0
PROBLEM:
print 'Unable to eliminate lease amounts from the database!'
rollback transaction
return 1
Multiple Rollbacks
Unlike Commit statements, only one Rollback statement is required to close a set of nested transactions. In fact, if more than one Rollback statement is executed, SQL Server will raise another error.
Long Transactions
SQL Server places locks on data that has been modified by a transaction, to prevent other users from further changing the data until the transaction is committed. This feature can lead to problems if a transaction takes “too long” to complete.
NOTE
There is no exact definition of “too long.” The longer a transaction works, the greater the likelihood that problems will occur.
Some of the problems that might occur if a long transaction is present in the database include the following:
- Other users are blocked. They will not be able to access and modify data.
- The transaction log fills up. (SQL Server 2000 and SQL Server 7.0 can be configured to automatically increase the size of the transaction log, but you could fill your disk as well.)
- Most of the time, transaction log work is performed in memory. If all available memory is used before the transaction is complete, SQL Server will start saving changes to disk, thus reducing the overall performance of the server.
TIP
You should be particularly aware of concurrency problems because they are the problems most likely to happen. While you are developing applications, you will probably work alone (or in a small group) on the server, but the situation will change drastically when you place 50, 250, or 5000 concurrent users on the production server.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
