Deadlock found when trying to get lock; try restarting transaction

원인

• 잠금 순서의 불일치:

  트랜잭션 A가 리소스 X에 잠금을 걸고 리소스 Y를 요청합니다.

  트랜잭션 B가 리소스 Y에 잠금을 걸고 리소스 X를 요청합니다.

  두 트랜잭션 모두 상대방이 가진 잠금이 풀리기를 영원히 기다리게 됩니다.

• 긴 트랜잭션 시간:

  트랜잭션이 너무 오래 실행되면, 해당 트랜잭션이 보유하고 있는 잠금 때문에 다른 트랜잭션들이 리소스에 접근하지 못하고 대기하게 되어 교착 상태의 위험이 커집니다.

• 부적절한 인덱스:

  쿼리가 적절한 인덱스를 사용하지 못하고 대량의 행을 검사하면서 필요 이상의 많은 행에 잠금을 걸게 될 때 발생할 수 있습니다.

UPDATE 또는 DELETE는 일반적으로 SQL 문 처리에서 스캔되는 모든 인덱스 레코드에 레코드 잠금을 설정한다 … … , WHERE절에 적절한 인덱스를 사용하지 않으면 MySQL은 쿼리를 수행하기 위해 전체 table에 lock을 건다

• 읽기/쓰기 동시성 문제:

  SELECT … FOR UPDATE와 같이 명시적인 잠금을 사용하는 쿼리가 다른 쓰기 작업과 충돌할 때 발생할 수 있습니다.

해결책

• 잠금 순서 통일:

  애플리케이션 전체에서 테이블 또는 레코드를 잠그는 순서를 항상 동일하게 유지합니다. 예를 들어, Table A와 Table B를 동시에 수정해야 할 경우, 모든 트랜잭션은 A를 먼저 잠근 후 B를 잠그도록 규칙을 만듭니다.

• 트랜잭션 최소화 및 단축:

• 적절한 인덱스 사용:

  WHERE 절에서 사용되는 컬럼에 인덱스를 생성하여, 쿼리가 꼭 필요한 레코드에만 잠금을 걸도록 합니다. 인덱스가 없으면 스캔하는 모든 레코드에 잠금을 걸어 교착 상태를 유발할 수 있습니다.

• SELECT FOR UPDATE 사용 최소화:

  꼭 필요한 경우에만 사용하고, 사용 시에는 LIMIT 이나 WHERE 절을 통해 잠금 범위를 최소화합니다.

관련 쿼리

-- 현재 LOCK이 걸려 대기중인 정보
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- LOCK을 건 정보
SELECT * FROM information_schema.INNODB_LOCKS;

-- LOCK을 걸고 있는 프로세스 정보
SELECT * FROM information_schema.INNODB_TRX;

-- 현재 교착 상태 정보
SHOW ENGINE INNODB STATUS;

-- 잠금 대기 시간 제한(초)
SELECT @@innodb_lock_wait_timeout;

-- 교착 상태 자동 감지 기능의 활성화 여부(ON = 1)
SELECT @@innodb_deadlock_detect;

-- 전역 격리 수준
SELECT @@global.tx_isolation;

-- 세션 격리 수준
SELECT @@session.tx_isolation;

-- 현재 트랜잭션 격리 수준(8.0 이후)
SELECT @@transaction_isolation;