Oracle Database Administration/Data Concurrency

This lesson introduces Oracle database data concurrency.

Objectives and skills for the data concurrency portion of Oracle Database Administration I certification include:^[1]

• Managing Data and Concurrency
  • Monitor and resolve locking conflicts

1. Oracle: Database Concepts Data Concurrency and Consistency

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

1. Resolve locking conflicts using Enterprise Manager Database Control.
   1. Connect to the database and identify the current session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   2. Connect to the database in a second session and identify the session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   3. In the first session, begin a transaction using the following query:
      SELECT * FROM HR.REGIONS FOR UPDATE;
   4. In the second session, begin a transaction using the following query:
      SELECT * FROM HR.REGIONS FOR UPDATE;
   5. Use Enterprise Manager Database Control / Performance / Blocking Sessions to view blocking sessions. Note the Session ID and Serial Number of the idle session.
   6. Select Kill Session and then Show SQL.
   7. Kill the idle session and then refresh Blocking Sessions.
   8. Return to the two sessions and observe the results.
   9. Identify the current session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   10. In the session that is still active, roll back the transaction using the following query:
       ROLLBACK;
2. Resolve locking conflicts using SQL.
   1. Connect to the database and identify the current session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   2. Connect to the database in a second session and identify the session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   3. In the first session, begin a transaction using the following query:
      SELECT * FROM HR.REGIONS FOR UPDATE;
   4. In the second session, begin a transaction using the following query:
      SELECT * FROM HR.REGIONS FOR UPDATE;
   5. 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;
   6. 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);
   7. Kill the blocking session by filling in the SID and Serial# in the following query:
      ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>' IMMEDIATE;
   8. Return to the two sessions and observe the results.
   9. Identify the current session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   10. In the session that is still active, roll back the transaction using the following query:
       ROLLBACK;
3. Create a deadlock.
   1. Connect to the database and identify the current session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   2. Connect to the database in a second session and identify the session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   3. In the first session, begin a transaction using the following query:
      SELECT * FROM HR.REGIONS FOR UPDATE;
   4. In the second session, begin a transaction using the following query:
      SELECT * FROM HR.COUNTRIES FOR UPDATE;
   5. In the first session, extend the transaction using the following query:
      SELECT * FROM HR.COUNTRIES FOR UPDATE;
   6. In the second session, extend the transaction using the following query:
      SELECT * FROM HR.REGIONS FOR UPDATE;
   7. In the session that detects a deadlock, roll back the remaining transaction using the following query:
      ROLLBACK;
   8. In the session that is still active, roll back the transaction using the following query:
      ROLLBACK;

• Oracle: V$LOCK
• Oracle: V$SESSION