Multi-user Database Processing

1. Multi-user Database Processing Architectures Transactions Security Administration

2. Multi-user Processing Architectures • Teleprocessing Systems • Client-Server Systems • File Sharing Systems • Distributed Database Systems

3. Teleprocessing Systems • Central CPU & Applications • Central DBMS • Central Database Communication lines

4. Client-Server Systems • Distributed CPUs & Applications • Central DBMS • Central DB

5. File Sharing Systems • Distributed CPUs & Applications • Distributed DBMS • Central DB

6. Distributed Database Systems • Distributed CPUs & Applications • Distributed DBMS • Distributed DB

7. Comparing Architectures C = centralized, D = distributed

8. Types of Distributed Databases Partition Replication

9. Distributed Database Continuum Simplicity Security Affordability Independence Flexibility Availability

10. Distributed DBs: Your turn • An example of distributed database! • Partitioned? • Replicated? • 3 advantages? • Independence • Flexibility • Availability • 3 disadvantages? • Complexity • Insecurity • Cost

11. Database Reliability Alice reads ACCOUNT Alice adds $50 Bob reads ACCOUNT Alice writes ACCOUNT Bob takes $100 Bob writes ACCOUNT • Concurrency control • Hide other users • Database recovery • Hide failures Change CUSTOMER data Change SALESPERSON data Insert ORDER data

12. Database Transactions • Transaction = logical unit of work • Series of actions defined such that either all of them or none of them are performed • Should always transform the database from one consistent state to another Start Transaction Change CUSTOMER data Change SALESPERSON data Insert ORDER data If no errors then Commit Transaction Else Rollback Transaction End If

13. BEGIN TRANSACTION COMMIT ROLLBACK Transaction Outcomes • If successful, transaction is committed & database reaches a new state • If unsuccessful, transaction is aborted & database must be restored to previous state

14. Problems Caused by Concurrency • Lost update • Overwrite another user’s update • Uncommitted dependency • When user sees intermediate step of another transaction before it is committed • Inconsistent analysis • Read partial results of incomplete update transaction

15. Concurrency Control • Goal: Serializability • concurrent transactions produce same result as serial execution • Technique: Locking Alice reads ACCOUNT Alice adds $50 Alice writes ACCOUNT Bob reads ACCOUNT Bob takes $100 Bob writes ACCOUNT

16. Two-Phase Locking • Transaction can obtain locks as needed • Once first lock is released, no other locks may be obtained • Guarantees serializability 1. Lock item 100 for A 2. Read item 100 for A 3. Lock item 100 for B; cannot, so place B in wait state 4. Set item count to 5 for A 5. Write item 100 for A 6. Release A's lock on item 100 7. Place lock on item 100 for B 8. Read item 100 for B 9. Set item count to 2 for B 10. Write item 100 for B 11. Release B's lock on item 100 A1. Lock item 100 A2. Read item 100 A3. Reduce count by 5 A4. Write item 100 B1. Lock item 100 B2. Read item 100 B3. Reduce count by 3 B4. Write item 100

17. Dealing with Deadlock 1. Lock paper for user A 2. Lock pencils for user B 3. Process A's requests; write paper record 4. Process B's requests; write pencil record 5. Put A in wait state for pencils 6. Put B in wait state for paper [Deadlock…] • Two-phase locking can lead to deadlock • Prevention • All or nothing locking • Detection and Recovery • DBMS detects deadlock and aborts one of the transactions

18. Recovery via Reprocessing • Return to known point and reprocess the transactions • Requires database save and log of transactions • Problems: • Inefficiency • Asynchrony may lead to different end results

19. Recovery via Roll-back • Requires log of changes made to database • Undo changes made by erroneous or incomplete transactions, restart valid transactions

20. Recovery via Roll-forward • Requires database save and log of changes made to database • Restore database save and apply changes from log • Frequent checkpoints decrease delay

21. Security • Set by DBA, enforced by DBMS • Authorization Rules = table in which each record keeps processing permissions via the following fields: • Subject - user, group, application, computer... • Object - database, relation, row, column, view, transaction…(Granularity = size of objects) • Action - read, write, execute, create, grant... • Authorization constraint - limit...

22. Actual DBMS security • The capabilities of the Authorization Rules model are only partially implemented • Subject-oriented security • For each subject, binary Action/Object table • Object-oriented security • For each object, binary Action/Subject table • Constraints via programmable exits • Triggers to constraint-checking programs

23. DBA Responsibilities • Manage the DB structure • Contribute to DB & application development • Maintain documentation to facilitate changes • Manage data activity • Data standards & documentation • Establish and maintain the DB data dictionary • Security • Backup & recovery • Manage the DBMS software