Resolving Lock Conflicts - Practices for Lesson 10: Overview

Practices for Lesson 10: Overview

Practice 10-1: Resolving Lock Conflicts

In this practice, you use two separate SQL*Plus sessions to cause a lock conflict. Using Enterprise Manager, you detect the cause of the lock conflict, and then resolve the conflict. 1. Users NGREENBERG and SMAVRIS already exist in your database. User NGREENBERG

makes an uncommitted update to a row in the HR.EMPLOYEES table. Then user SMAVRIS attempts to update the same row.

a. Ensure your environment is configured for the orcl database by executing the oraenv command in each terminal window.

$ . oraenv

ORACLE_SID = [oracle] ? orcl …

b. In one terminal window, invoke SQL*Plus and connect as NGREENBERG with the password of oracle_4U. Perform the update shown below. Do NOT commit or exit the SQL*Plus session.

$ sqlplus ngreenberg … Enter password: Connected to: … SQL> show user USER is "NGREENBERG"

SQL> update hr.employees set phone_number='650.555.1212' 2> where employee_id = 110;

1 row updated.

SQL>

c. Leave this session connected in the state that it is currently. Do not exit at this time. 2. In a separate terminal window, attempt to update the same row in a separate session by executing the SQL statement shown below. Do not worry if the session seems to “hang”— this is the condition that you are trying to create.

a. Open a second terminal window. Log in to SQL*Plus as the SMAVRIS user with the password oracle_4U.

$ sqlplus smavris

…

Enter password: …

SQL> update hr.employees set salary=8300 2> where employee_id = 110;

b. Notice that this session appears to be hung. Leave this session as is and move on to the next step.

3. Using EM Express, navigate to the Current Findings tab of the Performance Hub page and determine which session is causing the locking conflict.

a. In EM Express, select Performance > Performance Hub. Then click the Current ADDM Findings tab. In the Findings section, click the detail finding “Unresolved hangs or session wait chains.”

b. In the Details section, you should see two sessions listed. The first session is the blocker. In the example, the Session ID of the blocker is 1:38,45678. The session ID that you see will be different. Record the Session ID ______________________.

Note: You will resolve the blocking session in Enterprise Manager Cloud Control in subsequent steps. However, note that you could execute the following statement as SYSDBA to kill the session:

ALTER SYSTEM KILL SESSION (38,45678);

You could also issue the following command as an OS sys administrator to stop the OS process associated with the blocking session.

$ kill 26623

4. Using Cloud Control, find the details of the blocking session.

a. Log in to Cloud Control as the admin user with the password oracle_4U. Then navigate to the orcl home page.

b. Because Susan Mavris doesn't know what is wrong, you start Real Time ADDM to diagnose the problem. Select Performance > Real Time ADDM.

c. If you are asked to provide credentials, select Preferred and SYSDBA Database Credentials in the SYSDBA Credentials section. In the DB Host Credentials section, select New and enter oracle in the User Name field. Enter your OS password in the Password and Confirm Password fields. Then click Submit.

d. On the Real Time ADDM page, in the Results section, click Start.

e. When the Number of Findings field shows a value, click the Findings tab.

f. On the Finding tab, notice the same information about blocking sessions that you saw in EM Express.

g. Click the Hang Data tab. Notice that this tab gives you the session details of the blocking session such as username. You could contact NGREENBERG directly and ask her to commit or roll back the transaction, or you could kill her session. Do NOT kill the session at this time. Continue to investigate the issue.

5. What was the last SQL statement that the blocking session executed? a. Click Performance > Blocking Sessions.

b. Select the NGREENBERG session, and then click View Session.

c. Under the Application heading, click the hash value link for Current SQL or Previous SQL.

d. Note the SQL that was most recently run. Seeing the last SQL statement can help you to decide which session to kill.

6. Resolve the conflict in favor of the user who complained, by killing the blocking session. a. Click Performance > Blocking Sessions.

b. Select the NGREENBERG session, and then click Kill Session.

c. On the Confirmation page, choose the Option Kill Immediate. Click Show SQL. d. On the Show DDL page, the SQL statement is shown.

e. Click Return. f. Click Yes.

g. On the Blocking Sessions page, the listing is gone, and an information message appears saying that session nnn has been killed.

7. Return to the SQL*Plus command window, and note that SMAVRIS’s update has now completed successfully. Issue a ROLLBACK command in this session and exit.

SQL> update hr.employees set salary=8300 2> where employee_id = 110; 1 row updated. SQL> ROLLBACK; Rollback complete. SQL> exit $

8. Try issuing a SQL select statement in the NGREENBERG session. What do you see? SQL> SELECT sysdate from dual;

SELECT sysdate from dual *

ERROR at line 1:

ORA-03135: connection lost contact Process ID: 26623

Session ID: 38 Serial number: 45678 SQL>

Answer: The session has been disconnected. There could be other errors such as:

− ORA-12571: TNS:packet writer failure − ORA-03114: not connected to ORACLE

9. Close all open SQL sessions by entering exit, and then close the terminal windows.

Practices for Lesson 11:

In document D78846GC20_ag (Page 109-115)