업데이트 실행 중 SQL 업데이트가 하위 쿼리에 영향을 줍니까?

업데이트 실행 중 SQL 업데이트가 하위 쿼리에 영향을 줍니까?

저는 단지 다음과 같은 복잡한 업데이트 쿼리를 작성하고 있을 뿐입니다.

update table join
    (select y, min(x) as MinX 
     from table
     group by y) as t1
    using (y)
set x = x - MinX

그 말은 그 변수가x하위 쿼리를 기반으로 업데이트되며 변수도 처리합니다.x- 이미 실행 중인 update 명령으로 수정할 수 있지 않습니까?이거 문제 아닌가요?일반적인 프로그래밍에서는 일반적으로 이를 명시적으로 처리해야 합니다. 즉, 이전 값에서 새 값을 다른 곳에 저장하고 작업이 완료된 후에는 이전 값을 새 값으로 교체합니다.하지만 SQL 데이터베이스는 어떻게 이것을 할 것인가요?

저는 단 하나의 관찰이나 실험에는 관심이 없습니다.이 경우 정의된 동작이 무엇인지 알려줄 문서 또는 sql 표준의 스니펫을 가지고 싶습니다.MySQL을 사용하고 있지만, 다른 PostgresQL, Oracle 등에서도 유효한 답변과 특히 SQL 표준에 대해서는 전반적으로 감사드립니다.감사합니다!

** 편집됨 **

대상 테이블에서 선택

13.2.9.8부터. FROM 조항의 하위 쿼리:

FROM 절의 하위 쿼리는 스칼라, 열, 행 또는 테이블을 반환할 수 있습니다.JOIN 작업의 ON 절에서 사용하지 않는 한 FROM 절의 하위 쿼리는 상관된 하위 쿼리가 될 수 없습니다.

예, 위의 쿼리를 수행할 수 있습니다.

문제가

여기에는 정말 두 가지 문제가 있습니다.동시성이 있습니다. 즉, 아무도 우리의 발밑에서 데이터를 변경하지 않도록 보장하는 것입니다.이것은 잠금으로 처리됩니다.새 값과 이전 값의 실제 수정에 대한 내용은 파생된 표를 사용하여 처리합니다.

잠금

위의 쿼리의 경우 InnoDB에서 MySQL이 먼저 SELECT를 수행하고 테이블의 각 행에 대한 읽기(공유) 잠금을 개별적으로 획득합니다.SELECT 문에 WHERE 절이 있는 경우 선택한 레코드만 잠기게 되며, 범위를 지정하면 공백도 잠기게 됩니다.

읽기 잠금을 사용하면 다른 쿼리가 쓰기 잠금을 획득할 수 없으므로, 읽기 잠금 상태에서는 다른 곳에서 레코드를 업데이트할 수 없습니다.

그런 다음 MySQL은 테이블의 각 레코드에 대한 쓰기(전용) 잠금을 개별적으로 획득합니다.UPDATE 문에 WHERE 절이 있으면 특정 레코드만 기록 잠금이 되고, WHERE 절이 범위를 선택하면 범위가 잠깁니다.

이전 SELECT에서 읽기 잠금이 설정된 레코드는 자동으로 쓰기 잠금으로 확대됩니다.

쓰기 잠금은 다른 쿼리가 읽기 또는 쓰기 잠금을 얻을 수 없도록 합니다.

Innotop을 사용하여 잠금 모드로 실행하고 트랜잭션을 시작하고 쿼리를 실행하면 Innotop에 잠금이 표시됩니다.또한 Innotop을 사용하지 않고도 자세한 내용을 볼 수 있습니다.SHOW ENGINE INNODB STATUS.

데드락

두 인스턴스가 동시에 실행된 경우 쿼리는 교착 상태에 취약합니다.쿼리 A에 읽기 잠금이 있는 경우 쿼리 B에 읽기 잠금이 있는 경우 쿼리 A는 쓰기 잠금을 획득하기 전에 쿼리 B의 읽기 잠금이 해제될 때까지 기다려야 합니다.그러나 쿼리 B는 완료될 때까지 읽기 잠금을 해제하지 않으며 쓰기 잠금을 획득할 수 없는 한 완료되지 않습니다.쿼리 A와 쿼리 B는 교착 상태에 있으며 따라서 교착 상태에 빠집니다.

따라서 메모리를 사용하고 성능에 영향을 미치는 방대한 양의 레코드 잠금을 방지하고 교착 상태를 방지하기 위해 명시적 테이블 잠금을 수행할 수 있습니다.

내부 SELECT에서 SELECT ... FOR UPDATE를 사용하는 방법도 있습니다.이것은 읽기와 에스컬레이션으로 시작하는 대신 모든 행에 쓰기 잠금으로 시작합니다.

파생표

내부 SELECT의 경우 MySQL은 파생된 임시 테이블을 만듭니다.파생 테이블은 MySQL에서 자동으로 생성되는 임시 테이블에 있는 데이터의 실제 인덱스가 아닌 복사본입니다(사용자가 명시적으로 생성하고 인덱스를 추가할 수 있는 임시 테이블과는 반대).

MySQL은 파생 테이블을 사용하므로 질문에서 언급하는 임시 이전 값입니다.다른 말로 하자면, 여기엔 마법이 없습니다.MySQL은 임시 값을 사용하여 다른 곳에서 수행하는 것과 마찬가지로 수행합니다.

도출된 테이블은 UPDATE 문(MySQL 5.6+에서 지원)에 대한 EXPRESON을 수행하여 볼 수 있습니다.

적절한 RDBMS는 다음을 사용합니다.statement level read consistency, 그러면 문장이 문장이 시작할 때의 데이터를 그대로(selects) 볼 수 있습니다.그래서 당신이 두려워하는 시나리오는 발생하지 않을 것입니다.

안부 전해요,
롭.

Oracle은 11.2 설명서에 이 내용이 있습니다.

모든 쿼리에 대해 일관된 결과 집합이 제공되어 사용자의 작업 없이 데이터 일관성을 보장합니다.UPDATE 문의 WHERE 절에 포함된 쿼리와 같은 암시적 쿼리는 일관된 결과 집합을 보장합니다.그러나 암시적 쿼리의 각 문은 DML 문 자체에 의해 변경된 것을 보지 않고 변경되기 전의 데이터를 그대로 봅니다.

자체 데이터를 기반으로 테이블을 업데이트할 수 없다는 것이 알려졌지만, 다음을 통해 MySQL 구문을 조정할 수 있어야 합니다.

update Table1, 
       (select T2.y, MIN( T2.x ) as MinX from Table1 T2 group by T2.y ) PreQuery
  set Table1.x = Table1.x - PreQuery.MinX
  where Table1.y = PreQuery.y

JOIN 대 쉼표 목록 버전을 사용하여 구문이 다른 경로로 이동하는지는 모르겠지만, 완료된 사전 쿼리에 의해 결과가 완료된 ONCE를 위해 먼저 적용되어야 하고, 실제로 업데이트를 수행하기 위해 (WHERE를 통해) 가입되어야 합니다.

언급URL : https://stackoverflow.com/questions/10091788/will-sql-update-affect-its-subquery-during-the-update-run