University of Manitoba Asper School of Business 3500
University of Manitoba Asper School of Business 3500 DBMS Bob Travica Chapter 7 Database Transactions, Integrity & Accuracy Based on G. Post, DBMS: Designing & Building Business Applications Updated 2020
D B S Y S T E M S Outline ² Concepts of Database Transaction, Integrity & Accuracy ² Code in DB system ² Triggers ² Transactions ² Concurrent Access ² Locks & Deadlocks ² ACID Transactions ² Keys creation ² DB cursor 2 of 22
D B Transaction & Integrity Concepts ² Database transaction is a set of processing steps that succeed or fail altogether. A database transaction behaves like a whole. S Y S T E M S ² Differentiate from business transaction – one instance of a business operation (a purchase, a sale, an inventory input…); refer to TPS. ² Workings of a DBS is usually in the form of database transactions. ² Database Integrity is about data consistency. Inside a DBS, integrated data are “correct” data since a particular piece of data is the same across the system. But it might be wrong (inaccurate), that is, not corresponding to reality outside the system. 3 of 22
D B S Y S T E M S Transaction & Integrity Concepts ² Database integrity is sometimes mixed up with the concept of database accuracy (data corresponding to/reflecting reality); keep this in mind when reading the textbook. ² Better: think of data integrity AND data accuracy as properties of a quality database. ² Example: A $100 million figure is stored and referred to consistently in a DB system (integrity), but the real income is $99. 9999 million (accuracy, the lack of it). ² Properly designed and executed database transactions ensure data integrity and accuracy. 4 of 22
D B S Y S T E M S Code as Part of DB System ² Code in DB system: 1. As triggers = procedures pinned to table-related events; 2. In forms and reports (localized effects) 3. Functions combined with embedded SQL DB System Tables 3. Functions C++ code: if (. . . ) { // embedded SQL SELECT … } 1. Trigger AFTER table UPDATE If inventory < Minimum Then CREATE POrder End If Forms 2. In form, report If (Click) Then Msg. Box. End If 5 of 22
D B S Y S T E M S User-Defined Function with SQL* • Function for increasing a salary for a certain amount (pseudo code). CREATE FUNCTION Increase. Salary (Emp. ID INTEGER, Amt CURRENCY) BEGIN INPUT Emp. ID, Amt Procedural IF (Amt > 5000) language THEN RETURN -1 -- error flag, data validation code ELSE SQL UPDATE Employee SET Salary = Salary + Amt code WHERE Employee. ID = Emp. ID; PRINT Salary -- new values END * A function is the code (piece of application software) that returns a result of processing. 6 of 22
D B S Y S T E M S Events and Triggers (Extending the topic from pre-midterm) • An event starts (initiates, fires, causes) a trigger* (programming code): • Events in a DBS: • SQL-based on rows: INSERT, DELETE, UPDATE • SQL-based on tables: ALTER, CREATE, DROP • Based on user’s action: LOGOFF, LOGON • Based on server action: SERVERERROR, SHUTDOWN, STARTUP • Caution! The word "Trigger" may be confusing compared to the ordinary use of it (trigger comes first as a cause of an event, such as a gun trigger causes the event of bullet firing). 7 of 22
D B Table • SQL events support triggers at two points in time - BEFORE and AFTER data change Row S Y S T E M S Table and Row Triggers Before Update on a table (all rows) Before Update of a row Update point After Update on the table After Update of the row time 8 of 22
D B S Y S T E M S An After Row Trigger Example • The trigger logs employee ID, date, user’s ID, old salary, and new salary into table Salary. Changes, after each update on the salary column in the Employee table. Useful for auditing. CREATE TRIGGER Log. Salary. Changes AFTER UPDATE OF Salary ON Employee REFERENCING OLD ROW As oldrow NEW ROW As newrow FOR EACH ROW INSERT INTO tbl. Salary. Changes (Emp. ID, Change. Date, User. ID, Old. Value, New. Value) VALUES (newrow. Employee. ID, CURRENT_TIMESTAMP, CURRENT_USER, oldrow. Salary, newrow. Salary); 9 of 22
D B Cascading Triggers • Increase scope of automation. Take care of dependencies. Sale(Sale. ID, Sale. Date, …) Sale S Y S T E M S Sale. Item(Sale. ID, Item. ID, Quantity, …) Sale. Item Inventory(Item. ID, QOH, …) Inventory AFTER INSERT UPDATE Inventory SET Inventory. QOH=Inventory. QOH – newrow. Quantity; AFTER UPDATE WHEN Inventory. QOH < min. Quantity INSERT {new Purchase. Order} INSERT {new Purchase. Order -Item}; …) Purchase. Order(POrder. ID, Order. Date, Order Purchase. Order-Item(POrder. ID, Item. ID, Quantity, …) Purchase. Order-Item 10 of 22
D B S Y S T E M S Transactions ² Definition: Transaction is a sequence of processing tasks (a process) that succeed or fail altogether. ² Functions, procedures and triggers (preceding slides) can be declared a transaction. Transaction 1. Subtract $1000 from Savings. (Then, system crashes) 2. Add $1000 to Checking. (Money not added) ² Reason: to protect database accuracy 1. Savings. Account Checking against system failures. Joe Doe Account ² 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. Bal. : 5340. 92 Joe Doe Subtract: 1000. 00 Bal. : 1424. 27 New Bal. : 4340. 92 Add: 1000 2. Transfer Fails $1000 subtracted from Savings but Checking Balance not increased! Inaccurate data! Customer lost $1000. x Checking Account Joe Doe Bal. : 1424. 27 11 of 22
D B S Y S T E M S Designing Transactions ² 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. 12 of 22
D B S Y S T E M S Designing Transactions: Error Correction Full ROLLBACK Start SAVEPOINT Start. Optional Run simple steps COMMIT (Save) Riskier steps Partial ROLLBACK processing sequence START TRANSACTION; SELECT * FROM tbl_Customer WHERE Cust. ID=… UPDATE tbl_Customer. Address SAVEPOINT Start. Optional UPDATE tbl_Order. newrecord UPDATE tbl_Inventory. QOH IF error THEN ROLLBACK TO SAVEPOINT Start. Optional END IF COMMIT; 13 of 22
D B S Y S T E M S Concurrent Access ² 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 (inflated). Table Customer Account read and update Payment transaction 1) Read Balance 800 2) Subtract pmt -200 3) Read old balance=800 4) Save new bal. 600 5) Add order 150 6) Write balance 950 Order transaction Integrity violation Accuracy violation $950 is the end balance instead of $750 (600+150). Customer at loss. 14 of 22
D B S Y S T E M S Pessimistic Locks (Serialization) ² One answer to problems of concurrent access is to force transactions to run in 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 Payment transaction locks the table 1) Read balance 2) Subtract pmt 3) Save new bal. 800 -200 600 Order transaction, trying to interfere with Payment at step 3 is locked out. 3) System’s message : “Table is locked, try later”. The integrity problem avoided and the transaction will work with accurate data. 15 of 22
D B S Y S T E M S Problem: Deadlock! Transaction 1 (T 1) ² Deadlock = problem with serialized locks when different transactions use multiple tables 1) Lock tbl A concurrently. T 1 locks tbl A and requests 2) Read tbl B access to tbl B, while T 2 locks tbl B and 1 3 requests access to tbl A. Neither transaction can be completed; T 1 & T 2 lock out each other. Tbl A Tbl B • Result: Deadlock, a “deadly embrace” of transactions. • Solutions to deadlock: 1) Each transaction waits random time, tries again, releases locks if unsuccessful, and runs itself all over again. 2) Automated lock manager (next). 2 4 1) Lock tbl B 2) Update tbl A Transaction 2 (T 2) 16 of 22
D B Deadlock Loop Sometimes a lock manager must intervene: A closed lock-wait loop that blocks many transactions. Table A S Y S T E M S Transaction 1 Table B Table C Lock Table D Table E Wait (needs tbl_B, tbl_D) Transaction 2* (needs tbl_D, tbl_A) Lock Wait Transaction 3 Transaction 4 (needs tbl_A, tbl_E) Lock Wait Transaction 6 (needs tbl_E, tbl_C) Wait Lock Transaction 7 (needs tbl_C, tbl_D) Lock Wait Lock Manager (part of DBMS) monitors all transactions and disables some temporarily to clear the deadlock. 17 of 22
D B S Y S T E M S Optimistic Locks ² Opposite to pessimistic lock (serialized transactions); there is no real locking but preventing processing of data that are not current. ² Logic: ª Assuming that collisions are rare ª Read transactions are frequent ª Record state of accessed data at Read time (no locks) ª If any transaction tries to write a new value, system reads data again (current data), and compares it with the initial read. ª If current read <> initial read, system reports error and executes the transaction again, so it works with the current data. 18 of 22
D B S Y S T E M S ACID Transactions ² The ACID standard ensures data integrity and accuracy (in part) in a relational database. Differentiates DBS on quality and the relational DB tech. from non-relational tech. ² Atomicity: all changes succeed or fail together (as “an atom”), i. e. , DBS supports transactions definition and execution. ² Consistency: all data remain internally consistent (when transactions are executed) and can be validated, i. e. , referential integrity supported). ² Isolation: each transaction is processed separately, i. e. , DBS supports lock management. ² Durability: When a transaction is committed, data changes are permanently saved (using log files and other techniques), i. e. , DBS has a recovery capability. 19 of 22
D B S Y S T E M S Methods to Generate Keys 1. 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. , Customer. ID In Customer table, which needs to be reused in the Order table). 2. 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. 20 of 22
D B S Y S T E M S Key Generator • Appropriate key creation also ensures database integrity and accuracy. Customer Table Create an order for a new customer: (1) Create new key for Customer. ID (2) INSERT row into Customer (3) Create key for new Order. ID (4) INSERT row into Order Customer. ID, Name, … Order Table Order. ID, Customer. ID, … 21 of 22
D B S Y S T E M S Database Cursor ² 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 Year 1998 1999 2000 2001 Sales Diff. 104, 321 145, 998 276, 004 362, 736 A cursor would read values of sales for the current and previous year, and calculate the difference. 22 of 22
- Slides: 22