DATABASE ADMINISTRATION AND CONCURRENCY

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.