Oracle Database Administration/Data Concurrency
Appearance
This lesson introduces Oracle database data concurrency.
Objectives and Skills
[edit | edit source]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
Readings
[edit | edit source]Multimedia
[edit | edit source]Activities
[edit | edit source]- 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;
- Connect to the database and identify the current session using the following query:
- 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;
- Connect to the database and identify the current session using the following query:
- 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;
- Connect to the database and identify the current session using the following query:
See Also
[edit | edit source]References
[edit | edit source]