mysql 교착상태의 원인과 해결방법은 무엇인가요?

여러 트랜잭션이 동시에 동일한 리소스에 대해 잠금을 유지하고 요청하면 교착 상태가 발생하여 순환 종속성이 발생합니다. 해결 방법은 다음과 같습니다. 1. 낮은 격리 수준을 사용합니다. 2. 고정된 순서로 리소스에 액세스합니다. 3. 신중하게 선택한 인덱스를 테이블에 추가합니다. 4. 잠금을 더 적게 사용합니다.

이 튜토리얼의 운영 환경: windows7 시스템, mysql8 버전, Dell G3 컴퓨터.

여러 트랜잭션이 동시에 동일한 리소스에 대한 잠금을 유지하고 요청하면 교착 상태가 발생하여 순환 종속성이 발생합니다. 교착 상태는 트랜잭션이 다른 순서로 리소스를 잠그려고 시도할 때 발생합니다. StockPrice 테이블의 두 트랜잭션을 예로 들어 보겠습니다.

Transaction 1

START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;

Transaction #2

START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET;
COMMIT;

운이 좋지 않으면 각 트랜잭션이 첫 번째 문 실행을 완료하고 프로세스에서 리소스를 잠글 수 있습니다. 그런 다음 각 트랜잭션은 명령문의 두 번째 줄을 실행하려고 시도했지만 잠겨 있음을 발견했습니다. 두 트랜잭션은 교착 상태를 깨야 할 다른 이유가 없는 한 다른 트랜잭션이 완료될 때까지 영원히 기다립니다.

이 문제를 해결하기 위해 데이터베이스는 다양한 교착 상태 감지 및 시간 초과 메커니즘을 구현합니다. InnoDB와 같은 복잡한 스토리지 엔진은 순환 종속성을 표시하고 즉시 오류를 반환합니다. 그렇지 않으면 교착 상태로 인해 쿼리 속도가 매우 느려집니다. 다른 나쁜 습관으로는 시간 초과를 기다렸다가 포기하는 것입니다. InnoDB가 교착 상태를 처리하는 현재 방법은 최소 배타적 행 수준 잠금을 보유한 트랜잭션을 롤백하는 것입니다. (롤백에 대한 가장 간단한 참조 표시기)

잠금 동작은 스토리지 엔진에 의해 순서대로 결정됩니다. 따라서 일부 저장소 엔진은 특정 작업 순서에서 교착 상태가 발생할 수 있지만 다른 저장소 엔진은 그렇지 않을 수 있습니다. 교착 상태에는 두 가지 유형이 있습니다. 실제 데이터 충돌로 인해 피할 수 없는 경우도 있고, 스토리지 엔진 작동 방식으로 인해 발생하는 경우도 있습니다.

트랜잭션 중 하나의 부분 또는 전체 롤백만 교착 상태를 해결할 수 있습니다. 교착 상태는 트랜잭션 시스템의 객관적인 사실이며 설계에서는 교착 상태 처리를 고려해야 합니다. 일부 비즈니스 시스템은 처음부터 트랜잭션을 재시도할 수 있습니다.

교착 상태 처리 방법
교착 상태는 트랜잭션 데이터베이스의 일반적인 문제이지만 트랜잭션을 전혀 실행할 수 없을 정도로 자주 발생하지 않는 한 일반적으로 위험하지 않습니다. 일반적으로 교착 상태로 인해 트랜잭션이 롤백되는 경우 항상 트랜잭션을 재발행할 수 있도록 애플리케이션을 작성해야 합니다.

InnoDB는 자동 행 수준 잠금을 사용합니다. 단일 행만 삽입하거나 삭제하는 트랜잭션의 경우에도 교착 상태가 발생할 수 있습니다. 이는 이러한 작업이 실제로 "작은" 작업이 아니며 삽입되거나 삭제된 행의 인덱스 레코드(아마도 여러 개)에 자동으로 잠금을 설정하기 때문입니다.

다음 기술을 사용하여 교착 상태를 처리하고 교착 상태가 발생할 가능성을 줄일 수 있습니다.

SHOW INNODB STATUS를 사용하여 마지막 교착 상태의 원인을 확인합니다. 이는 교착 상태를 방지하기 위해 애플리케이션을 조정하는 데 도움이 될 수 있습니다.

교착 상태로 인해 거래가 실패할 경우 항상 거래를 재발행할 준비를 하세요. 교착 상태는 위험하지 않습니다. 다시 시도하세요.

거래를 자주 제출하세요. 작은 문제는 갈등이 덜 발생합니다.

잠긴 읽기(SELECT ... FOR UPDATE 또는 ... LOCK IN SHARE MODE)를 사용하는 경우 READ COMMITTED와 같은 낮은 격리 수준을 사용해 보세요.

정해진 순서로 테이블과 행에 액세스하세요. 그러면 트랜잭션이 잘 정의된 쿼리를 형성하고 교착 상태가 발생하지 않습니다.

신중하게 선택한 인덱스를 테이블에 추가하세요. 그러면 쿼리에서 더 적은 수의 인덱스 레코드를 스캔해야 하므로 더 적은 수의 잠금을 설정해야 합니다. EXPLAIN SELECT를 사용하여 MySQL이 귀하의 쿼리에 가장 적합하다고 생각하는 인덱스를 결정하십시오.

자물쇠를 적게 사용하세요. SELECT가 이전 스냅샷에서 데이터를 반환하도록 허용할 수 있는 경우 FOR UPDATE 또는 LOCK IN SHARE MODE 절을 추가하지 마세요. 동일한 트랜잭션 내의 각 지속 읽기는 자체의 새로운 스냅샷에서 읽기 때문에 여기서는 READ COMMITTED 격리 수준을 사용하는 것이 더 좋습니다.

다른 방법이 도움이 되지 않으면 테이블 수준 잠금으로 트랜잭션을 직렬화하세요. 트랜잭션 테이블(예: InnoDB)에서 LOCK TABLES를 사용하는 올바른 방법은 AUTOCOMMIT = 0으로 설정하고 트랜잭션을 명시적으로 커밋할 때까지 UNLOCK TABLES를 호출하지 않는 것입니다. 예를 들어 테이블 t1에 쓰고 테이블 t에서 읽어야 하는 경우 다음과 같이 수행할 수 있습니다.

SET AUTOCOMMIT=0;

LOCK TABLES t1 WRITE, t2 READ, ...;

[do something with tables t1 and t2 here];

COMMIT;

UNLOCK TABLES;

테이블 수준 잠금을 사용하면 트랜잭션이 멋지게 대기열에 추가되고 교착 상태가 방지됩니다.

직렬화된 트랜잭션을 얻는 방법은 단일 행만 포함하는 보조 "세마포어" 테이블을 만드는 것입니다. 다른 테이블에 액세스하기 전에 각 트랜잭션이 해당 행을 업데이트하도록 합니다. 이러한 방식으로 모든 트랜잭션은 순차적으로 발생합니다. InnoDB의 즉각적인 교착 상태 감지 알고리즘은 직렬화된 잠금이 행 수준 잠금이기 때문에 이 경우에도 작동합니다. 교착 상태를 해결하려면 MySQL 테이블 수준 잠금을 사용하는 시간 초과 방법을 사용해야 합니다.

응용 프로그램에서 LOCK TABLES 명령을 사용하세요. AUTOCOMMIT=1이면 MySQL은 InnoDB 테이블 잠금을 설정하지 않습니다.

관련 추천: "mysql 튜토리얼"

위 내용은 mysql 교착상태의 원인과 해결방법은 무엇인가요?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!