mysql 에서 for update를 통해 lock을 걸경우와 postgres에서 걸경우 같을까?

*MySQL (특히 InnoDB)**와 PostgreSQL는 행 잠금과 쓰기 락 처리 방식에서 차이가 있습니다. MySQL과 PostgreSQL은 모두 **MVCC (Multi-Version Concurrency Control)**를 사용하여 데이터의 일관성을 유지하면서 동시성을 처리하지만, 행 잠금(ROW-level locking) 방식에 차이가 있습니다.

MySQL (InnoDB)에서의 행 잠금

InnoDB는 인덱스를 사용한 쿼리에 대해 인덱스 기반의 잠금을 사용합니다. 즉, 특정 인덱스를 통해 WHERE 절에 의해 필터링된 모든 행에 잠금이 걸리며, 이 잠금은 다음과 같은 특성을 가집니다:

• 인덱스 기반 락: InnoDB는 인덱스를 사용하는 쿼리에 대해 잠금을 걸기 때문에, 해당 인덱스를 사용하는 범위 내의 모든 행에 잠금이 걸립니다. 만약 인덱스가 없는 경우 테이블 스캔을 하면서 모든 행에 잠금이 걸릴 수 있습니다.
• 갭 락(Gap Lock): InnoDB는 간격에 대한 락도 걸 수 있습니다. 이로 인해 SELECT ... FOR UPDATE로 특정 행을 선택할 때, 해당 인덱스 범위에 해당하는 존재하지 않는 행에도 락이 걸려 다른 트랜잭션이 새로운 행을 삽입하는 것을 방지합니다.

MySQL InnoDB 예시:

BEGIN;
SELECT * FROM data WHERE member_id = 8 FOR UPDATE;
-- member_id에 인덱스가 있으면 해당 인덱스에 의해 필터링된 모든 행에 락이 걸림.

PostgreSQL에서의 행 잠금

PostgreSQL은 MVCC를 사용하여 보다 정교한 방식으로 락을 처리합니다. PostgreSQL의 기본적인 동작 방식은 다음과 같습니다:

• 실제 업데이트 대상 행만 잠금: PostgreSQL에서 FOR UPDATE는 해당 쿼리로 선택된 행에만 배타적 잠금을 겁니다. 인덱스를 사용하여 필터링된 특정 행에만 잠금이 걸리며, 해당 인덱스 범위 전체에 잠금을 걸지는 않습니다.
• 갭 락 없음: MySQL의 갭 락과 달리, PostgreSQL은 존재하지 않는 행이나 인덱스 범위에 잠금을 걸지 않습니다. 따라서 특정 인덱스 범위 내에서 행이 존재하지 않으면, 그 범위에 새로운 행을 삽입할 수 있습니다.

PostgreSQL 예시:

BEGIN;
SELECT * FROM data WHERE member_id = 8 FOR UPDATE;
-- 인덱스를 사용해 특정 행만 선택하고, 해당 행에만 배타적 잠금이 걸림.

주요 차이점:

1. 인덱스 기반 락:
   • **MySQL (InnoDB)**는 인덱스 기반으로 잠금을 걸어 인덱스 범위 내의 모든 행에 잠금을 겁니다. 심지어 갭 락으로 존재하지 않는 행에 대한 잠금도 걸릴 수 있습니다.
   • PostgreSQL은 선택된 행에만 잠금을 겁니다. 갭 락이나 인덱스 범위 전체에 잠금이 걸리지 않습니다.
2. 갭 락 (Gap Lock):
   • **MySQL (InnoDB)**는 갭 락을 사용하여 존재하지 않는 행에 대한 삽입도 방지할 수 있습니다.
   • PostgreSQL은 갭 락 개념이 없으며, 인덱스 범위 내에서 존재하지 않는 행에 대한 삽입을 허용합니다.
3. 동시성 제어:
   • MySQL은 잠금이 인덱스 범위에 대해 보다 광범위하게 적용될 수 있으므로, 동시성 제어 측면에서 제약이 더 클 수 있습니다.
   • PostgreSQL은 필요한 행에만 잠금을 걸기 때문에 동시성 제어가 더 유연하고 효율적일 수 있습니다.

 

예제:

Postgres,Mysql DB 모두 다 멤버 아이디에 인덱스가 걸렸이다고하자. 

그리고 둘다 member_id,send_type칼럼이 있고 send_type 내용 분포는 'send','notsend' 있다고치자 이럴때 'select * from data where member_id=8'을 할경우 mysql은 send_type이 'send','notsend' 상관없이 member_id가 인덱스이기 때문에 member_id=8인 모든 로우가 lock이 걸리고, postgres는 조회된 부분만 lock걸리는가?

 

맞습니다. 두 데이터베이스(MySQL과 PostgreSQL)의 동작 방식에 따라 다음과 같은 차이가 있습니다.

MySQL (InnoDB)

1. 인덱스 기반 잠금: SELECT * FROM data WHERE member_id=8; 쿼리를 실행할 때, member_id에 인덱스가 걸려있다면 MySQL은 member_id가 8인 모든 행에 대해 잠금을 겁니다.
2. send_type 값 무관: MySQL은 member_id=8에 해당하는 모든 행을 잠금 처리하며, 그 행의 send_type 값이 **'send'**이든 **'notsend'**이든 상관없이 잠금이 발생합니다.

PostgreSQL

1. 행 잠금: SELECT * FROM data WHERE member_id=8 FOR UPDATE; 쿼리를 실행하면, PostgreSQL은 member_id가 8인 특정 행에 대해서만 잠금을 겁니다.
2. send_type 값 무관: PostgreSQL도 마찬가지로 member_id=8에 해당하는 모든 행이 존재할 경우, 그 행들에 대해서만 잠금이 걸리며, send_type의 값이 **'send'**이든 **'notsend'**이든 관계없이 조회된 행에만 잠금이 적용됩니다.

요약

• MySQL에서는 member_id=8에 해당하는 모든 행에 대해 잠금이 걸립니다. 즉, send_type의 값에 관계없이 모든 로우가 영향을 받습니다.
• PostgreSQL에서는 member_id=8에 해당하는 행만 잠금이 걸립니다. 즉, send_type의 값에 관계없이 조회된 행에만 락이 적용됩니다.

이러한 차이는 동시성 제어와 성능에 중요한 영향을 미칩니다. PostgreSQL은 세밀한 행 잠금을 통해 더 많은 동시성을 허용하고, MySQL은 광범위한 잠금으로 인해 잠금 경합이 더 자주 발생할 수 있습니다.