/* Chapter 9: Managing Multiuser databases Concurrency control pp. 296 Need for Atomic transactions (more on ACID later) What is a transaction? pp. 297 Concurrent transaction pp. 297 */ USE Northwind --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 /* Lost update 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 /* Uncommitted Data 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 SELECT * FROM logProducts /* Inconsistent Retrieval or Nonrepeatable Read pp. 300 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 --Compute UnitsOnOrder for ProductID 3 as a percentage of the total --UnitsOnOrder for the entire table BEGIN TRAN --Declare local variable DECLARE @ProductID INT, @UnitsOnOrder REAL, @TotalUnitsOnOrder REAL 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 --First read of ProductiD = 3 IF @ProductID = 3 BEGIN WAITFOR DELAY '00:00:5' SELECT @UnitsOnOrder AS FirstReadOfUnitsOnOrderForProduct3 END --Accumulate TotalUnitsOnOrder SET @TotalUnitsOnOrder = @TotalUnitsOnOrder + @UnitsOnOrder --Fetch next ProductID, UnitsOnOrder from the cursor FETCH curUnitsOnOrder INTO @ProductID, @UnitsOnOrder END --Garbage collection CLOSE curUnitsOnOrder DEALLOCATE curUnitsOnOrder --Second read of ProductiD = 3 which is nonrepeatable SET @UnitsOnOrder = (SELECT UnitsOnOrder FROM Products WHERE ProductID = 3) --Echo the accumulated value TotalUnitsOnOrder SELECT @TotalUnitsOnOrder AS TotalUnitsOnOrderAccumulated --Echo actual value in table SELECT SUM(UnitsOnOrder) AS TotalUnitsOrderActual FROM Products SELECT @UnitsOnOrder AS SecondReadOfUnitsOnOrderForProduct3 --Echo the Product3 UnitsOnOrder as a percentage of total SELECT (@UnitsOnOrder/@TotalUnitsOnOrder)*100 AS UnitsOnOrderAsPercentageOfTotalComputed SELECT (70.0/799)*100 AS UnitsOnOrderAsPercentageOfTotalCorrect 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. */ /* The Scheduler Specialized DBMS program Interleaves database operations Attempts to ensure ACID Also attempts to optimize CPU usages and minimize I/O */ /* Resource locking pp. 301 Implicit/Explicit locks FROM EMPLOYEES WITH(XLOCK) Lock granularity Exclusive/Share locks */ --Lost update prevented with locking UPDATE Products SET UnitsOnOrder = 70 WHERE ProductID = 3 --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 /* ACID pp. 305 */ --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. 316 (Figure 9-16) Most developers use a third-party tool to read this log file. */ SELECT * FROM logProducts DBCC log (Northwind, type=2)