Database Management Systems

1. Database Management Systems Chapter 7 Database Integrity and Transactions

2. Objectives • Why would you need to use procedural code when SQL is so powerful? • How are SQL commands integrated into more traditional programming structures? • What capabilities exist in procedural code? • How are business rules added to the database? • How does a DBMS handle multiple transaction events? • How do you prevent problems arising when two processes change the same data? • What are the primary rules to ensure integrity of transactions? • How are key values generated? • How can procedural code track row-by-row through a query? • What issues arise when maintaining totals in the database?

3. Programming Environment DBMS • Create code (1) Within the query system (2) In forms and reports (3) Hosted in external programs Tables Queries (1) If ( . . ) Then SELECT . . . Else . . . UPDATE . . . End If External Program Forms & Reports (3) C++ if (. . .) { // embed SQL SELECT … } (2) If (Click) Then MsgBox . . . End If

4. User-Defined Function CREATE FUNCTION EstimateCosts (ListPrice Currency, ItemCategoryVarChar) RETURNS Currency BEGIN IF (ItemCategory = ‘Clothing’) THEN RETURN ListPrice * 0.5 ELSE RETURN ListPrice * 0.75 END IF END

5. Function to Perform Conditional Update CREATE FUNCTION IncreaseSalary (EmpID INTEGER, Amt CURRENCY) RETURNS CURRENCY BEGIN IF (Amt > 50000) THEN RETURN -1 -- error flag END UPDATE Employee SET Salary = Salary + Amt WHERE EmployeeID = EmpID; RETURN Amt END

6. Looking Up Data CREATE FUNCTION IncreaseSalary (EmpID INTEGER, Amt CURRENCY) RETURNS CURRENCY DECLARE CURRENCY MaxAmount; BEGIN SELECT MaxRaise INTO MaxAmount FROM CompanyLimits WHERE LimitName = ‘Raise’; IF (Amt > MaxAmount) THEN RETURN -1 -- error flag END UPDATE Employee SET Salary = Salary + Amt WHERE EmployeeID = EmpID; RETURN Amt; END

7. Programming Tools Sequence Variables Conditions Loops Input and Output Procedures and functions (subroutines)

8. (1) Sequence Control the order of execution for multiple tasks. INSERT INTO Customer (CustomerID, LastName, …) VALUES (1551, ‘Jones’, …); INSERT INTO Sales(SaleDate, CustomerID) VALUES (Now(), 1551);

9. (2) Variables Temporary locations in memory to hold data. FUNCTION ComputeTax(SalesTotal)… DECLARE Tax as currency; TaxRateas float; BEGIN SELECT TaxRate= SalesTax FROM TaxTable WHERE … Tax = SalesTotal*TaxRate; RETURN Tax; END; Scope: Within this function only.

10. (3) Conditions Compare values or logical tests. FUNCTION sample(…) BEGIN IF (amount < 0) THEN action 1 ELSE action 2 END IF END;

11. (4) Loops Repeat a set of operations. i=0; DO action 1 i = i + 1; WHILE (i < 10); C IS CURSOR FOR SELECT … DO UNTIL c.EOF examine one row of data FETCH(c) LOOP Useful for examining one row of data at a time from a SELECT statement.

12. (5) Input and Output In a DBMS, interact with a form or a data table. DBMS, use SQL SELECT variables … INSERT INTO … variables FORM or other devices, depends on DBMS and location. Form code can access form fields, Trigger code cannot access forms.

13. (6) Procedures and Functions Split code into meaningful pieces that are easier to debug. CREATE PROCEDURE AS … CREATE FUNCTION AS … To call a procedure or function: EXEC procedure … variable = function(…)

14. Data Trigger Events • Oracle additions: • Tables ALTER, CREATE, DROP • User LOGOFF, LOGON • Database SERVERERROR, SHUTDOWN, STARTUP INSERT DELETE UPDATE BEFORE AFTER

15. Example Trigger: Employee Salary • Goal: Keep a separate record of changes to salaries • Create a new audit-log table with columns for time, old salary, new salary, employee changed, and person making the change • Trigger: AFTER UPDATE of SALARY on EMPLOYEE • Basic code: • INSERT a row into the audit-log table with the tracking data

16. Statement v. Row Triggers UPDATE Employee SET Salary = Salary + 10000 WHERE EmployeeID=442 OR EmployeeID=558 SQL After Update On table Before Update On table Triggers for overall table Update Row 442 After Update Row 442 … other rows time Before Update Row 442 Triggers for each row

17. Data Trigger Example CREATE TRIGGER LogSalaryChanges AFTER UPDATE OF Salary ON Employee REFERENCING OLD ROW as oldrow NEW ROW AS newrow FOR EACH ROW INSERT INTO SalaryChanges (EmpID, ChangeDate, User, OldValue, NewValue) VALUES (newrow.EmployeeID, CURRENT_TIMESTAMP, CURRENT_USER, oldrow.Salary, newrow.Salary);

18. Canceling Data Changes in Triggers CREATE TRIGGER TestDeletePresident BEFORE DELETE ON Employee REFERENCING OLD ROW AS oldrow FOR EACH ROW WHEN (oldrow.Title = ‘President’) SIGNAL _CANNOT_DELETE_PRES;

19. Cascading Triggers Sale(SaleID, SaleDate, …) SaleItem(SaleID, ItemID, Quantity, …) AFTER INSERT UPDATE Inventory SET QOH = QOH – newrow.Quantity Inventory(ItemID, QOH, …) AFTER UPDATE WHEN newrow.QOH < newrow.Reorder INSERT {new order} INSERT {new OrderItem} Order(OrderID, OrderDate, …) OrderItem(OrderID, ItemID, Quantity, …)

20. Trigger Loop Employee(EID, Salary) AFTER UPDATE IF newrow.Salary > 100000 THEN Add Bonus END BonusPaid(EID, BonusDate, Amount) AFTER UPDATE Or INSERT IF newrow.Bonus > 50000 THEN Reduce Bonus Add Options END StockOptions(EID, OptionDate, Amount, SalaryAdj) AFTER UPDATE Or INSERT IF newrow.Amount > 100000 THEN Reduce Salary END

21. Transactions Some transactions result in multiple changes. These changes must all be completed successfully, or the group must fail. Protection for hardware and communication failures. example: bank customer transfers money from savings account to checking account. Decrease savings balance Increase checking balance Problem if one transaction and machine crashes. Possibly: give users a chance to reverse/undo a transaction. Performance gain by executing transactions as a block. Savings Accounts Inez: 5340.92 4340.92 $1000 Checking Accounts Inez: 1424.27 Transaction 1. Subtract $1000 from savings. (machine crashes) 2. Add $1000 to Checking. (money disappears)

22. Transaction Steps

23. Transaction Logs Table 1 Table 2 Update Table 1 … Update Table 2 … Log File Transaction changes are written to the log file so they can be restarted if the computer crashes during a transaction. Low-end DBMSs (e.g., Access) do not have log files.

24. Defining Transactions The computer needs to be told which changes must be grouped into a transaction. Turn on transaction processing. Signify a transaction start. Signify the end. Success: save all changes Failure: cancel all changes Must be set in module code Commit Rollback

25. SQL Transaction Code CREATE FUNCTION TransferMoney(Amount Currency, AccountFrom Number, AccountTo Number) RETURNS NUMBER curBalance Currency; BEGIN DECLARE HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; Return -2; -- flag for completion error END; STARTTRANSACTION; -- optional SELECT CurrentBalance INTO curBalance FROM Accounts WHERE (AccountID = AccountFrom); IF (curBalance < Amount) THEN RETURN -1; -- flag for insufficient funds END IF UPDATE Accounts SET CurrentBalance = CurrentBalance – Amount WHERE AccountID = AccountFrom; UPDATE Accounts SET CurrentBalance = CurrentBalance + Amount WHERE AccountID = AccountTo; COMMIT; RETURN 0; -- flag for success END;

26. SAVEPOINT SAVEPOINT StartOptional start commit Required elements Risky steps time Partial rollback START TRANSACTION; SELECT … UPDATE … SAVEPOINT StartOptional; UPDATE … UPDATE … If error THEN ROLLBACK TO SAVEPOINT StartOptional; END IF COMMIT;

27. Concurrent Access • Concurrent Access • Multiple users or processes changing the same data at the same time. • Final data will be wrong! • Force sequential • Locking • Delayed, batch updates • Two processes • Receive payment ($200) • Place new order ($150) • Initial balance $800 • Result should be $800 -200 + 150 = $750 • Interference result is either $600 or $950 Customers Receive Payment Place New Order ID Balance Jones $800 $600 $950 1) Read balance 800 2) Subtract pmt -200 4) Save new bal. 600 3) Read balance 800 5) Add order 150 6) Write balance 950

28. Concurrent Access Steps

29. Optimistic Locks • Assume that collisions are rare • Improved performance, fewer resources • Allow all code to read any data (no locks) • When code tries to write a new value • Check to see if the existing value is different from the one you were given earlier • If it is different, someone changed the database before you finished, so it is a collision--raise an error • Reread the value and try again

30. Optimistic Locks for Simple Update (1) Read the balance (2) Add the new order value (3) Write the new balance (4) Check for errors (5) If there are errors, go back to step (1).

31. Optimistic Locks with SQL CREATE FUNCTION ReceivePayment ( AccountID NUMBER, Amount Currency) RETURNS NUMBER oldAmount Currency; testEnd Boolean = FALSE; BEGIN DO UNTIL testEnd = TRUE BEGIN SELECT Amount INTO oldAmount WHERE AccountNumber = AccountID; … (slow code) UPDATE Accounts SET AccountBalance = AccountBalance - Amount WHERE AccountNumber = AccountID AND Amount = oldAmount; COMMIT; IF SQLCODE = 0 and nrows > 0 THEN testEnd = TRUE; RETURN 0; END IF -- keep a counter to avoid infinite loops END END

32. Optimistic UPDATE: WHERE var: oldAmount = SELECT … … UPDATE … WHERE ID=AccountID AND (Amount = oldAmount) To handle missing values, better to write: ((Amount = oldAmount) OR (Amount Is Null AND oldAmount Is Null) )

33. Minimize Concurrency Issues Avoid concurrency issues by keeping code fast and avoiding delays. Bad: Read Balance SELECT … Do slow stuff Add new Bal + Bal + New Write new total UPDATE … Better: UPDATE table SET Balance = Balance + NewValue WHERE … Delays can arise when you present screens of data to users. Particularly on Web forms. But, when possible, perform the computations within the DBMS not on the Web server forms. That means the data displayed to the user sometimes might be out of date, but concurrency within the DBMS is rare. Also: Avoid storing totals. Just write time-stamped change logs and compute the totals when they are needed.

34. Pessimistic Locks: Serialization • One answer to concurrent access is to prevent it. • When a transaction needs to alter data, it places a SERIALIZABLE lock on the data used, so no other transactions can even read the data until the first transaction is completed. SET TRANSACTION SERIALIZABLE, READ WRITE Customers Receive Payment Place New Order ID Balance Jones $800 $600 1) Read balance 800 2) Subtract pmt -200 4) Save new bal. 600 3) Read balance Receive error message that it is locked.

35. SQL Pessimistic Lock CREATE FUNCTION ReceivePayment ( AccountID NUMBER, Amount Currency) RETURNS NUMBER BEGIN DECLARE HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RETURN -2; END SET TRANSACTION SERIALIZABLE, READ WRITE; UPDATE Accounts SET AccountBalance = AccountBalance - Amount WHERE AccountNumber = AccountID; COMMIT; RETURN 0; END

36. Serialization Effects

37. Transaction to Transfer Money CREATE FUNCTION ReceivePayment ( AccountID NUMBER, Amount Currency) RETURNS NUMBER BEGIN DECLARE HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RETURN -2; END SET TRANSACTION SERIALIZABLE, READ WRITE; UPDATE Accounts SET AccountBalance = AccountBalance - Amount WHERE AccountNumber = AccountID; COMMIT; RETURN 0; END

38. Deadlock 1) Lock Data A 3) Wait for Data B • Deadlock • Two (or more) processes have placed locks on data and are waiting for the other’s data. • Many solutions • Random wait time • Global lock manager • Two-phase commit - messages Data A Data B 2) Lock Data B 4) Wait for Data A

39. Deadlock Sequence

40. Lock Manager

41. ACID Transactions • Atomicity: all changes succeed or fail together. • Consistency: all data remain internally consistent (when committed) and can be validated by application checks. • Isolation: The system gives each transaction the perception that it is running in isolation. There are no concurrent access issues. • Durability: When a transaction is committed, all changes are permanently saved even if there is a hardware or system failure.

42. SQL 99/2003 Isolation Levels (Advanced) • READ UNCOMMITTED • Problem: might read dirty data that is rolled back • Restriction: not allowed to save any data • READ COMMITTED • Problem: Second transaction might change or delete data • Restriction: Need optimistic concurrency handling • REPEATABLE READ • Problem: Phantom rows • SERIALIZABLE • Provides same level of control as if all transactions were run sequentially. • But, still might encounter locks and deadlocks

43. Phantom Rows(Advanced) SELECT SUM(QOH) FROM Inventory WHERE Price BETWEEN 10 and 20 Result: 5 + 4 + 8 = 17 Included in first query INSERT INTO Inventory VALUES (121, 7, 16) INSERT INTO Inventory VALUES (122, 3, 14) Additional rows will be included in the second query SELECT SUM(QOH) FROM Inventory WHERE Price BETWEEN 10 and 20 Result: 5 + 4 + 8 + 7 + 3 = 27

44. Generated Keys Customer Table CustomerID, Name, … Create an order for a new customer: (1) Create new key for CustomerID (2) INSERT row into Customer (3) Create key for new OrderID (4) INSERT row into Order Order Table OrderID, CustomerID, …

45. Methods to Generate Keys • The DBMS generates key values automatically whenever a row is inserted into a table. • Drawback: it is tricky to get the generated value to use it in a second table. • A separate key generator is called by a programmer to create a new key for a specified table. • Drawback: programmers have to write code to generate a key for every table and each row insertion. • Overall drawbacks: neither method is likely to be transportable. If you change the DBMS, you will have to rewrite the procedures to generate keys.

46. Auto-Generated Keys • Create an order for a new customer: • INSERT row into Customer • Get the key value that was generated • Verify the key value is correct. How? • INSERT row into Order Major problem: Step 2 requires that the DBMS return the key value that was most recently generated. How do you know it is the right value? What happens if two transactions generate keys at almost the same time on the same table?

47. Key-Generation Routine • Create an order for a new customer: • Generate a key for CustomerID • INSERT row into Customer • Generate a key for OrderID • INSERT row into Order This method ensures that unique keys are generated, and that you can use the keys in multiple tables because you know the value. But, none of it is automatic. It always requires procedures and sometimes data triggers.

48. Database Cursors • Purpose • Track through table or query one row at a time. • Data cursor is a pointer to active row. • Why? • Performance. • SQL cannot do everything. • Complex calculations. • Compare multiple rows. Year Sales 1998 104,321 1999 145,998 2000 276,004 2001 362,736 1998 104,321 1999 145,998 2000 276,004 2001 362,736

49. Database Cursor Program Structure DECLARE cursor1 CURSOR FOR SELECT AccountBalance FROM Customer; sumAccount, balance Currency; SQLSTATE Char(5); BEGIN sumAccount = 0; OPEN cursor1; WHILE (SQLSTATE = ‘00000’) BEGIN FETCH cursor1 INTO balance; IF (SQLSTATE = ‘00000’) THEN sumAccount = sumAccount + balance; END IF END CLOSE cursor1; -- display the sumAccount or do a calculation END

50. Cursor Positioning with FETCH DECLARE cursor2 SCROLL CURSOR FOR SELECT … OPEN cursor2; FETCH LAST FROM cursor2 INTO … Loop… FETCH PRIOR FROM cursor2 INTO … End loop CLOSE cursor2; FETCH positioning options: FETCH NEXT next row FETCH PRIOR prior row FETCH FIRST first row FETCH LAST last row FETCH ABSOLUTE 5 fifth row FETCH RELATIVE -3 back 3 rows