MySQL 환경에서 FK 설정으로 인한 제품에 대한 리뷰 생성시 발생하는 동시성 이슈를 해결한다
주의사항(Optional)
다양한 방법으로 해결할 수 있으니 이를 비교해보고 프로젝트 환경에 맞게 적용한다
문제상황
Mysql을 이용하는 DB에서 FK가 걸린 테이블을 insert할 때 부모 관계가 delete되거나 수정되면 안되기 때문에 부모 관계의 row에 s-lock을 걸도록 하기 때문에 DeadLock 문제가 발생하여 2명의 회원이 1개의 제품에 대해서 리뷰를 동시에 남기면 1명은 실패하게 된다.
실제 문제 발생 상황(실제로 2가지 요청을 동시에 요청했을 때)
was log
db Log(Dead Lock이 발생함을 알 수 있음)
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-11-02 16:11:58 281472825409536
*** (1) TRANSACTION:
TRANSACTION 165007, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 2
MySQL thread id 4631, OS thread handle 281471879008256, query id 68625 ip-192-168-1-75.ap-northeast-2.compute.internal 192.168.1.75 f12 updating
update product set avg_rating=(total_rating+3)/cast(review_count+1 as double precision), review_count=review_count+1, total_rating=total_rating+3 where id=168
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 98 page no 11 n bits 128 index PRIMARY of table `f12`.`product` trx id 165007 lock mode S locks rec but not gap
Record lock, heap no 47 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 8; hex 80000000000000a8; asc ;;
1: len 6; hex 000000027dcd; asc } ;;
2: len 7; hex 82000000b80c4e; asc N;;
3: len 8; hex 534f465457415245; asc SOFTWARE;;
4: len 30; hex 68747470733a2f2f73747564696f33742e636f6d2f77702d636f6e74656e; asc https://studio3t.com/wp-conten; (total 77 bytes);
5: len 9; hex 53747564696f203354; asc Studio 3T;;
6: len 4; hex 80000001; asc ;;
7: len 4; hex 80000004; asc ;;
8: len 8; hex 0000000000001040; asc @;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 98 page no 11 n bits 128 index PRIMARY of table `f12`.`product` trx id 165007 lock_mode X locks rec but not gap waiting
Record lock, heap no 47 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 8; hex 80000000000000a8; asc ;;
1: len 6; hex 000000027dcd; asc } ;;
2: len 7; hex 82000000b80c4e; asc N;;
3: len 8; hex 534f465457415245; asc SOFTWARE;;
4: len 30; hex 68747470733a2f2f73747564696f33742e636f6d2f77702d636f6e74656e; asc https://studio3t.com/wp-conten; (total 77 bytes);
5: len 9; hex 53747564696f203354; asc Studio 3T;;
6: len 4; hex 80000001; asc ;;
7: len 4; hex 80000004; asc ;;
8: len 8; hex 0000000000001040; asc @;;
*** (2) TRANSACTION:
TRANSACTION 165008, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 2
MySQL thread id 4632, OS thread handle 281472230445056, query id 68626 ip-192-168-1-75.ap-northeast-2.compute.internal 192.168.1.75 f12 updating
update product set avg_rating=(total_rating+4)/cast(review_count+1 as double precision), review_count=review_count+1, total_rating=total_rating+4 where id=168
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 98 page no 11 n bits 128 index PRIMARY of table `f12`.`product` trx id 165008 lock mode S locks rec but not gap
Record lock, heap no 47 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 8; hex 80000000000000a8; asc ;;
1: len 6; hex 000000027dcd; asc } ;;
2: len 7; hex 82000000b80c4e; asc N;;
3: len 8; hex 534f465457415245; asc SOFTWARE;;
4: len 30; hex 68747470733a2f2f73747564696f33742e636f6d2f77702d636f6e74656e; asc https://studio3t.com/wp-conten; (total 77 bytes);
5: len 9; hex 53747564696f203354; asc Studio 3T;;
6: len 4; hex 80000001; asc ;;
7: len 4; hex 80000004; asc ;;
8: len 8; hex 0000000000001040; asc @;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 98 page no 11 n bits 128 index PRIMARY of table `f12`.`product` trx id 165008 lock_mode X locks rec but not gap waiting
Record lock, heap no 47 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 8; hex 80000000000000a8; asc ;;
1: len 6; hex 000000027dcd; asc } ;;
2: len 7; hex 82000000b80c4e; asc N;;
3: len 8; hex 534f465457415245; asc SOFTWARE;;
4: len 30; hex 68747470733a2f2f73747564696f33742e636f6d2f77702d636f6e74656e; asc https://studio3t.com/wp-conten; (total 77 bytes);
5: len 9; hex 53747564696f203354; asc Studio 3T;;
6: len 4; hex 80000001; asc ;;
7: len 4; hex 80000004; asc ;;
8: len 8; hex 0000000000001040; asc @;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 165010
Purge done for trx's n:o < 165010 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 562948197020456, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 562948197019648, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 562948197018840, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 562948197018032, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 562948197017224, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
해결방법
Database에서 Product와 Review의 FK 관계를 제거한다.
장점: lock이 걸리는 범위가 줄어들기 때문에 성능상으로 이익이 있다.
단점: 만약 리뷰를 남기기전에 Product가 삭제된다면 없는 Product에 대해서 리뷰를 남길 수 있게된다.
상황: F12 프로젝트에서 Product에 대한 delete될 상황이 없다고 판단
미리 리뷰를 남길 제품에 X-lock을 건다
장점: 데이터의 정합성이 보장된다.
단점: 리뷰를 동시에 남기게된다면 한 사용자는 대기하고 있어야 하기에 성능상 문제가 발생할 수 있다. 참고로 현재 1개의 제품에 대해서는 모든 사용자가 접근하여 리뷰를 남길 수 있기 때문에 문제가 크리티컬할 수 있다.
트랜잭션을 분리하여 처리한다
장점: 리뷰를 생성하는 로직과 제품을 업데이트하는 트랜잭션이 분리되어 처리된다.
단점: 만약에 제품의 reviewCount를 업데이트하는 과정이 실패한다면, 최종적으로 데이터의 정합성이 안맞을 수 있기에, 이를 추후에 다시 맞춰주는 로직이 필요하다.
목표
주의사항(Optional)
문제상황
Mysql을 이용하는 DB에서 FK가 걸린 테이블을 insert할 때 부모 관계가 delete되거나 수정되면 안되기 때문에 부모 관계의 row에 s-lock을 걸도록 하기 때문에 DeadLock 문제가 발생하여 2명의 회원이 1개의 제품에 대해서 리뷰를 동시에 남기면 1명은 실패하게 된다.
실제 문제 발생 상황(실제로 2가지 요청을 동시에 요청했을 때)
was log
db Log(Dead Lock이 발생함을 알 수 있음)
해결방법
Database에서 Product와 Review의 FK 관계를 제거한다. 장점: lock이 걸리는 범위가 줄어들기 때문에 성능상으로 이익이 있다. 단점: 만약 리뷰를 남기기전에 Product가 삭제된다면 없는 Product에 대해서 리뷰를 남길 수 있게된다. 상황: F12 프로젝트에서 Product에 대한 delete될 상황이 없다고 판단
미리 리뷰를 남길 제품에 X-lock을 건다 장점: 데이터의 정합성이 보장된다. 단점: 리뷰를 동시에 남기게된다면 한 사용자는 대기하고 있어야 하기에 성능상 문제가 발생할 수 있다. 참고로 현재 1개의 제품에 대해서는 모든 사용자가 접근하여 리뷰를 남길 수 있기 때문에 문제가 크리티컬할 수 있다.
트랜잭션을 분리하여 처리한다 장점: 리뷰를 생성하는 로직과 제품을 업데이트하는 트랜잭션이 분리되어 처리된다. 단점: 만약에 제품의 reviewCount를 업데이트하는 과정이 실패한다면, 최종적으로 데이터의 정합성이 안맞을 수 있기에, 이를 추후에 다시 맞춰주는 로직이 필요하다.