# Oracle Database Administration/Data Concurrency

This lesson introduces Oracle database data concurrency.

## Objectives and Skills

Objectives and skills for the data concurrency portion of Oracle Database Administration I certification include:
* Managing Data and Concurrency
** Monitor and resolve locking conflicts

## Readings

* Oracle: Database Concepts Data Concurrency and Consistency

## Multimedia

* YouTube: How to kill a user session connected to an Oracle database

## Activities

* Resolve locking conflicts using Enterprise Manager Database Control.
** Connect to the database and identify the current session using the following query: SELECT DISTINCT SID FROM V$MYSTAT;
** Connect to the database in a second session and identify the session using the following query: SELECT DISTINCT SID FROM V$MYSTAT;
** In the first session, begin a transaction using the following query: SELECT * FROM HR.REGIONS FOR UPDATE;
** In the second session, begin a transaction using the following query: SELECT * FROM HR.REGIONS FOR UPDATE;
** Use Enterprise Manager Database Control / Performance / Blocking Sessions to view blocking sessions. Note the Session ID and Serial Number of the idle session.
** Select Kill Session and then Show SQL.
** Kill the idle session and then refresh Blocking Sessions.
** Return to the two sessions and observe the results.
** Identify the current session using the following query: SELECT DISTINCT SID FROM V$MYSTAT;
** In the session that is still active, roll back the transaction using the following query: ROLLBACK;
* Resolve locking conflicts using SQL.
** Connect to the database and identify the current session using the following query: SELECT DISTINCT SID FROM V$MYSTAT;
** Connect to the database in a second session and identify the session using the following query: SELECT DISTINCT SID FROM V$MYSTAT;
** In the first session, begin a transaction using the following query: SELECT * FROM HR.REGIONS FOR UPDATE;
** In the second session, begin a transaction using the following query: SELECT * FROM HR.REGIONS FOR UPDATE;
** Connect to the database in a third session and identify the blocking and blocked sessions using the following queries: SELECT * FROM V$LOCK WHERE BLOCK != 0; SELECT SID, BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;
** Identify the SID and Serial# of the blocking session using the following query: SELECT SID, SERIAL#, WAIT_CLASS FROM V$SESSION WHERE SID IN (SELECT BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION != 0);
** Kill the blocking session by filling in the SID and Serial# in the following query: ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>' IMMEDIATE;
** Return to the two sessions and observe the results.
** Identify the current session using the following query: SELECT DISTINCT SID FROM V$MYSTAT;
** In the session that is still active, roll back the transaction using the following query: ROLLBACK;
* Create a deadlock.
** Connect to the database and identify the current session using the following query: SELECT DISTINCT SID FROM V$MYSTAT;
** Connect to the database in a second session and identify the session using the following query: SELECT DISTINCT SID FROM V$MYSTAT;
** In the first session, begin a transaction using the following query: SELECT * FROM HR.REGIONS FOR UPDATE;
** In the second session, begin a transaction using the following query: SELECT * FROM HR.COUNTRIES FOR UPDATE;
** In the first session, extend the transaction using the following query: SELECT * FROM HR.COUNTRIES FOR UPDATE;
** In the second session, extend the transaction using the following query: SELECT * FROM HR.REGIONS FOR UPDATE;
** In the session that detects a deadlock, roll back the remaining transaction using the following query: ROLLBACK;
** In the session that is still active, roll back the transaction using the following query: ROLLBACK;

## See Also

* Oracle: V$LOCK
* Oracle: V$SESSION