PostgreSQL ROW LOCK걸기(FOR UPDATE, SKIP LOCKED)

• 아래와 같은 테이블 tbl_entry가 있을 때

id	status	assign
1	100	F
2	100	F
3	100	F
4	200	F
5	200	F

요청이 들어오면 assign = F인 id 오름차순 첫번째 레코드를 가져와서 assign을 T로 바꾸고 해당 레코드의 정보를 화면에 표시한다.

 

• 가져오는 SQL

SELECT * FROM tbl_entry WHERE status = ? AND assign = F OFFSET 0 LIMIT 1

 

• 그런데 동시에 3명의 유저가 같은 동작을 실행하면 모두가 id=1인 레코드를 가져버린다.
• 이걸 방지하기 위해서는 "FOR UPDATE"를 쓰면 되는데 이걸 쓰면 해당 레코드에 락이 걸려서 트랜잭션이 끝날 때까지 기다린다.

 

• 해당 레코드에 락을 거는 SQL

SELECT * FROM tbl_entry WHERE status = ? AND assign = F OFFSET 0 LIMIT 1 FOR UPDATE

 

• 문제는 그 기다린다는 것이 문제이다. assign=T로 바꾸는 작업이 끝나고 트랜잭션이 끝나서야 다음 SELECT가 실행이 된다.
  • update가 실행되고 get이 실행된다. 
  • 관련 로그

2023-07-20 15:29:13.233 DEBUG [YmUx] get start. args: {String : "user_20"}
2023-07-20 15:29:13.621 DEBUG [YWJj] get start. args: {String : "user_61"}
2023-07-20 15:29:13.845 DEBUG [NzQ1] get start. args: {String : "user_172"}
...
생략.
...
2023-07-20 15:29:13.846 DEBUG [NzE4] get start. args: {String : "user_56"}
2023-07-20 15:29:13.848 DEBUG [MDMx] get start. args: {String : "user_136"}
2023-07-20 15:29:15.883 DEBUG [ZjIw] get end. elapsed MS: [2597], return: TblEntry(id=1, status=100, assign=false)
2023-07-20 15:29:16.528 INFO  [ZjIw] update.  entryUser=user_162, id=1, updateCount=1, elapsedMS=3348
2023-07-20 15:29:20.876 DEBUG [YTJm] get start. args: {String : "user_133"}
2023-07-20 15:29:20.989 DEBUG [MWMw] get end. elapsed MS: [7148], return: TblEntry(id=2,  status=100, assign=false)
2023-07-20 15:29:21.226 INFO  [MWMw] update.  entryUser=user_158, id=2, updateCount=1, elapsedMS=7438
2023-07-20 15:29:22.851 DEBUG [YmY2] get start. args: {String : "user_37"}
2023-07-20 15:29:22.862 DEBUG [YmUx] get end. elapsed MS: [9524], return: TblEntry(id=3,  status=100, assign=false)
2023-07-20 15:29:23.050 INFO  [YmUx] update.  entryUser=user_20, id=3, updateCount=1, elapsedMS=9817
2023-07-20 15:29:23.852 DEBUG [YTJm] get end. elapsed MS: [2975], return: TblEntry(id=4,  status=100, assign=false)
2023-07-20 15:29:23.854 DEBUG [YTIw] get start. args: {String : "user_174"}
2023-07-20 15:29:23.883 INFO  [YTJm] update.  entryUser=user_133, id=4, updateCount=1, elapsedMS=3007
2023-07-20 15:29:24.203 DEBUG [YmY2] get end. elapsed MS: [1344], return: TblEntry(id=5,  status=100, assign=false)

 

• 이 문제를 해결할 수 있는 것이 "SKIP LOCKED"이다. PostgreSQL에만 있는 구문인 것 같은데(9.5버전부터), 이것을 추가하면 락이 걸린 레코드는 제외해준다.

 

• 락이 걸린 레코드를 제외하고 레코드를 가져오면서 락을 거는 SQL

SELECT * FROM tbl_entry WHERE status = ? AND assign = F OFFSET 0 LIMIT 1 FOR UPDATE SKIP LOCKED

 

• update가 끝나기를 기다리지 않고 get이 실행되는 것이 보인다.
  • 관련 로그

2023-07-20 16:15:28.693 DEBUG bf1b585ccb1b [MzY3] get start. args: {String : "user_134"}
2023-07-20 16:15:28.994 DEBUG bf1b585ccb1b [MjA1] get start. args: {String : "user_107"}
2023-07-20 16:15:28.995 DEBUG bf1b585ccb1b [ZDUy] get start. args: {String : "user_171"}
...
생략.
...
2023-07-20 16:15:28.996 DEBUG bf1b585ccb1b [Yzhh] get start. args: {String : "user_103"}
2023-07-20 16:15:28.998 DEBUG bf1b585ccb1b [OTA1] get start. args: {String : "user_146"}
2023-07-20 16:15:28.999 DEBUG bf1b585ccb1b [NjEx] get start. args: {String : "user_178"}
2023-07-20 16:15:32.640 DEBUG bf1b585ccb1b [OWQx] get end. elapsed MS: [3775], return: TblEntry(id=1, status=100, assign=false)
2023-07-20 16:15:34.055 DEBUG bf1b585ccb1b [ZWE3] get end. elapsed MS: [5027], return: TblEntry(id=2, status=100, assign=false)
2023-07-20 16:15:34.485 INFO  bf1b585ccb1b [OWQx] update. entryUser=user_118, id=1, updateCount=1, elapsedMS=5672
2023-07-20 16:15:34.525 INFO  bf1b585ccb1b [ZWE3] update. entryUser=user_192, id=2, updateCount=1, elapsedMS=5542
2023-07-20 16:15:34.927 DEBUG bf1b585ccb1b [ZmVh] get end. elapsed MS: [5985], return: TblEntry(id=7, status=100, assign=false)
2023-07-20 16:15:34.999 DEBUG bf1b585ccb1b [YmQ1] get end. elapsed MS: [5991], return: TblEntry(id=4, status=100, assign=false)
2023-07-20 16:15:35.020 DEBUG bf1b585ccb1b [ZDQ5] get end. elapsed MS: [6190], return: TblEntry(id=5, status=100, assign=false)
2023-07-20 16:15:35.190 DEBUG bf1b585ccb1b [MTQ1] get end. elapsed MS: [6374], return: TblEntry(id=3, status=100, assign=false)

 

• 이번 대응으로 아래와 같이 개선되었다.

대응	10분간 처리건수	평균 응답시간(초)	최장응답시간(초)	1초당 처리건수
FOR UPDATE 미적용	1,041	61.978	99.277	1.79
FOR UPDATE 적용	1.706	36.545	62.993	3.03
SKIP LOCKED도 적용	2,219	35.424	54.272	4.06

 

 

• 우선 문제는 해결되었지만 매 리퀘스트마다 DB에 쿼리를 날려야 하는 구조는 완전 별로이다.
  • 하나의 API를 통해서 미리 준비된 값을 하나씩 받는 방식으로 고치는 게 좋을 듯 싶다.
• 참고자료
  • https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE