Day 13

Quiz

1:What is the difference between ROLLBACK TRANSACTIONS and ROLLBACK TRANSACTIONS <savepoint>?
A1: When you roll back a transaction to a savepoint, the transaction remains active, and any locks that existed before are still available. Without a savepoint, ROLLBACK TRANSACTIONS reverses all work in the transaction, ends the active transaction, and releases locks.
2:How do the following settings affect your programming: 
set implicit_transactions on

A2: Implicit transactions omit the automatic COMMIT record that would normally be included with single-statement transactions. Whenever you are executing single-statement transactions, you need to include an explicit COMMIT TRANSACTIONS statement to commit your work. Otherwise, your work will never be permanently saved, and locks will be held until you log out.
set xact_abort on

When you use the xact_abort setting, you can omit error-trapping code in your transactions. As long as a transaction is active, the server will abort any batch and roll back all work whenever a runtime error occurs.

set lock_timeout 0

The lock_timeout setting should not matter because your programs already need to be able to react to a query timeout, when a query runs too long. Behavior when the process fails on a lock timeout error should be the same as the query timeout.

Exercise

1:In the following exercise, correct this batch to avoid a common transaction programming problem. Describe the problem and then suggest a solution. 
begin transaction
if (
    select avg(UnitPrice)
    from Products
    where Category = 1
) > 15
update Products
   set UnitPrice = UnitPrice * .90
 where CategoryID = 1
/* implement 30-day price protection */
update [Order Details]
   set UnitPrice = UnitPrice * .90
 where ProductID in (
       select ProductID
       from Products
       where CategoryID = 1
       )
   and OrderID in (
       select OrderID
       from Orders
       where datediff(dd, OrderDate, getdate()) <= 30
       )
commit transaction

A1: Problem: There are partial transactions. Products is not being updated, but Order Details is.

Solution: There is no error trapping in transaction allows updates to continue after error.

Run the batch using the xact_abort setting.

set xact_abort on     --   <<--
begin transaction
if (
    select avg(UnitPrice)
    from Products with (xlock)
    where Category = 1
) > 15
update Products
   set UnitPrice = UnitPrice * .90
 where CategoryID = 1
/* implement 30-day price protection */
update [Order Details]
   set UnitPrice = UnitPrice * .90
 where ProductID in (
       select ProductID
       from Products
       where CategoryID = 1
       )
   and OrderID in (
       select OrderID
       from Orders
       where datediff(dd, OrderDate, getdate()) <= 30
       )
commit transaction

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset