Oracle에서 잠긴 행을 찾는 방법

Oracle에서 잠긴 행을 찾는 방법

Oracle 데이터베이스가 있으며 고객 계정 테이블에는 약 백만 개의 행이 있습니다.몇 년 동안 NAT은 4개의 서로 다른 UI(Oracle Forms에 2개, 에 2개)를 구축했습니다.Net), 모두 사용 중입니다.또한 여러 가지 백그라운드 작업(지속적 작업 및 예약된 작업)이 있습니다.

계정 테이블의 행에 있는 긴 잠금(예: 30초 이상)이 걸려 지속적인 백그라운드 작업 중 하나가 실패하는 경우가 있습니다.업데이트 시간이 초과되면 문제의 백그라운드 작업이 자동으로 다시 시작됩니다.우리는 그 일이 일어난 후 몇 분 후에 그것에 대해 알게 되지만, 그 때쯤이면 자물쇠가 풀렸습니다.

우리는 그것이 잘못된 UI일 수 있다고 믿는 이유가 있지만, "스모킹 건"을 찾을 수 없었습니다.

블록을 나열하는 쿼리를 몇 개 찾았지만, 이는 두 개의 작업이 한 줄로 경쟁하는 경우를 위한 것입니다.잠금을 시도하는 보조 작업이 없을 때 어떤 행에 잠금이 있는지 알고 싶습니다.

우리는 11g에 있지만, 8i 이후로 문제가 발생하고 있습니다.

Oracle의 잠금 개념은 다른 시스템의 잠금 개념과 상당히 다릅니다.

행이 있을 때Oracle잠기면 레코드 자체가 새 값(있는 경우)으로 업데이트되고, 또한 잠금(기본적으로 롤백 세그먼트에 있는 트랜잭션 잠금에 대한 포인터)이 레코드에 바로 배치됩니다.

이것은 레코드를 잠근다는 것을 의미합니다.Oracle레코드의 메타데이터를 업데이트하고 논리적 페이지 쓰기를 실행하는 것을 의미합니다.예를 들어, 당신은 할 수 없습니다.SELECT FOR UPDATE읽기 전용 테이블스페이스에 있습니다.

또한 커밋 후 레코드 자체가 업데이트되지 않고 롤백 세그먼트가 업데이트됩니다.

즉, 트랜잭션 자체가 오래 전에 종료된 경우에도 각 레코드에는 마지막으로 업데이트한 트랜잭션에 대한 일부 정보가 저장됩니다.트랜잭션이 활성 상태인지 여부(따라서 레코드가 활성 상태인지 여부)를 확인하려면 롤백 세그먼트를 방문해야 합니다.

Oracle에는 기존의 잠금 관리자가 없으므로 모든 잠금 목록을 가져오려면 모든 개체의 모든 레코드를 검색해야 합니다.시간이 너무 오래 걸립니다.

객체와 잠금을 수 .v$locked_object waits), 잠금대사(사용)v$session데이터베이스의 모든 개체에 대한 모든 잠금 목록은 아닙니다.

다음 쿼리를 사용하여 Oracle에서 잠긴 테이블을 찾을 수 있습니다.

    select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

잠금보다는 장기적으로 실행되는 트랜잭션을 검토하는 것이 좋습니다.v$transaction서 여서가입수다있니습기할에 할 수 .v$session사용자뿐만 아니라 UI(프로그램 및 기계 열 사용)에 대한 아이디어를 제공합니다.

.dba_blockers,dba_waiters그리고.dba_locks 이름들은 설명할수 .이름은 스스로 설명할 수 있어야 합니다.

들어, 에 한 번 을 만들 수 .dba_blockers 활동 중인 재현활상태성상▁andsql_id그 시간 동안.(을 통해)v$session그리고.v$sqlstats).

당신은 또한 조사하는 것이 좋을 것입니다.v$sql_monitor5초 이상 걸리는 모든 SQL을 기본 로그로 기록합니다.Enterprise Manager 의 "SQL Monitoring" 페 있 수 니 습 다 볼 도 이

아래 PL/SQL 블록은 테이블에서 잠긴 모든 행을 찾습니다.다른 답변은 차단 세션만 찾으며, 실제 잠긴 행을 찾으려면 각 행을 읽고 테스트해야 합니다.

그러나 이 코드를 실행할 필요는 없습니다.잠금 문제가 있는 경우 일반적으로 다음을 사용하여 범인을 찾는 것이 더 쉽습니다.GV$SESSION.BLOCKING_SESSION기타 관련 데이터 사전 보기.이 매우 느린 코드를 실행하기 전에 다른 접근 방식을 시도하십시오.)

먼저 샘플 테이블과 몇 가지 데이터를 생성해 보겠습니다.세션 #1에서 실행합니다.

--Sample schema.
create table test_locking(a number);
insert into test_locking values(1);
insert into test_locking values(2);
commit;
update test_locking set a = a+1 where a = 1;

세션 #2에서 잠긴 ROWID를 보관할 테이블을 만듭니다.

--Create table to hold locked ROWIDs.
create table locked_rowids(the_rowid rowid);
--Remove old rows if table is already created:
--delete from locked_rowids;
--commit;

세션 #2에서 이 PL/SQL 블록을 실행하여 전체 테이블을 읽고, 각 행을 조사하고, 잠긴 ROWID를 저장합니다.경고합니다. 이것은 터무니없이 느릴 수 있습니다.이 쿼리의 실제 버전에서 TEST_LOCKING에 대한 두 참조를 자신의 테이블로 변경합니다.

--Save all locked ROWIDs from a table.
--WARNING: This PL/SQL block will be slow and will temporarily lock rows.
--You probably don't need this information - it's usually good enough to know
--what other sessions are locking a statement, which you can find in
--GV$SESSION.BLOCKING_SESSION.
declare
    v_resource_busy exception;
    pragma exception_init(v_resource_busy, -00054);
    v_throwaway number;
    type rowid_nt is table of rowid;
    v_rowids rowid_nt := rowid_nt();
begin
    --Loop through all the rows in the table.
    for all_rows in
    (
        select rowid
        from test_locking
    ) loop
        --Try to look each row.
        begin
            select 1
            into v_throwaway
            from test_locking
            where rowid = all_rows.rowid
            for update nowait;
        --If it doesn't lock, then record the ROWID.
        exception when v_resource_busy then
            v_rowids.extend;
            v_rowids(v_rowids.count) := all_rows.rowid;
        end;

        rollback;
    end loop;

    --Display count:
    dbms_output.put_line('Rows locked: '||v_rowids.count);

    --Save all the ROWIDs.
    --(Row-by-row because ROWID type is weird and doesn't work in types.)
    for i in 1 .. v_rowids.count loop
        insert into locked_rowids values(v_rowids(i));
    end loop;
    commit;
end;
/

마지막으로 LOCKED_ROWIDS 테이블에 참여하여 잠긴 행을 볼 수 있습니다.

--Display locked rows.
select *
from test_locking
where rowid in (select the_rowid from locked_rowids);


A
-
1

일부 테이블이 주어지면 어떤 행이 잠겨 있지 않은지 확인할 수 있습니다.SELECT FOR UPDATESKIP LOCKED.

예를 들어, 이 쿼리는 잠금 해제된 모든 행을 잠그고 반환합니다.

SELECT * FROM mytable FOR UPDATE SKIP LOCKED

레퍼런스

• TOM에게 "오라클에서 잠긴 행에 대한 ROWID를 가져오는 방법"을 물어봅니다.

언급URL : https://stackoverflow.com/questions/2186848/how-to-find-locked-rows-in-oracle