10 23 2008 db admin concurrency and recovery
This presentation is the property of its rightful owner.
Sponsored Links
1 / 42

10-23-2008, DB Admin, Concurrency and Recovery PowerPoint PPT Presentation


  • 80 Views
  • Uploaded on
  • Presentation posted in: General

CS8630 Database Administration Dr. Mario Guimaraes. 10-23-2008, DB Admin, Concurrency and Recovery. Class Will Start Momentarily…. Database Administration. All large and small databases need database administration

Download Presentation

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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


10 23 2008 db admin concurrency and recovery

CS8630 Database Administration

Dr. Mario Guimaraes

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

  • Class

    • Will

      • Start

      • Momentarily…


Database administration

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


Dba tasks

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 ?


Managing database structure

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


Concurrency control

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


Record locking

Record Locking

  • Shared versus Exclusive

  • Unit of Locking (Field, Row, Table, DB)

  • Explicit versus Implicit

  • Releasing Locks

  • Wait versus NoWait


Shared versus exclusive

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


Unit of locking field row table db

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.


Explicit versus implicit

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;


Releasing locks

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.


Wait versus nowait

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.


Commit rollback what tables are updated

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;


Why commit rollback

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


Implicit and explicit commit rollback

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)


Animations

Animations

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


Concurrency overview

Concurrency Overview


Concurrency db example

Concurrency – DB example


Concurrency error handling

Concurrency – Error Handling


Exclusive x shared lock review

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)


Unit of locking review

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


Lock terminology

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


Serializable transactions

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.


Deadlock

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


Optimistic pessimistic locking

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


If 2 transactions with no locks what will happen

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 ?


Record locking1

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


Deadlock1

TRANSACTION 1

Update Record 1

Update Record 2

TRANSACTION 2

Update Record 2

Update Record 1

Deadlock


Avoid deadlock

Avoid Deadlock

Select * from customers where cid = ‘c006’ for update no wait;

This command can avoid deadlock, but it may abort transactions pre-maturally.


Questions on locks

QUESTIONS ON LOCKS

  • WHAT TWO COMMANDS IN ORACLE RELEASES LOCKS RECORDS ?

  • WHEN WE EXIT FROM INTERACTIVE SQL, DOES COMMIT AUTOMATICALLY OCCURS OR A ROLLBACK ?


10 23 2008 db admin concurrency and recovery

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 ;

}


Cont c002 locked by another transaction

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


  • Locking in oracle v lock

    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.


    Summary

    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


    In oracle

    In Oracle

    • Select ???? For Update No wait;


    When deadlock detected possible action

    When deadlock detected (possible action)

    • Managing Sessions

    • SELECT username, sid, serial#, status

      FROMv$session

      WHEREusername = ‘JOHN’;

      USERNAME SID SERIAL # STATUS

      JOHN 9 3 5

      ALTER SYSTEM KILL SESSION ‘9,3’;


    Transactions recovery

    Transactions - Recovery


    Physical write to disc

    Physical Write to Disc


    Physical logical update

    Physical & Logical Update


    Recovery power failure

    Recovery - Power Failure


    Recovery test question

    Recovery – Test Question


    Triggers and locks

    Triggers and Locks


    End of lecture

    End of Lecture

    End

    Of

    Today’s

    Lecture.


  • Login