chapter 7 database integrity and transactions based on g. post, dbms: designing & building...
TRANSCRIPT
Chapter 7
Database Integrity and Transactions
Based on G. Post, DBMS: Designing & Building Business Applications
University of ManitobaAsper School of Business
3500 DBMSBob Travica
Updated 2015
DDBB
SSYYSSTTEEMMSS
2 of 20
Outline
Code in DB system Triggers Transactions Concurrent Access Locks & Deadlocks ACID Transactions Keys creation DB cursor
DDBB
SSYYSSTTEEMMSS
3 of 20
DB System
Code as Part of DB System
Create code As triggers = procedures
that support biz processes; In forms and reports
(localized effects) Functions combined with
queries (embedded SQL) User-defined functions
Tables
Forms &Reports
Triggers
If (Click) ThenMsgBox . . .
End If
If inventory < Minimum Then CREATE POrderEnd If
C++ code:
Programs combined withSQL statements
if (. . .) { // embedded SQL SELECT …}
DDBB
SSYYSSTTEEMMSS
4 of 20
User-Defined Functions*
CREATE FUNCTION IncreaseSalary(EmpID INTEGER, Amt CURRENCY)
BEGININPUT EmpID, AmtIF (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.
• A program for increasing a salary for a certain amount (pseudo code).
DDBB
SSYYSSTTEEMMSS
5 of 20
Events and Triggers(Expanding the topic)
• Example on slide 4: BEFORE UPDATE on Salary column, run a trigger that checks if a salary ceiling is respected.
• 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
DDBB
SSYYSSTTEEMMSS
6 of 20
Table and Row Triggers
time
Before Updateon a table (all rows)
After Updateon the table
Before Updateof a row
After Updateof the row
Updatepoint
• SQL events support triggers at two points in time -- BEFORE and AFTER
Ta
ble
Ro
w
DDBB
SSYYSSTTEEMMSS
7 of 20
An After Row Trigger Example
CREATE TRIGGER LogSalaryChangesAFTER UPDATE OF Salary ON EmployeeREFERENCING OLD ROW As oldrow
NEW ROW As newrowFOR EACH ROW
INSERT INTO tblSalaryChanges (EmpID, ChangeDate, UserID, OldValue, NewValue)VALUES (newrow.EmployeeID, CURRENT_TIMESTAMP,CURRENT_USER, oldrow.Salary, newrow.Salary);
• 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.
DDBB
SSYYSSTTEEMMSS
8 of 20
Cascading Triggers
Sale(SaleID, SaleDate, …)
OrderItem(OrderID, ItemID, Quantity, …)
Order(OrderID, OrderDate, …)
Inventory(ItemID, QOH, …)
SaleItem(SaleID, ItemID, Quantity, …)
AFTER INSERTUPDATE InventorySET Inventory.QOH=Inventory.QOH –
newrow.Quantity
AFTER UPDATEWHEN Inventory.QOH < minQuantity
INSERT {new order}INSERT {new OrderItem}
• Increase scope of automation. Take care of dependencies.
DDBB
SSYYSSTTEEMMSS
9 of 20
Transactions
Definition: 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.
Transaction1. Subtract $1000 from Savings.
(Then, system crashes)2. Add $1000 to Checking.
(Money not added)
SavingsAccountJoe DoeBal.: 5340.92Subtract: 1000.00New Bal.: 4340.92
1.
$1000 subtracted from Savings but Balance in
Checking not increased!
Checking AccountJoe DoeBal.: 1424.27
2. Transfer Fails
Checking AccountJoe DoeBal.: 1424.27Add: 1000
x
DDBB
SSYYSSTTEEMMSS
10 of 20
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.
DDBB
SSYYSSTTEEMMSS
11 of 20
Designing Transactions: SAVEPOINT
START TRANSACTION;SELECT …UPDATE tblCustomer.recordSAVEPOINT StartOptional;UPDATE tblOrder.newrecordUPDATE tblInventory.QOHIF error THEN
ROLLBACK TO SAVEPOINT StartOptional;END IFCOMMIT;
time
startRun tasks
SAVEPOINTStartOptional
Risky tasksCOMMIT
Partial ROLLBACK
DDBB
SSYYSSTTEEMMSS
12 of 20
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.
$950 is the end balance instead of $750 (600+150). Customer at loss.
Table AccountPayable read and update
1) Read Balance 8002) Subtract pmt -200 3) Read balance 8004) Save new bal. 600 5) Add order 150 6) Write balance 950
Order transaction Payment transaction
DDBB
SSYYSSTTEEMMSS
13 of 20
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.
3) Order transaction locked out;
System’s message : “Table is locked”.
Order transaction, trying tointerfere with Payment at step 3
SET TRANSACTION SERIALIZABLE, READ WRITE
1) Read balance 8002) Subtract pmt -2003) Save new bal. 600
Payment transaction
DDBB
SSYYSSTTEEMMSS
14 of 20
Deadlock
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.
1) Lock tbl A2) Read tbl B
1) Lock tbl B2) Update tbl A
Transaction 1 (T1)
Transaction 2 (T2)
• 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.
Tbl A Tbl B
DDBB
SSYYSSTTEEMMSS
15 of 20
Solution 2 to Deadlock: Lock Manager
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
A closed lock-wait loop that blocks all processes.
Lock Manager (part of DBMS) monitors all processes. Temporarily disables some processes to clear the blockage.
DDBB
SSYYSSTTEEMMSS
16 of 20
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.
DDBB
SSYYSSTTEEMMSS
17 of 20
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).
DDBB
SSYYSSTTEEMMSS
18 of 20
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., CustomerIDIn 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.
DDBB
SSYYSSTTEEMMSS
19 of 20
Key Generator
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
Customer Table
CustomerID, Name, …
Order Table
OrderID, CustomerID, …
DDBB
SSYYSSTTEEMMSS
20 of 20
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 Sales Diff.1998 104,3211999 145,9982000 276,0042001 362,736
A cursor would read values of sales for the current and previous year, and calculate the
difference.