Outline

2. Outline • Code in DB system • Triggers • Transactions • Concurrent Access • Locks & Deadlocks • ACID Transactions • Keys creation • DB cursor

3. Create code As triggers = procedures that support biz processes; In forms and reports (localized effects) Functions combined with queries (embedded SQL) User-defined functions Code as Part of DB System DB System Tables Triggers If inventory < Minimum Then CREATE POrder End If Forms & Reports Programs combined with SQL statements C++ code: if (. . .) { // embedded SQL SELECT … } If (Click) Then MsgBox . . . End If

4. User-Defined Functions* • A program for increasing a salary for a certain amount (pseudo code). CREATE FUNCTION IncreaseSalary (EmpID INTEGER, Amt CURRENCY) BEGIN INPUT EmpID, Amt IF (Amt > 50000) THEN RETURN -1 -- error flag, data validation ELSE UPDATE Employee SET Salary = Salary + Amt WHERE EmployeeID = EmpID; PRINT New Salary END * Function is the code (program) that returns a result of processing.

5. Events and Triggers(Expanding the topic) • An event fires (initiates) a trigger (program, code): • Common events: • SQL-based on rows: INSERT, DELETE, UPDATE • SQL-based on tables: ALTER, CREATE, DROP • LOGOFF, LOGON – user’s action • SERVERERROR, SHUTDOWN, STARTUP – server’s action • Example on slide 4: BEFORE UPDATE on Salary column, run a trigger that checks if a salary ceiling is respected.

6. Before Update on a table (all rows) After Update on the table Update point time Before Update of a row After Update of the row Table and Row Triggers • SQL events support triggers at two points in time -- BEFORE and AFTER Table Row

7. An After Trigger Example • The trigger logs employee ID, date, user’s ID, old salary, and new salary into table SalaryChanges, after each update on the salary column in the Employee table. Useful for auditing. CREATE TRIGGER LogSalaryChanges AFTER UPDATE OF Salary ON Employee REFERENCING OLD ROW as oldrow NEW ROW AS newrow FOR EACH ROW INSERT INTO tblSalaryChanges (EmpID, ChangeDate, UserID, OldValue, NewValue) VALUES (newrow.EmployeeID, CURRENT_TIMESTAMP, CURRENT_USER, oldrow.Salary, newrow.Salary);

8. Sale(SaleID, SaleDate, …) SaleItem(SaleID, ItemID, Quantity, …) AFTER INSERT UPDATE Inventory SET Inventory.QOH=Inventory.QOH – newrow.Quantity Inventory(ItemID, QOH, …) AFTER UPDATE WHEN Inventory.QOH < minQuantity INSERT {new order} INSERT {new OrderItem} Order(OrderID, OrderDate, …) OrderItem(OrderID, ItemID, Quantity, …) Cascading Triggers • Increase scope of automation. Take care of dependencies.

9. Transaction is a sequence of processing tasks (a process) that succeed or fail altogether. Each task results in a database change but all must be completed for data change to be valid. Reason: to protect data accuracy against system failures. Example on right: 1. customer starts transferring money from savings account to checking account. 2. System crashes after subtracting amount from Savings and the amount is not added to Checking. Checking Account Joe Doe Bal.: 1424.27 Add: 1000 2. Transfer Fails Checking Account Joe Doe Bal.: 1424.27 Transactions Transaction 1. Subtract $1000 from Savings. (Then, system crashes) 2. Add $1000 to Checking. (Money not added) 1. SavingsAccount Joe Doe Bal.: 5340.92 Subtract: 1000.00 New Bal.: 4340.92 x $1000 subtracted from Savings but Balance in Checking not increased!

10. Transactions are programmed Mark a transaction start - START TRANSACTION Determine a point of temporary saving of data changes Mark a transaction end - COMMIT end-result of processing to database, permanent save. Designing Transactions

11. Designing Transactions: SAVEPOINT SAVEPOINT StartOptional start COMMIT Run tasks Risky tasks time Partial ROLLBACK START TRANSACTION; SELECT … UPDATE tblCustomer.record SAVEPOINT StartOptional; UPDATE tblOrder.newrecord UPDATE tblInventory.QOH IF error THEN ROLLBACK TO SAVEPOINT StartOptional; END IF COMMIT;

12. Concurrent Access Multiple transactions competing for the same data at the same time. If Order process reads Balance before Payment process ends, the end balance will be incorrect. Concurrent Access Table AccountPayable read and update Payment transaction Order transaction 1) Read Balance 800 2) Subtract pmt -200 3) Read balance800 4) Save new bal. 600 5) Add order 150 6) Write balance950 $950 is the end balance instead of $750 (600+150). Customer at loss.

13. Pessimistic Locks (Serialization) • One answer to problems of concurrent access is to disable it by forcing transactions into a sequence one after another. • A transaction places a SERIALIZABLE lock on data so that no other transaction can access it before the first transaction is completed. SET TRANSACTION SERIALIZABLE, READ WRITE Order transaction, trying to interfere with Payment at step 3 Payment transaction 1) Read balance 800 2) Subtract pmt -200 3) Save new bal. 600 3) Order transaction locked out; System’s message : “Table is locked”.

14. Deadlock = problem with serialized locks: When different transactions use multiple tables concurrently. T1 locks tbl A and requests access to tbl B, while tbl B is locked by T2 that, in turn, requests access to T1. Neither transaction can be completed. Transaction 1 (T1) 1) Lock tbl A 2) Read tbl B Transaction 2 (T2) 1) Lock tbl B 2) Update tbl A Deadlock Tbl A Tbl B • Transactions lock out each other, create “deadly embrace” (another name for deadlock). • Solution 1 to deadlock: Each transaction waits random time, tries again, releases locks if unsuccessful, and runs itself all over again.

15. Solution 2 to Deadlock: Lock Manager A closed lock-wait loop that blocks all processes. Data A Data B Data C Data D Data E Process1 Lock Wait Process2* Wait Lock Process3 Lock Process4 Lock Wait Process5 Wait Process6 Wait Lock Process7 Wait Wait Lock Manager (part of DBMS) monitors all processes. Temporarily disables some processes to clear the blockage.

16. Optimistic Locks • Opposite to serialization lock (pessimistic lock); a solution against deadlocks. • Logic: • Assuming that collisions are rare • Record state of DB at READ time of any transaction (no locks) • When a transaction tries to write a new value, system reads data again (current data), and compares it with record at start. • If there is a difference, system sends error message and calls the transaction to execute itself again.

17. ACID Transactions • Atomicity: all changes succeed or fail together. • Consistency: all data remain internally consistent (when new data is committed, transactions fully executed) and can be validated (e.g., referential integrity). • Isolation: system gives each transaction the perception that it is running in isolation. There are no concurrent access problems (locks do the job). • Durability: When a transaction is committed, all changes are permanently saved even if there is a hardware or system failure (use a master log file for new data).

18. Methods to Generate Keys • The DBMS can generate key values automatically whenever a row is inserted into a table (surrogate key). Drawback: concurrent access to DB system can mix up keys that are generated at the same time (e.g., CustomerID In Customer table, which needs to be reused in the Order table). • A separate key generator is called by a programmer to create a new key for a specified table. Prevents mix-ups but requires that programmers write code to generate a key for every table and each row insertion.

19. Key Generator 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, …

20. Cursor = A type of variable (memory space) that holds entire records. A relic from old DB systems (Declare Cursor… Close Cursor) – no relationship with screen cursor (user interface). Purposes: Complex calculations on rows Comparisons between rows Database Cursor Year Sales Diff. 1998 104,321 1999 145,998 2000 276,004 2001 362,736 A cursor would read values of sales for the current and previous year, and calculate the difference.