mysql 락 팩트 체크

1.pk,인덱스가 아닌 칼럼으로 업데이트 진행 시 업데이트 할 행을 찾기 위해 순회한 레코드에 X락이 걸린다.

-> 다른 블로그들 보면 순회한 레코드에는 S락을 걸고 실제 수정할 레코드에는 X락을 건다고 한다.

 

팩트 체크

1)member 테이블 생성후 아래 데이터를 생성

 

2)A 커넥션에서 오토 커밋 해제 후 아래 쿼리 실행

• id = 3 레코드가 수정됨

UPDATE member
SET age = 24
WHERE NAME = '장보고'

 

3)B커넥션에서 아래 쿼리 실행

• id = 2 레코드에 S락을 획득하려고 시도했다.

SELECT *
FROM member
WHERE id = '2' LOCK IN SHARE MODE;

 

4)결과

• S락을 획득하지 못하는 현상이 발생했다.
• 만약 id = 2 레코드에 S락이 걸려있었으면 B커넥션에서 S락을 획득하려 했을때 락대기 오류가 발생하지 않는다.
• id = 2 레코드에 X락이 걸려있기 때문에 B커넥션에서 S락을 획득하지 못하는것이다.
• S락이 걸려있으면 다른 커넥션에서도 S락을 획득할 수 있다. 하지만 X락이 걸려있으면 다른 커넥션에서 S락을 획득할 수 없다. 이건 다 잘 알것이다.

 

 

---

 

2.조인 update 시 참조 테이블이 자기 자신이면 참조 테이블에서 조회한 레코드를 찾기 위해 순회한 모든 레코드에 X락이 걸린다.

-> 다른 블로그들 보면 참조 테이블에서 조회한 레코드들을 찾기 위해 순회한 레코드에 S 락이 걸린다고함

 

팩트체크

1)A 커넥션에서 오토커밋 해제 후 아래 쿼리 실행

• name = '장보고' 레코드를 찾아 id 를 구한다음 해당 id 키로 update 진행

UPDATE member
INNER JOIN (
	SELECT id
	FROM member
	WHERE NAME = '장보고'
) SUB
ON member.id = sub.id
SET age = '24'

 

2)B커넥션에서 아래 쿼리 실행

SELECT *
FROM member
WHERE id = '2' LOCK IN SHARE MODE;

 

3)결과

• S락을 획득하지 못하는 현상이 발생했다.
• 이 현상을 보면 참조 테이블에서 조회한 레코드를 찾기 위해 순회한 모든 레코드에 X락이 걸린다는걸 증명한다.

 

 

---

 

3.조인 update 시 참조 테이블이 자기 자신이 아니면 이때 참조 테이블에 S락이 걸린다.

 

팩트체크

1)class 라는 테이블 생성 (member 테이블의 id 와 1:1 외래키 관계)

 

 

2)A 커넥션에서 오토커밋 해제 후 아래 쿼리 실행

• class 테이블에서 class_name = '과학' 이라는 레코드를 찾기위해 순회한 모든 레코드에 S락이 걸림

UPDATE member
INNER JOIN (
	SELECT id
	FROM class
	WHERE class_name = '과학'
) SUB
ON member.id = sub.id
SET age = '8888'

 

3)B커넥션에서 아래 쿼리 실행

SELECT *
FROM class
WHERE id = '3' LOCK IN SHARE MODE;

 

4)결과

• 순회했던 레코드에 S락을 획득하려했는데 S락이 정상적으로 획득되는걸 볼 수 있음
• 이 결과를 보면 참조한 테이블에는 S락이 걸린다는걸 알 수 있다.