오라클 추적 파일에서 교착 상태 오류의 원인 찾기

오라클 추적 파일에서 교착 상태 오류의 원인 찾기

여러 사용자가 응용프로그램을 사용할 때 응용프로그램에서 "ora-00060 교착 상태가 리소스 대기 중에 감지됨" 오류가 자주 발생합니다.오라클 관리자로부터 추적 파일을 얻었지만, 읽을 때 도움이 필요합니다.아래는 추적 파일의 데이터이며, 원인을 찾는 데 도움이 될 것으로 기대합니다.

*** 2013-06-25 09:37:35.324
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a deadlock due 
to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                   ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-000151a2-00000000       210      72    SX   SSX      208      24    SX   SSX
TM-000151a2-00000000       208      24    SX   SSX      210      72    SX   SSX

session 72: DID 0001-00D2-000000C6  session 24: DID 0001-00D0-00000043 
session 24: DID 0001-00D0-00000043  session 72: DID 0001-00D2-000000C6 

Rows waited on:
 Session 72: no row
 Session 24: no row

----- Information for the OTHER waiting sessions -----
Session 24:
 sid: 24 ser: 45245 audsid: 31660323 user: 90/USER
  flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
  flags2: (0x40009) -/-/INC
 pid: 208 O/S info: user: zgrid, term: UNKNOWN, ospid: 2439
   image: oracle@xyz.local
 client details:
   O/S info: user: , term: , ospid: 1234
   machine: xyz.local program: 
 current SQL:
  delete from EMPLOYEE where EMP_ID=:1

 ----- End of information for the OTHER waiting sessions -----

Information for THIS session:

 ----- Current SQL Statement for this session (sql_id=dyfg1wd8xa9qt) -----
 delete from EMPLOYEE where EMP_ID=:1
===================================================

"데드록 그래프:라고 말하고 있습니다.또한 섹션에서 대기 중인 행은 행이 없음으로 표시됩니다.

저는 또한 몇몇 블로그에서 추적 파일의 "sqltxt" 섹션이 원인을 제시할 수 있다는 것을 읽었습니다.아래는 해당 섹션에 표시된 쿼리입니다.

 select /*+ all_rows */ count(1) from "USERS"."EMPLOYEE_SALARY" where EMPSAL_EMP_ID=:1

employee_salary 테이블에는 EMPSAL_EMP_ID 열에 외부 키 제약 조건이 있습니다.

sql 힌트에 "all_rows"라고 표시되는데, 직원 테이블에서 레코드를 삭제할 때 이 테이블이 테이블 레벨 잠금을 받는다는 뜻입니까?저는 현재 외부 키 열에 색인이 없습니다.이 열에 인덱스를 추가하는 것이 도움이 됩니까?

더 필요한 정보가 있을 경우 게시해 주시기 바랍니다.

감사해요.

일단은.select문은 Oracle에서 어떤 것도 잠그지 않으며 사용 가능한 마지막 일관된 버전의 데이터만 사용합니다.을 위한 경우가 아닙니다.select ... for update다음과 같이 데이터를 잠급니다.update오라클 9i 이후, 하지만 없습니다.for update질문의 쿼리에 절이 있습니다.

Resource Name          process session holds waits  process session holds waits
TM-000151a2-00000000       210      72    SX   SSX      208      24    SX   SSX

세션 #72는 "Row Exclusive" 유형(SX)이 있는 테이블 수준 잠금(TM)을 보유하고 있으며 동일한 테이블에서 "Share Row Exclusive"(SSX) 잠금을 획득하려고 합니다.이 세션은 이미 동일한 유형의 테이블 수준 잠금(SX)을 보유하고 있으며 SSX 잠금이 사용 가능할 때까지 대기하는 세션 #24에 의해 차단되었습니다.

Resource Name          process session holds waits  process session holds waits
TM-000151a2-00000000       208      24    SX   SSX      210      72    SX   SSX

이(두 번째 행)는 정확히 같은 상황을 보여주지만 반대 방향입니다.세션 #24는 SSX 잠금이 사용 가능해질 때까지 대기하지만 동일한 테이블에 이미 SX 잠금을 보유하고 있는 세션 #72에 의해 차단됩니다.

세션 #24와 세션 #72는 서로를 차단합니다. 교착 상태가 발생합니다.

두 잠금 유형(SX 및 SSX)은 모두 테이블 레벨 잠금입니다.
상황을 이해하기 위해서 저는 프랭크 파초의 이 기사를 읽는 것을 추천합니다.

다음은 귀하의 상황과 직접적으로 관련이 있는 이 기사의 인용문입니다(SSX와 SRX의 약어는 동일합니다).

참조 무결성은 TM 잠금도 획득합니다.예를 들어 인덱스되지 않은 외부 키와 관련된 일반적인 문제는 상위 테이블에서 키를 삭제하거나 업데이트할 때 하위 테이블에 잠금이 발생합니다.인덱스가 없으면 Oracle에는 참조 무결성을 위반할 수 있는 동시 삽입을 방지하기 위해 잠글 하위 수준 리소스가 하나도 없기 때문입니다.
외부 키 열이 일반 인덱스의 선행 열인 경우 상위 값을 가진 첫 번째 인덱스 항목을 단일 리소스로 사용하고 행 수준 TX 잠금으로 잠글 수 있습니다.
그리고 만약 참조 무결성에 삭제 시 캐스케이드가 있다면 어떨까요?S 모드 외에도 Row X(RX) 모드와 마찬가지로 하위 테이블의 행을 업데이트합니다.여기서 공유 행 배타적(SRX)이 발생합니다. S+RX=SRX가 표시됩니다.

가장 이 높은 변수는 # # 서라가장높은변세형션 #72및에 #24서다것일의 입니다.EMPLOYEE에 표가 , 테블동시에그, 거에기고리이가 있습니다.on delete cascadeEMPSAL_EMP_ID 색인 것과함께에 색인이 하여.EMPLOYEE_SALARY 있는 EMPSAL_EMP_ID열이 먼저 나열됩니다.

언급URL : https://stackoverflow.com/questions/17358088/finding-cause-of-deadlock-error-from-oracle-trace-file