10-19-2009, DB Admin, Concurrency and RecoveryDatabase AdministrationDBA TasksManaging Database StructureConcurrency ControlRecord LockingShared versus ExclusiveUnit of Locking (Field, Row, Table, DB)Explicit versus ImplicitReleasing LocksWait versus NoWaitCommit/Rollback: What tables are updated ?Why commit & rollbackImplicit and Explicit Commit/RollbackAnimationsConcurrency OverviewConcurrency – DB exampleConcurrency – Error HandlingExclusive x Shared Lock (review)Unit of Locking - reviewLock TerminologySerializable TransactionsDeadlockOptimistic/Pessimistic LockingIf 2 transactions with no locks, what will happen ?Record lockingDeadlockAvoid DeadlockQUESTIONS ON LOCKSAssuming 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 ?Cont. (‘c002’ locked by another transaction)Locking in Oracle (v$lock)SummaryIn OracleWhen deadlock detected (possible action)Transactions - RecoveryPhysical Write to DiscPhysical & Logical UpdateRecovery - Power FailureRecovery – Test QuestionTriggers and LocksEnd of LectureCS 8630 Database Administration, Dr. Guimaraes10-19-2009, DB Admin, Concurrency and RecoveryClassWill Start Momentarily…CS8630 Database AdministrationDr. Mario GuimaraesCS 8630 Database Administration, Dr. GuimaraesDatabase 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 applicationsCS 8630 Database Administration, Dr. GuimaraesDBA 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 issues•Installing/uninstalling•Startup & Shutdown•Backup & Restore–What do you think is the DBA’s most common task ?CS 8630 Database Administration, Dr. GuimaraesManaging 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 documentationCS 8630 Database Administration, Dr. GuimaraesConcurrency 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 throughputCS 8630 Database Administration, Dr. GuimaraesRecord Locking•Shared versus Exclusive•Unit of Locking (Field, Row, Table, DB)•Explicit versus Implicit•Releasing Locks •Wait versus NoWaitCS 8630 Database Administration, Dr. GuimaraesShared 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 lock data.•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 accessedCS 8630 Database Administration, Dr. GuimaraesUnit 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.CS 8630 Database Administration, Dr. GuimaraesExplicit 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;CS 8630 Database Administration, Dr. GuimaraesReleasing 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.CS 8630 Database Administration, Dr. GuimaraesWait 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.CS 8630 Database Administration, Dr. GuimaraesCommit/Rollback: What tables are updated ?•Update customers set discnt = discnt + .01where city < > ‘Miami’;•Commit;•Update Products set price = price * 1.03;•Update Orders set dollars = dollars + .06;•Rollback;CS 8630 Database Administration, Dr. GuimaraesWhy commit & rollbackImplement 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 checkingto savings.ChkAccount = ChkAccount – 500SavAccount = SavAccount + 500CS 8630 Database Administration, Dr. GuimaraesImplicit 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)CS 8630 Database