1 / 29

DATABASE ADMINISTRATION AND CONCURRENCY

DATABASE ADMINISTRATION AND CONCURRENCY. Database Administration. Data administration refers to a function concerning all of an organization’s data assets Database administration (DBA) refers to a database and its applications. DBA Tasks. Backup and Restore Startup & Shutdown

duer
Download Presentation

DATABASE ADMINISTRATION AND CONCURRENCY

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. DATABASE ADMINISTRATION AND CONCURRENCY

  2. Database Administration • Data administration refers to a function concerning all of an organization’s data assets • Database administration (DBA) refers to a database and its applications

  3. DBA Tasks • Backup and Restore • Startup & Shutdown • Managing database structure • Controlling concurrent processing • Managing processing rights and responsibilities (Grant & Revoke) • Developing database security • Providing for database recovery • Managing the DBMS • Maintaining the data repository • Installing/uninstalling software • Resolving connectivity issues

  4. Managing Database Structure • DBA’s tasks: • Participate in database and application development • Assist in requirements stage and data model creation • Play an active role in database design and creation • Facilitate changes to database structure • Seek community-wide solutions • Assess impact on all users • Provide configuration control forum • Be prepared for problems after changes are made • Maintain documentation

  5. Concurrency Control • Concurrency control ensures that one user’s work does not inappropriately influence another user’s work • No single concurrency control technique is ideal for all circumstances • Trade-offs need to be made between level of protection and throughput

  6. Concurrency - Overview • Shared versus Exclusive • Unit of Locking (Field, Row, Table, DB) • Explicit versus Implicit • Releasing Locks • Wait versus NoWait Animations http://coffee.kennesaw.edu/sql_java/concurrency.html

  7. Atomic Transactions • A transaction, or logical unit of work (LUW), is a series of actions taken against the database that occurs as an atomic unit • Either all actions in a transaction occur or none of them do

  8. Concurrent Transaction • Concurrent transactions refer to two or more transactions that appear to users as they are being processed against a database at the same time • In reality, CPU can execute only one instruction at a time • Transactions are interleaved meaning that the operating system quickly switches CPU services among tasks so that some portion of each of them is carried out in a given interval • Concurrency problems: lost update and inconsistent reads

  9. Resource Locking • Resource locking prevents multiple applications from obtaining copies of the same record when the record is about to be changed

  10. Lock Terminology • Implicit locks are locks placed by the DBMS • Explicit locks are issued by the application program • Lock granularity refers to size of a locked resource • Rows, page, table, and database level • Large granularity is easy to manage but frequently causes conflicts • Types of lock • An exclusive lock prohibits other users from reading the locked resource • A shared lock allows other users to read the locked resource, but they cannot update it

  11. Serializable Transactions • Serializable transactions refer to two transactions that run concurrently and generate results that are consistent with the results that would have occurred if they had run separately • Two-phased locking is one of the techniques used to achieve serializability

  12. Two-phased Locking • Two-phased locking • Transactions are allowed to obtain locks as necessary (growing phase) • Once the first lock is released (shrinking phase), no other lock can be obtained • A special case of two-phased locking • Locks are obtained throughout the transaction • No lock is released until the COMMIT or ROLLBACK command is issued • This strategy is more restrictive but easier to implement than two-phase locking

  13. Deadlock • Deadlock, or the deadly embrace, occurs when 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

  14. Optimistic/Pessimistic Locking • Optimistic locking assumes that no transaction conflict will occur • DBMS processes a transaction; checks whether conflict occurred • If not, the transaction is finished • If so, the transaction is repeated until there is no conflict • Pessimistic locking assumes that conflict will occur • Locks are issued before transaction is processed, and then the locks are released • Optimistic locking is preferred for the Internet and for many intranet applications

  15. Record Locking • Shared versus Exclusive • Unit of Locking (Field, Row, Table, DB) • Explicit versus Implicit • Releasing Locks • Wait versus NoWait Animations http://coffee.kennesaw.edu/sql_java/concurrency.html

  16. When you issue an exclusive lock on a record, nobody else can read or update that record. When you issue a shared lock on a record, nobody else can update that record Exclusive x Shared Lock

  17. A true multi-user DBMS offers row locking and table locking. Normally, the default is row locking. Personal DBMS normally lock by table or lock the whole Database (like MS-Access) Row or Field Locking x Table or File Locking

  18. TRANSACTION 1 Update accounts Set bal = bal – 500 Where aid = ‘A1’; Update accounts Set bal = bal + 500 Where aid = ‘A2’; TRANSACTION 2 Select bal into :bal1 from accounts where aid = ‘A1’; Select bal into :bal2 from accounts where aid = ‘A2’; If (:bal1 + :bal2 < 500) deny credit If 2 transactions with no locks, what will happen ?

  19. Exclusive Lock In oracle, like in many other DBMSs, when you update, you lock the rows you are accessing in exclusive mode Shared Lock In oracle, when you issue a select … for update, you lock the rows you access in shared mode. In many DBMSs’ a simple select may issue the shared lock. Record locking

  20. TRANSACTION 1 Update Record 1 Update Record 2 TRANSACTION 2 Update Record 2 Update Record 1 Deadlock

  21. One way to avoid deadlock iswith the no wait clause Select * from customers where cid = ‘c006’ for update no wait;

  22. QUESTIONS ON LOCKS • WHAT TWO COMMANDS IN ORACLE RELEASES LOCKS RECORDS ? • WHEN WE EXIT FROM INTERACTIVE SQL, DOES COMMIT AUTOMATICALLY OCCURS OR A ROLLBACK ?

  23. Assuming that customer c002 (cap database) was already locked (exclusive lock) by another transaction, what is going to happen ? In other words, which tables will be updated in the example below ? // BEGIN TRANSACTION EXEC SQL WHENEVER SQLERROR DO sql_error () EXEC SQL UPDATE AGENTS … EXEC SQL UPDATE ORDERS … EXEC SELECT name, discnt INTO :cust_name, :cust_discnt FROM CUSTOMERS where cid = ‘c002’ FOR UPDATE NO WAIT; UPDATE PRODUCTS … Commit; // END TRANSACTION Sql_error () { exec sql rollback; exit ; }

  24. Cont. (‘c002’ locked by another transaction) • BEGIN TRANSACTION • BEGIN • UPDATE AGENTS …. • UPDATE ORDERS …. • SELECT name, discnt INTO :cust_name, :cust_discnt FROM CUSTOMERS where cid = ‘c002’ FOR UPDATE NO WAIT; • UPDATE PRODUCTS …. • EXCEPTION • WHEN OTHERS • DBMS_OUTPUT.PUT_LINE (‘Resource is locked by another user’); • COMMIT; • END;

  25. Locking in Oracle (v$lock) V$LOCK’s main attributes sid (identifying the session holding or • aquiring the lock), type, and the lmode/request pair. • http://www.adp-gmbh.ch/ora/concepts/lock.html • http://www.ss64.com/orav/V$LOCK.html

  26. Summary • Exclusive or Shared • Unit (Database, Table, Row, Field) • Search Data Dictionary to see who has the locks, how long, etc. • Delete their session if necessary

  27. In Oracle • Select ???? For Update No wait;

  28. When deadlock detected (possible action) • Managing Sessions • SELECT username, sid, serial#, status FROM v$session WHERE username = ‘JOHN’; USERNAME SID SERIAL # STATUS JOHN 9 3 5 ALTER SYSTEM KILL SESSION ‘9,3’;

  29. Recovery • Most of the time, it is automatically done by DBMS • See coffee for animations on LRU & Recovery.

More Related