업데이트를 위해 선택...에는 항상 주문 기준이 포함되어야 합니까?
우리가 사형을 집행한다고 치자...
SELECT * FROM MY_TABLE FOR UPDATE
...MY_TABLE에 두 개 이상의 행이 있습니다.
이론적으로 두 개의 동시 트랜잭션이 이 문을 실행하지만 행을 서로 다른 순서로 이동(따라서 잠김)하는 경우 교착 상태가 발생할 수 있습니다.예:
- 트랜잭션 1: 행 A를 잠급니다.
- 트랜잭션 2: 행 B를 잠급니다.
- 트랜잭션 1: 행 B와 블록을 잠그려고 합니다.
- 트랜잭션 2: 행 A와 교착 상태를 잠그려고 합니다.
이 문제를 해결하는 방법은 ORDER BY를 사용하여 행이 항상 같은 순서로 잠겨 있는지 확인하는 것입니다.
그래서 제 질문은 이런 이론적 교착 상태가 실제로 일어날 수 있을까 하는 것입니다.인위적으로 유도할 수 있는 방법이 있는 것으로 알고 있습니다만, 정상적인 수술에서 그런 일이 발생할 수 있을까요?항상 ORDER BY를 사용해야 합니까, 아니면 생략해도 무방합니까?
저는 주로 Oracle과 MySQL/InnoDB의 동작에 관심이 있지만 다른 DBMS에 대한 의견도 도움이 될 것입니다.
편집 ---
다음은 잠금 순서가 동일하지 않을 때 오라클에서 교착 상태를 재현하는 방법입니다.
테스트 테이블을 만들고 테스트 데이터로 채웁니다.
CREATE TABLE DEADLOCK_TEST (
ID INT PRIMARY KEY,
A INT
);
INSERT INTO DEADLOCK_TEST SELECT LEVEL, 1 FROM DUAL CONNECT BY LEVEL <= 10000;
COMMIT;
...한 클라이언트 세션(SQL Developer 사용)에서 다음 블록을 실행합니다.
DECLARE
CURSOR CUR IS
SELECT * FROM DEADLOCK_TEST
WHERE ID BETWEEN 1000 AND 2000
ORDER BY ID
FOR UPDATE;
BEGIN
WHILE TRUE LOOP
FOR LOCKED_ROW IN CUR LOOP
UPDATE DEADLOCK_TEST
SET A = -99999999999999999999
WHERE CURRENT OF CUR;
END LOOP;
ROLLBACK;
END LOOP;
END;
/
다른 클라이언트 세션(SQL Developer 인스턴스를 하나 더 시작했을 뿐)에서 동일한 블록을 실행합니다.DESC에 시대에ORDER BY몇 초 후에 다음과 같은 메시지가 표시됩니다.
ORA-00060: deadlock detected while waiting for resource
참고로, 당신은 완전히 제거함으로써 같은 결과를 얻을 수 있을 것입니다.ORDER BY(따라서 두 블록 모두 동일), 그리고 추가...
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1;
한 블록 앞에서...
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10000;
...다른 실행 계획을 선택하고 행을 다른 순서로 가져올 가능성이 높습니다.
이는 커서에서 행을 가져올 때 잠금이 실제로 수행된다는 것을 나타냅니다(커서를 열 때 한 번에 전체 결과 세트에 대해 수행되는 것은 아님).
질문의 예를 보면 잠금 순서가 액세스 방법에 따라 다르다는 것을 알 수 있습니다.이 액세스 경로는 쿼리의 ORDER BY 절에 의해 직접 결정되지 않으므로 이 액세스 경로에 영향을 줄 수 있는 많은 요인이 있습니다.따라서 두 개의 서로 다른 액세스 경로가 있을 수 있기 때문에 ORDER BY를 추가하는 것만으로는 교착 상태를 방지할 수 없습니다.실제로 테스트 케이스를 실행하고 세션 매개 변수를 변경하여 두 세션을 동일한 쿼리를 가진 ORA-60에 실행할 수 있었습니다.
관련 세션에 보류 중인 다른 잠금이 없는 경우 모든 세션에서 동일한 순서로 행을 잠그면 교착 상태를 방지할 수 있지만 이 순서를 안정적으로 적용할 수 있는 방법은 무엇입니까?이것은 어쨌든 이 매우 특별한 교착 상태의 경우만 방지할 수 있습니다.각 세션의 여러 쿼리 또는 다른 계획으로 교착 상태가 발생할 수 있습니다.
실제로 이 경우는 정말 특별하고 어쨌든 자주 발생해서는 안 됩니다. 교착 상태가 걱정된다면, 저는 여전히 교착 상태를 방지할 수 있는 더 쉬운 방법이 있다고 생각합니다.
교착 상태를 방지하는 가장 쉬운 방법은 다음 중 하나를 사용하는 것입니다.FOR UPDATE NOWAIT또는FOR UPDATE WAIT X(WAIT X는 여전히 교착 상태 감지 메커니즘보다 높은 X의 값으로 교착 상태를 트리거할 수 있지만, 현재 11g 기준으로 3초입니다. 수정에 대해 @APC에 감사드립니다.)
즉, 두 트랜잭션 모두 해당 행을 제공하고 잠가야 합니다. 그러나 다른 사용자가 이미 잠금을 가지고 있으면 무기한 기다리는 대신 오류를 반환합니다.그것은 교착 상태를 야기하는 무한 대기입니다.
실제로 저는 실제 사용자가 있는 대부분의 응용 프로그램은 트랜잭션이 다른 트랜잭션이 완료될 때까지 무한정 기다리느니 차라리 즉시 오류를 수신할 것이라고 말하고 싶습니다.해 보겠습니다.FOR UPDATE 없이NOWAIT중요하지 않은 배치 작업에만 해당됩니다.
업데이트를 위한 방법을 잘못 이해하신 것 같습니다.이는 커서가 활성화될 때, 즉 SELECT가 실행될 때 잠금을 획득합니다.
따라서 쿼리를 실행하면 WHERE 절을 지정하지 않았기 때문에 트랜잭션 1이 전체 테이블을 잠급니다.트랜잭션 2는 트랜잭션 1이 선택한 레코드 집합에 대해 DML을 실행했는지 여부에 관계없이(사용자가 WAIT 절에서 지정한 내용에 따라) 중단되거나 실패합니다.사실 트랜잭션 1은 레코드를 가져올 필요조차 없습니다. 트랜잭션 1이 업데이트 커서를 열면 트랜잭션 2는 ORA-00054를 던집니다.
당신이 설명하는 교착 상태 시나리오는 낙관적인 잠금을 사용하는 애플리케이션의 고전적인 결과입니다(즉, 필요할 때 잠금을 획득할 수 있다고 가정합니다).For Update의 요점은 비관적인 잠금 전략이라는 것입니다. 미래에 성공적인 처리를 보장하기 위해 지금 필요한 모든 잠금을 확보해야 합니다.
Kyte 씨는 블로그에서 다음과 같은 중요한 통찰력을 제공합니다.
"심각한 탐지가 대기 기간을 능가합니다."
내 코드에서 나는 두 번째 세션에서 사용된 커서의 FOR UPDATE 절에 NOWAIT을 사용하고 있었습니다.
cursor c10000 is
select * from order_lines
where header_id = 1234
for update;
cursor c1 is
select * from order_lines
where header_id = 1234
and line_id = 9999
for update nowait;
결과적으로 세션 2는 즉시 실패하고 ORA-00054를 던집니다.
그러나 OP는 아무것도 지정하지 않으며, 이 경우 두 번째 세션은 행이 해제될 때까지 무한정 기다립니다.그렇지 않은 경우를 제외하고는 잠시 후 교착 상태 감지가 시작되고 명령이 극도의 편견으로 종료되기 때문입니다.ORA-00060.만약 그들이 짧은 대기 기간을 지정했다면 - WAIT 1처럼 - 그들은 보았을 것입니다.ORA-30006: resource busy.
자세한 구문을 사용하는지 여부에 관계없이 발생합니다.
open c10000;
loop
fetch c10000 into r;
아니면 더 멋진...
for r in c10000 loop
그리고 세션 2가 시작될 때 세션 1이 관심의 행을 가져왔는지 여부는 중요하지 않습니다.
tl;dr
중요한 것은 ORDER BY로 해결할 수 있는 것이 없다는 것입니다.업데이트를 위해 발급할 첫 번째 세션은 결과 집합의 모든 레코드를 가져옵니다.이러한 레코드를 업데이트하려는 후속 세션은 NOWAIT, Wait 또는 nothing 중 하나를 지정했는지에 따라 ORA-00054, ORA-30006 또는 ORA-00060 중 하나로 실패합니다.WAIT 기간이 초과되거나 교착 상태 감지가 시작되기 전에 첫 번째 세션이 잠금을 해제하지 않는 한.
다음은 실제 사례입니다.저는 두 번째 세션을 시뮬레이션하기 위해 자동 트랜잭션을 사용하고 있습니다.효과는 동일하지만 출력을 읽기가 더 쉽습니다.
declare
cursor c1 is
select * from emp
where deptno = 10
for update;
procedure s2
is
cursor c2 is
select * from emp
where empno = 7934 -- one of the employees in dept 10
for update
-- for update nowait
-- for update wait 1
;
x_deadlock exception;
pragma exception_init( x_deadlock, -60);
x_row_is_locked exception;
pragma exception_init( x_row_is_locked, -54);
x_wait_timeout exception;
pragma exception_init( x_wait_timeout, -30006);
pragma autonomous_transaction;
begin
dbms_output.put_line('session 2 start');
for r2 in c2 loop
dbms_output.put_line('session 2 got '||r2.empno);
update emp
set sal = sal * 1.1
where current of c2;
dbms_output.put_line('session 2 update='||sql%rowcount);
end loop;
rollback;
exception
when x_deadlock then
dbms_output.put_line('session 2: deadlock exception');
when x_row_is_locked then
dbms_output.put_line('session 2: nowait exception');
when x_wait_timeout then
dbms_output.put_line('session 2: wait timeout exception');
end s2;
begin
for r1 in c1 loop
dbms_output.put_line('session 1 got '||r1.empno);
s2;
end loop;
end;
/
이 버전에서는 직선을 지정했습니다.for update제2회에다음은 OP가 사용하는 구성이며 교착 상태가 감지되었기 때문에 출력 헐에서 볼 수 있습니다.
session 1 got 7782
session 2 start
session 2: deadlock exception
session 1 got 7839
session 2 start
session 2: deadlock exception
session 1 got 7934
session 2 start
session 2: deadlock exception
PL/SQL procedure successfully completed.
이것이 분명히 보여주는 것은
- 첫 번째 세션이 결과 집합 전체를 Go-get에서 잠급니다. 첫 번째 세션이 아직 검색하지 않은 경우에도 두 번째 세션은 해당 행 하나에 대해 잠금을 설정하지 않기 때문입니다.
- 그
Deadlock detected두 번째 세션이 아무것도 업데이트할 수 없음에도 예외가 발생합니다. 1.Deadlock detected첫 번째 세션이 가져온 윈도우를 업데이트하지 않더라도 예외가 발생합니다.
이 코드는 FOR UPDATE 변형의 다양한 동작을 보여주기 위해 쉽게 수정할 수 있습니다.
언급URL : https://stackoverflow.com/questions/11311951/should-select-for-update-always-contain-order-by
'programing' 카테고리의 다른 글
| 스프링 부트 애플리케이션 내부에 리액트 웹 애플리케이션과 jar 패키징을 통합하는 방법 (0) | 2023.07.31 |
|---|---|
| MySQL GROUP_CONCAT(null 포함) (0) | 2023.07.31 |
| Angular 2에서 상대 경로가 매우 긴 수입을 피하는 방법은 무엇입니까? (0) | 2023.07.31 |
| web.config에서 디버그가 활성화되었는지 확인하려면 어떻게 해야 합니까? (0) | 2023.07.31 |
| SQL 쿼리 - 결과를 하나의 문자열로 연결 (0) | 2023.07.31 |