Lesson-1-Fundamentals-of-Database.pptx

Fundamentals of Database Systems Lesson 1: Transactions and Concurrency Control Transaction __________ Marc Raphael S. Piñero Faculty College of Engineering & Information Sciences Unit Expected Outcome Define and explain...

Fundamentals of Database Systems Lesson 1: Transactions and Concurrency Control Transaction __________ Marc Raphael S. Piñero Faculty College of Engineering & Information Sciences Unit Expected Outcome Define and explain ACID properties. Use SQL to manage transactions (begin, commit, rollback). Interpret transaction results and analyze transaction logs. Implement locking, timestamping, and optimistic concurrency control methods. Compare and assess concurrency control methods for different scenarios. Part 1: Transaction  Journalizing Facilities DBMS must provide journalizing facilities to produce an audit trail of transactions and database changes. 2 basic journal or logs: - Transaction Log - Database Change Log  Transaction Log Contains a record of the essential data for each transaction that is processed against database. Data that are typically recorded for each transaction.  Database Change Log Contains before and after images of records that have been modified by transactions. 2 kinds of images of records: - Before Images - After Images Checkpoint Facility  Checkpoint Facility DBMS refuses to accept any new transactions. All transactions in progress are completed, and the journal files are brought up-to-date. System is in a quite state, and database and transaction logs are synchronized. Recovery Manager  Recovery Manager A module of a DBMS that restores the database to correct condition when a failure occurs and then resumes processing user requests. 2 Procedures: - Recovery - Restart Disk Mirroring  Disk Mirroring To be able to switch to an existing copy of a database, the data base must be mirrored. At least two copies of database must be kept and updated simultaneously. Restore and Return  Restore and Return Technique involves reprocessing the day’s transaction against the backup copy of the database or portion of the database being recovered. DBMS doesn’t need to create a database journal and no special restart procedures are required.  Maintaining Transaction Integrity A database is updated by processing transaction that result in changes to one or more database. If an error occurs, the database may be compromised. The transaction follows four well- accepted properties: Atomic, Consistent, Isolated, Durable.  Aborted Transaction Terminates abnormally. Cause of human error, input or invalid data, hardware failure, and deadlock.  Incorrect Data A more complex situation arises when database has been updated with incorrect, but valid data. Part 2: Concurrency Control  Controlling Concurrency Access Databases are shared resources. With concurrent processing involving updates, a database without concurrency control will be compromised, due to interference between users. Serializability  Serializability If one transaction were entirely processed before another transaction, no interference would occur. Procedures that process transactions so that the outcome is the same as this are called serializable. Locking Mechanisms  Locking Any data that are retrieved by a user for updating must be locked, or denied to other users, until the update is complete or aborted.  Locking Levels Database – The entire database is locked and becomes unavailable to other users. Table – The entire table containing a requested record is locked. Block – The physical storage block containing requested record is locked. Record – Only requested record is locked. Field – Only particular field in a requested record is locked. Thank you!