/*
What is a transaction? pp. 399
"In database terms, a transaction is any action that reads from and/or
writes to a database."

AS we will see, this is a very broad definition and a transcation is much 
more than this.

Take the new Northwind transction (see handout).

What are the steps to "add" this transction to the database?

1. Insert the "1 table" entry
2. Insert the "many table" entries.
3. Update any related tables with accumulator attributes

*/

USE Northwind

--INSERT Order row
SELECT * FROM Orders ORDER BY OrderID DESC

INSERT INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate,
                    ShipVia, Freight, ShipName, ShipAddress, ShipCity,ShipRegion,
                    ShipPostalCode, ShipCountry)
VALUES
(1201, 'ISLAT', 1, GETDATE(), DATEADD(DAY, 7, GETDATE()), 3, 4.50, 'shipname',
 'ShipAddress', 'ShipCity', 'ShipRegion', 'ShipPostalCode', 'UK')

sp_help Orders

/*
We can't input the OrderID because it is an identity field
Omit OrderID from the INSERT statement

Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Orders' when IDENTITY_INSERT is set to OFF.
*/

INSERT INTO Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
                    ShipVia, Freight, ShipName, ShipAddress, ShipCity,ShipRegion,
                    ShipPostalCode, ShipCountry)
VALUES
('ISLAT', 1, GETDATE(), DATEADD(DAY, 7, GETDATE()), 3, 4.50, 'shipname',
 'ShipAddress', 'ShipCity', 'ShipRegion', 'zip', 'UK')

SELECT * FROM Customers WHERE CompanyName = 'Island Trading'
SELECT * FROM Employees WHERE LastName = 'Davolio'
SELECT * FROM Shippers WHERE CompanyName = 'Federal Shipping'

/*
How can we retreive the newly created surrogate key inside the 
transaction?

@@Identity
*/

SELECT * FROM Orders ORDER BY OrderID DESC

--INSERT Order Details row(s)
SELECT * FROM [Order Details] ORDER BY OrderID DESC

--Note, because OrderID is an Identity field,  your local transaction
--may not be the same value

INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
   VALUES (11083, 3, 10.00, 5, 0.0)
INSERT  INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
   VALUES (11083, 10, 31.00, 6, 0.0)
INSERT  INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
   VALUES (11083, 16, 17.45, 12, 0.0)

--This transaction violates referential integrity, there is not productid 3000
INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
   VALUES (11083, 3000, 10.00, 5, 0.0)

--This transaction should fail because of duplicate value in the primary key
INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
   VALUES (11083, 3, 10.00, 5, 0.0)

--UPDATE Products.UnitsOnOrder
SELECT * FROM Products WHERE ProductID IN(3, 10, 16)

UPDATE Products SET UnitsOnOrder = UnitsOnOrder + 5 WHERE ProductID = 3
UPDATE Products SET UnitsOnOrder = UnitsOnOrder + 6 WHERE ProductID = 10
UPDATE Products SET UnitsOnOrder = UnitsOnOrder + 12 WHERE ProductID = 16

/*
Notice that there was more than one "(1 row(s) affected)" message for
my NorthWind database. That is because I have a trigger that automatically 
creates an entry in the table LogProducts. We'll explore this later...

*/
SELECT * FROM logProducts

/*
The steps are correct, but what can we say about the integrity of the transaction?

ACIDS pp. 401.
*/

--BEGIN TRAN, COMMIT TRAN and ROLLBACK TRAN pp. 402
BEGIN TRAN TranName
--SQL
COMMIT TRAN TranName

--IF @@ERROR <> 0

--Customer error messages using RAISERROR('Message', 16,1)
RAISERROR('Tranasction rolledback due to duplicate pk value', 16,1)

SELECT * FROM Master.dbo.sysmessages

/*
ROLLBACK is possible because the DBMS keeps a transaction log of all
changes to a database (well, nearly all).

The transaction log pp. 402  (Table 10.1)

Most developers use a third-party tool to read this log file. .ldf
*/

DBCC log (Northwind, type=2)


/*
ROLLBACK will return the database to a consistent state, but what if we want to
delete the order we have created? We reverse the steps.

1. Update any related tables with accumulator attributes
2. Delete the "many table" entries.
3. Delete the "1 table" entry

*/
--Reverse changes to accumulator
UPDATE Products SET UnitsOnOrder = UnitsOnOrder - 5 WHERE ProductID = 3
UPDATE Products SET UnitsOnOrder = UnitsOnOrder - 6 WHERE ProductID = 10
UPDATE Products SET UnitsOnOrder = UnitsOnOrder - 12 WHERE ProductID = 16

--Remove the line items
DELETE [Order Details] WHERE OrderID = 11083

--Remove the orders entry
DELETE Orders WHERE OrderID = 11083

--Now let's redo it as a single ACID transaction

BEGIN TRAN InsertNewOrder

  DECLARE @NewOrderID AS INT
  DECLARE @ErrorCode INT, @TranSuccessful BIT, @ErrorTemp INT
  SET @TranSuccessful = 1

  --Insert the 1 table entry
  INSERT INTO Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
                      ShipVia, Freight, ShipName, ShipAddress, ShipCity,ShipRegion,
                      ShipPostalCode, ShipCountry)
     VALUES
       ('ISLAT', 1, GETDATE(), DATEADD(DAY, 7, GETDATE()), 3, 4.50, 'shipname',
        'ShipAddress', 'ShipCity', 'ShipRegion', 'ZIP', 'UK')
  SET @ErrorCode = @@ERROR; IF (@ErrorCode <> 0) SET @TranSuccessful = 0 --False

  SET @NewOrderID = @@Identity

  --Insert the Line item entries
  INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
     VALUES (@NewOrderID, 3, 10.00, 5, 0.0)
  SET @ErrorCode = @@ERROR; IF (@ErrorCode <> 0) SET @TranSuccessful = 0 --False

  INSERT  INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
     VALUES (@NewOrderID, 10, 31.00, 6, 0.0)
  SET @ErrorCode = @@ERROR; IF (@ErrorCode <> 0) SET @TranSuccessful = 0 --False

  INSERT  INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
     VALUES (@NewOrderID, 16, 17.45, 12, 0.0)

  --Update accumulator variable
  UPDATE Products SET UnitsOnOrder = UnitsOnOrder + 5 WHERE ProductID = 3
  SET @ErrorCode = @@ERROR; IF (@ErrorCode <> 0) SET @TranSuccessful = 0 --False

  UPDATE Products SET UnitsOnOrder = UnitsOnOrder + 6 WHERE ProductID = 10
  SET @ErrorCode = @@ERROR; IF (@ErrorCode <> 0) SET @TranSuccessful = 0 --False

  UPDATE Products SET UnitsOnOrder = UnitsOnOrder + 12 WHERE ProductID = 16
  SET @ErrorCode = @@ERROR; IF (@ErrorCode <> 0) SET @TranSuccessful = 0 --False

  IF @TranSuccessful = 0
     ROLLBACK TRAN InsertNewOrder
  ELSE
     COMMIT TRAN InsertNewOrder

  SELECT * FROM ORders ORDER BY OrderID DESC

/*
Even though it is possible to ROLLBACK and reverse a transction, things aren't always that simple...

Concurrency control pp. 404

*/
--Lost updates pp. 404

--Copy the two sets of transactions into different sql query analyzer windows
--------------------------------------
--Transaction 1
--Original value of UnitsOnOrder for 'Aniseed Syrup' is 70

BEGIN TRAN
DECLARE @UnitsOnOrder INT

SET @UnitsOnOrder = (SELECT UnitsOnOrder FROM Products WHERE ProductID = 3)

--Force a delay in processing that is less than for transaction 2
WAITFOR DELAY '00:00:5'

UPDATE Products SET UnitsOnOrder = @UnitsOnOrder + 100  WHERE ProductID = 3

COMMIT TRAN



--------------------------------------
--Transaction 2
--Original value of UnitsOnOrder for 'Aniseed Syrup' is 70

BEGIN TRAN
DECLARE @UnitsOnOrder INT

SET @UnitsOnOrder = (SELECT UnitsOnOrder FROM Products WHERE ProductID = 3)

--Force a delay in processing that is more than for transaction 1
WAITFOR DELAY '00:00:10'

UPDATE Products SET UnitsOnOrder = @UnitsOnOrder - 30 WHERE ProductID = 3

COMMIT TRAN

/*
Execution sequence
-----------------------------------------------------------
Time   Transaction Step                        Stored Value
-----------------------------------------------------------
1      T1          Read UnitsOnOrder           70
2      T2          Read UnitsOnOrder           70
3      T1          Update @UnitsOnOrder + 100  
4      T1          Write @UnitsOnOrder         170
5      T2          Update @UnitsOnOrder - 30
6      T2          Write @UnitsOnOrder         40

What should the value of UnitsOnOrder be? 
70 + 100 = 170
170 - 30 = 140

The transaction "70 + 100" was lost! It was overwritten by Transaction2.

*/

SELECT ProductName, UnitsOnOrder FROM Products WHERE ProductID = 3

--Reset the value of UnitsOnOrder for 'AniseedSyrup'
UPDATE Products SET UnitsOnOrder = 70 WHERE ProductID = 3

/*
Uncommitted Data (or dirty read) pp. 405

Uncommitted Data occurs if instead of COMMIT, transaction 1 executes a ROLLBACK
thus reversing the change in the database. 

*/

--Copy the two sets of transactions into different sql query analyzer windows
--------------------------------------
--Transaction 1 -Uncommitted Data
--Original value of UnitsOnOrder for 'Aniseed Syrup' is 70

BEGIN TRAN
DECLARE @UnitsOnOrder INT

SET @UnitsOnOrder = (SELECT UnitsOnOrder FROM Products WHERE ProductID = 3)

UPDATE Products SET UnitsOnOrder = @UnitsOnOrder + 100  WHERE ProductID = 3

--Force a delay in processing that is less than for transaction 2
WAITFOR DELAY '00:00:5'

ROLLBACK TRAN


--------------------------------------
--Transaction 2 -Uncommitted Data
--Original value of UnitsOnOrder for 'Aniseed Syrup' is 70

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --Note, I have to allow dirty reads
BEGIN TRAN
DECLARE @UnitsOnOrder INT

SET @UnitsOnOrder = (SELECT UnitsOnOrder FROM Products WHERE ProductID = 3)

UPDATE Products SET UnitsOnOrder = @UnitsOnOrder - 30 WHERE ProductID = 3

--Force a delay in processing that is more than for transaction 1
WAITFOR DELAY '00:00:10'

COMMIT TRAN

/*
Execution sequence
-----------------------------------------------------------
Time   Transaction Step                        Stored Value
-----------------------------------------------------------
1      T1          Read UnitsOnOrder           70
2      T1          Update @UnitsOnOrder + 100  
3      T1          Write @UnitsOnOrder         170
4      T2          Read UnitsOnOrder           170
5      T2          Update @UnitsOnOrder - 30
6      T1          ROLLBACK                    70 
7      T2          Write @UnitsOnOrder         140

What should the value of UnitsOnOrder be? 
Trans 1
70 + 100 = 170
ROLLBACK

Trans 2
70 - 30 = 40

What is the actual value?
170 - 30 = 140
Because the transaction2 was allowed to read uncommitted (dirty) data.

Note that in SQL Server, by default, transactions are not allowed to execute
dirty reads. I had to reset the isolation level in Transaction 2 to allow it.

*/

SELECT ProductName, UnitsOnOrder FROM Products WHERE ProductID = 3

--Reset the value of UnitsOnOrder for 'AniseedSyrup'
UPDATE Products SET UnitsOnOrder = 70 WHERE ProductID = 3


/*
Inconsistent Retrieval pp. 405

When a transaction calcualtes some summary (aggregate) function
over a set of data while other transactions are updating the 
data.

I'll simulate this problem by using a cursor.

*/

 --Copy the two sets of transactions into different sql query analyzer windows
--------------------------------------
--Transaction 1 -Inconsistent Retrieval
--Original value of UnitsOnOrder for 'Aniseed Syrup' is 70

BEGIN TRAN

 --Declare local variable
  DECLARE @ProductID INT, @UnitsOnOrder INT, @TotalUnitsOnOrder INT

  SET @TotalUnitsOnOrder = 0

  --Declare cursor and define cursor
  DECLARE curUnitsOnOrder CURSOR
  FOR
     SELECT ProductID, UnitsOnOrder
     FROM Products
     ORDER BY ProductID

  OPEN curUnitsOnOrder

  --Read first row (priming read...)
  --Fetch the first ProductID, UnitsOnOrder from the cursor
  FETCH curUnitsOnOrder
     INTO @ProductID, @UnitsOnOrder

  WHILE @@FETCH_STATUS = 0 -- while not eof
  BEGIN

   --Once Product 3 has been read, wait for transaction 2
   IF @ProductID = 3
      WAITFOR DELAY '00:00:5'

   --Accumulate TotalUnitsOnOrder
   SET @TotalUnitsOnOrder = @TotalUnitsOnOrder + @UnitsOnOrder

     --Fetch next ProductID, UnitsOnOrder from the cursor
     FETCH curUnitsOnOrder
        INTO @ProductID, @UnitsOnOrder

  END

  --Echo the accumulated value
  SELECT  @TotalUnitsOnOrder AS TotalUnitsOnOrder

  --Echo actual value in table
  SELECT SUM(UnitsOnOrder) AS TotalUnitsOrderActual FROM Products

  --Garbage collection
  CLOSE curUnitsOnOrder
  DEALLOCATE curUnitsOnOrder

COMMIT TRAN

--------------------------------------
--Transaction 2 -Inconsistent Retrieval
--Original value of UnitsOnOrder for 'Aniseed Syrup' is 70

BEGIN TRAN
DECLARE @UnitsOnOrder INT

SET @UnitsOnOrder = (SELECT UnitsOnOrder FROM Products WHERE ProductID = 3)

UPDATE Products SET UnitsOnOrder = @UnitsOnOrder - 30 WHERE ProductID = 3

COMMIT TRAN 

/*
Inconsistent Retrieval
Execution sequence
-------------------------------------------------------------------------
Time   Transaction Step                                      Stored Value
-------------------------------------------------------------------------
1      T1          Read UnitsOnOrder for ProductID 1          0
2      T1          Read UnitsOnOrder for ProductID 2          40
3      T1          Read UnitsOnOrder for ProductID 3          70
4      T2          Read UnitsOnOrder for ProductID 3          70
5      T2          Update @UnitsOnOrder - 30
6      T2          Write @UnitsOnOrder                        40
7      T1          Read UnitsOnOrder for ProductID 4          0
   :   and so on...
   :
n      T1          Read UnitsOnOrder for ProductID n           n
n+1    T1          Read UnitsOnOrder for ProductID 3           40
n+2    T1          Compute Product UnitsOnOrder as % of total
n+3    T1          Write Computed value                        5.006258 (should be 8.760900)  

*/


/*
A phantom read would exist with the previous example if Transaction2, rather
than updating UnitsOnOrder for ProductID 3, deleted the Product with ProductID=3.
Similarily, if a new product was added by transaction 2 before transactionaction1 
finished the newly inserted row would be a phantom read.
*/


/*
A word about the scheduler... pp. 407
   Specialized DBMS program
   Interleaves database operations
   Attempts to ensure ACID
   Also attempts to optimize CPU usages and minimize I/O

*/

/*
Soo... aside from relying on the scheduler,
how to insure the serializability of transactions?
That is, so that operations in one transaction do not have
a negative impact on other concurrent transactions.

One method is Locking pp. 408

*/


--Lost update prevented with locking
/*
Lock granularity pp. 408

  Level              SQL Server locking hint: FROM tablename WITH(lock_type)
  --------------------------------------------------------------------------
  Database-level     
  Table-level        TABLOCK
  Page-level         PAGLOCK
  Row-level          ROWLOCK
  Field-level

  Lock Type            SQL Server locking hint: FROM tablename WITH(lock_type)
  ----------------------------------------------------------------------------
  Binary
  Shared/Exclusive     WITH(HOLDLOCK)/WITH(XLOCK)

*/

--Copy the two sets of transactions into different sql query analyzer windows
--------------------------------------
--Transaction 1
--Original value of UnitsOnOrder for 'Aniseed Syrup' is 70

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
DECLARE @UnitsOnOrder INT

SET @UnitsOnOrder = (SELECT UnitsOnOrder FROM Products WHERE ProductID = 3)

--Force a delay in processing that is less than for transaction 2
WAITFOR DELAY '00:00:5'

UPDATE Products SET UnitsOnOrder = @UnitsOnOrder + 100  WHERE ProductID = 3

COMMIT TRAN


--------------------------------------
--Transaction 2
--Original value of UnitsOnOrder for 'Aniseed Syrup' is 70

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
DECLARE @UnitsOnOrder INT

SET @UnitsOnOrder = (SELECT UnitsOnOrder FROM Products WHERE ProductID = 3)

--Force a delay in processing that is more than for transaction 1
WAITFOR DELAY '00:00:10'

UPDATE Products SET UnitsOnOrder = @UnitsOnOrder - 30 WHERE ProductID = 3

COMMIT TRAN

SELECT ProductName, UnitsOnOrder FROM Products WHERE ProductID = 3

/*
Now what happended?
Server: Msg 1205, Level 13, State 50, Line 10
Transaction (Process ID 55) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Transaction 1 completed but Transaction 2 was killed or terminated.

The value of UnitsOnOrder is 170.

Deadly embrace. pp. 302

The two transactions acquired an exclusive lock on the Products table and the scheduler
decided to kill transaction 2.

We can indicate to the scheduler that we prefer that, in a conflict, transaction 1 be given
lower priority with:

SET DEADLOCK_PRIORITY LOW

*/
SELECT * FROM 


/*
We can get the correct result by locking at the table level and not the transaction level.
This technique will require transaction 2 to wait.
*/

--Table locking
SELECT * FROM EMPLOYEES WITH(XLOCK)

--Transaction 1 --Using WITH(XLOCK)
--Original value of UnitsOnOrder for 'Aniseed Syrup' is 70

--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
DECLARE @UnitsOnOrder INT

SET @UnitsOnOrder = (SELECT UnitsOnOrder FROM Products WITH(XLOCK) WHERE ProductID = 3 )

--Force a delay in processing that is less than for transaction 2
WAITFOR DELAY '00:00:5'

UPDATE Products SET UnitsOnOrder = @UnitsOnOrder + 100  WHERE ProductID = 3

COMMIT TRAN


--Transaction 2 --Using WITH(XLOCK)

--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
DECLARE @UnitsOnOrder INT

SET @UnitsOnOrder = (SELECT UnitsOnOrder FROM Products WITH(XLOCK) WHERE ProductID = 3 )

--Force a delay in processing that is more than for transaction 1
WAITFOR DELAY '00:00:10'

UPDATE Products SET UnitsOnOrder = @UnitsOnOrder - 30 WHERE ProductID = 3

COMMIT TRAN


SELECT ProductName, UnitsOnOrder FROM Products WHERE ProductID = 3

--Reset the value of UnitsOnOrder for 'AniseedSyrup'
UPDATE Products SET UnitsOnOrder = 70 WHERE ProductID = 3


/*
Another example of deadlock.
*/

----------------------------------------
--Trans1 and Trans2 are given as an illustration of a deadlock
/*
Trans1                    Trans2
Locks Employees           Locks Orders
Attempts to Lock Orders   Attempts to Lock Employee
Wait                      Wait
Deadlock...
*/
--SELECT * FROM Employees WHERE EmployeeID = 1
--SELECT * FROM Orders WHERE EmployeeID = 1
---------------------------------------------------------------------
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

--SET DEADLOCK_PRIORITY LOW

BEGIN TRAN

   UPDATE employees SET titleofcourtesy = 'Mrs.' WHERE employeeid = 1

   --Artificially delay processing  
   WAITFOR DELAY '00:00:05' 

   UPDATE Orders Set ShipRegion = 'G' WHERE OrderID = 10258

   IF @@Error = 0
     BEGIN
        COMMIT TRAN
        PRINT 'Trans committed.'
     END
   ELSE
     BEGIN
        ROLLBACK TRAN
        PRINT 'Trans rolled back. Error: ' + CAST(@@Error AS NVARCHAR(3))
        --or
        --RAISERROR ('Trans rolled back. Error: ' + CAST(@@Error AS NVARCHAR(3)), 16, 1)
     END

---------------------------------------------------------------

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

   UPDATE Orders Set ShipRegion = NULL WHERE OrderID = 10258

   --Artificially delay processing  
   WAITFOR DELAY '00:00:03' 

   UPDATE employees SET titleofcourtesy = 'Mrs.' WHERE employeeid = 1

   IF @@Error = 0
     BEGIN
        COMMIT TRAN
        PRINT 'Trans committed.'
     END
   ELSE
     BEGIN
        ROLLBACK TRAN
        PRINT 'Trans rolled back. Error: ' + CAST(@@Error AS NVARCHAR(3))
        --or
        --RAISERROR ('Trans rolled back. Error: ' + CAST(@@Error AS NVARCHAR(3)), 16, 1)

     END

/*

*/