10-23-2008, DB Admin, Concurrency and Recovery

1. CS8630 Database Administration Dr. Mario Guimaraes 10-23-2008, DB Admin, Concurrency and Recovery • Class • Will • Start • Momentarily…

2. Database Administration • All large and small databases need database administration • Data administration refers to a function concerning all of an organization’s data assets • Database administration (DBA) refers to a person or office specific to a single database and its applications

3. DBA Tasks • Managing database structure • Controlling concurrent processing (ex: kill a user process) • Managing processing rights and responsibilities (Grant & Revoke) • Developing database security • Providing for database recovery • Managing the DBMS (ex: patching) • Maintaining the data repository • Resolving connectivity issuesackup • Installing/uninstalling softwareand Restore • Startup & Shutdown • Backup & Restore • What do you think is the DBA’s most common task ?

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. Record Locking • Shared versus Exclusive • Unit of Locking (Field, Row, Table, DB) • Explicit versus Implicit • Releasing Locks • Wait versus NoWait

7. Shared versus Exclusive • Shared Lock is a lock that keeps other transactions from writing to the locked data item. • They are also called Read Locks because when a SELECT statement is issued many DBMS will by default lock the data being accessed • In Oracle a normal SELECT statement will not lockdata. • In Oracle a SELECT statement that has the clause FOR UPDATE at the end will generate a shared lock in the rows being accessed. • Exclusive Lock keeps other transactions from viewing or writing to the locked data item. • They are also called Write Locks because when an UPDATE statement is issued many DBMS will by default lock the data being accessed. • In Oracle an UPDATE statement will generate an exclusive lock in the rows being accessed

8. Unit of Locking (Field, Row, Table, DB) • The bigger the DBMS the smaller the default unit of locking. • A personal DBMS typically lock the whole DB (which is simpler than locking smaller units) • An enterprise DBMS typically has the default unit of locking as the row • An Enterprise DBMS such as Oracle allows you to lock individual columns.

9. Explicit versus Implicit • Locks that we just talked about generated when an UPDATE or SELECT … FOR UPDATE; statement is issued are called explicit locks. • We may also lock data in an explicit way. For example in Oracle you may issue a command such as LOCK Table CUSTOMERS EXCLUSIVE;

10. Releasing Locks • There are two commands that release all locks. COMMIT and ROLLBACK. A possible place to insert a COMMIT in your DB application may bebefore user input in order to not slow down the other applications.

11. Wait versus NoWait • When a transaction tries to access a Data Object that is locked by another transaction. The transaction requesting the object will enter Wait. When the other transaction releases the lock, the requesting transaction will obtain the lock and automatically proceed. • This is what happens, unless there is a clause at the end of your UPDATE or SELECT statement that says NOWAIT. In this case, the transaction requesting the lock will abort (or jump to the error handling code if there is one). In Oracle’s PL/SQL, it will jump to the EXCEPTION handler.

12. Commit/Rollback: What tables are updated ? • Update customers set discnt = discnt + .01 where city < > ‘Miami’; • Commit; • Update Products set price = price * 1.03; • Update Orders set dollars = dollars + .06; • Rollback;

13. Why commit & rollback Implement the concept of a transaction. Command means end of transaction (releases all locks). Transaction: a set of operations where all has to be executed or none. Example: transfer $500 money from checking to savings. ChkAccount = ChkAccount – 500 SavAccount = SavAccount + 500

14. Implicit and Explicit Commit/Rollback • When there is a power failure, the system automatically executes a rollback (implicit rollback) • When you leave an SQL session through the proper way (exit command) the system executes a commit (implicit commit)

15. Animations • ADbC has three animations that illustrates possible scenarios and problems that may occur with locking.

16. Concurrency Overview

17. Concurrency – DB example

18. Concurrency – Error Handling

19. 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 (review)

20. A true multi-user DBMS offers row locking and table locking. Normally, the default is row locking. Personal DBMS normally offer less options and usually only lock by bigger units (table or lock the whole Database, for example). Unit of Locking - review

21. 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

22. 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 • Serializability is when to transactions are running concurrently but give the same results as if you executed them serially.

23. 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

24. 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

25. 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 ?

26. 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

27. TRANSACTION 1 Update Record 1 Update Record 2 TRANSACTION 2 Update Record 2 Update Record 1 Deadlock

28. Avoid Deadlock Select * from customers where cid = ‘c006’ for update no wait; This command can avoid deadlock, but it may abort transactions pre-maturally.

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

30. 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 ; }

31. 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; What happens when c002 is locked by another transaction ??

32. 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 ttp://www.ss64.com/orav/V$LOCK.html • A DBMS offers mechanisms for a DBA to know which process is holding locks, etc. In ORACLE much of this information is held in a system table called V$LOCK.

33. 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

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

35. 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’;

36. Transactions - Recovery

37. Physical Write to Disc

38. Physical & Logical Update

39. Recovery - Power Failure

40. Recovery – Test Question

41. Triggers and Locks

42. End of Lecture End Of Today’s Lecture.