1 / 27

IT420: Database Management and Organization

Learn about database transactions, encryption, concurrency control, and security measures. Understand optimistic vs pessimistic locking and ACID transactions. Explore database processing examples and techniques.

Download Presentation

IT420: Database Management and Organization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. IT420: Database Management and Organization Transactions 31 March 2006 Adina Crăiniceanu www.cs.usna.edu/~adina

  2. Project 2 • To insert an encrypted password into the database: • PHP: Find encrypted value • MySQL: Insert into Users table Kroenke, Database Processing

  3. Goals • Concurrency control Kroenke, Database Processing

  4. DBA Tasks • Managing database structure • Controlling concurrent processing • Managing processing rights and responsibilities • Developing database security • Providing for database recovery • Managing the DBMS • Maintaining the data repository Kroenke, Database Processing

  5. Atomic Transactions • Series of actions taken against the database that occur as an atomic unit • Either all actions in a transaction occur - COMMIT • Or none of them do - ABORT Kroenke, Database Processing

  6. Class Exercise • Transaction steps • Possible schedule • Possible problems • T1: Transfer money from savings to checking • T2: Add interest for savings account Kroenke, Database Processing

  7. Resource Locking • Locking: prevents multiple applications from obtaining copies of the same resource when the resource is about to be changed Kroenke, Database Processing

  8. Lock Terminology • Implicit locks - placed by the DBMS • Explicit locks - issued by the application program • Lock granularity - size of a locked resource • Rows, page, table, and database level • Types of lock • Exclusive lock (X)- prohibits other users from reading the locked resource • Shared lock (S) - allows other users to read the locked resource, but they cannot update it Kroenke, Database Processing

  9. Explicit Locks Lock type? Kroenke, Database Processing

  10. Serializable Transactions • Serializable transactions: • Run concurrently • Results like when they run separately • Strict two-phase locking – locking technique to achieve serializability Kroenke, Database Processing

  11. Strict Two-Phase Locking • Strict two-phase locking • Locks are obtained throughout the transaction • All locks are released at the end of transaction (COMMIT or ROLLBACK) Kroenke, Database Processing

  12. Not 2PL X(Sa) R(Sa) W(Sa) Rel(Sa) X(Ch) R(Ch) W(Ch) Rel(Ch) Strict 2PL Example • Strict 2PL • X(Sa) • R(Sa) • W(Sa) • X(Ch) • R(Ch) • W(Ch) • Rel(Ch) • Rel(Sa) Kroenke, Database Processing

  13. Deadlock Kroenke, Database Processing

  14. Deadlock • Deadlock: two transactions are each waiting on a resource that the other transaction holds • Preventing deadlock • Allow users to issue all lock requests at one time • Require all application programs to lock resources in the same order • Breaking deadlock • Almost every DBMS has algorithms for detecting deadlock • When deadlock occurs, DBMS aborts one of the transactions and rollbacks partially completed work Kroenke, Database Processing

  15. Optimistic versus PessimisticLocking • Optimistic locking assumes that no transaction conflict will occur: • DBMS processes a transaction; checks whether conflict occurred: • If not, the transaction is finished • If yes, the transaction is repeated until there is no conflict • Pessimistic locking assumes that conflict will occur: • Locks are issued before a transaction is processed, and then the locks are released Kroenke, Database Processing

  16. Optimistic Locking Kroenke, Database Processing

  17. Pessimistic Locking Kroenke, Database Processing

  18. Declaring Lock Characteristics • Most application programs do not explicitly declare locks due to its complication • Mark transaction boundaries and declare locking behavior they want the DBMS to use • Transaction boundary markers: BEGIN, COMMIT, and ROLLBACK TRANSACTION • Advantage • If the locking behavior needs to be changed, only the lock declaration need be changed, not the application program Kroenke, Database Processing

  19. Marking Transaction Boundaries Kroenke, Database Processing

  20. ACID Transactions • Transaction properties: • Atomic - all or nothing • Consistent • Isolated • Durable – changes made by commited transactions are permanent Kroenke, Database Processing

  21. Consistency • Consistency means either statement level or transaction level consistency • Statement level consistency: each statement independently processes rows consistently • Transaction level consistency: all rows impacted by either of the SQL statements are protected from changes during the entire transaction • With transaction level consistency, a transaction may not see its own changes Kroenke, Database Processing

  22. Statement Level Consistency UPDATE CUSTOMER SET AreaCode = ‘410’ WHERE ZipCode = ‘21218’ • All qualifying rows updated • No concurrent updates allowed Kroenke, Database Processing

  23. Transaction Level Consistency Start transaction UPDATE CUSTOMER SET AreaCode = ‘425’ WHERE ZipCode = ‘21666’ ….other transaction work UPDATE CUSTOMER SET Discount = 0.25 WHERE AreaCode = ‘425’ End Transaction The second Update might not see the changes it made on the first Update Kroenke, Database Processing

  24. ACID Transactions • Atomic • Consistent • Isolated • Durable Kroenke, Database Processing

  25. Inconsistent-Read Problem • Dirty reads – read uncommitted data • T1: R(A), W(A), R(B), W(B), Abort • T2: R(A), W(A), Commit • Unrepeatable reads • T1: R(A),R(A), W(A), Commit • T2: R(A), W(A), Commit • Phantom reads • Re-read data and find new rows Kroenke, Database Processing

  26. Isolation • SQL-92 defines four transaction isolation levels: • Read uncommitted • Read committed • Repeatable read • Serializable Kroenke, Database Processing

  27. Transaction Isolation Level Kroenke, Database Processing

More Related