Database/SQL

๋น„๊ด€์  ๋™์‹œ์„ฑ ์ œ์–ด๋ฅผ ์ ์šฉํ•  ๊ฒฝ์šฐ ๋ฝ์ด ๊ฑธ๋ฆฐ row๋Š” ์กฐํšŒ๊ฐ€ ์•ˆ ๋˜๋Š” ๊ฑธ๊นŒ?

์ฑ”๐Ÿป 2024. 2. 12. 19:26

โœ”๏ธย ๊ฒฐ๋ก 

Repeatable Read๋ฅผ ์‚ฌ์šฉ ์ค‘์ด๋ผ๋ฉด MVCC ๋•๋ถ„์— row์— X-Lock์„ ๊ฑธ์–ด๋„ ๊ฐ€์žฅ ์ตœ๊ทผ ์ปค๋ฐ‹๋œ ๋ฐ์ดํ„ฐ๋ฅผ ์ฝ์–ด์™€์„œ Lock์ด ๊ฑธ๋ฆฐ ๊ฐ™์€ row๋ผ๋„ ์กฐํšŒ๊ฐ€ ๊ฐ€๋Šฅํ•˜๋‹ค.

๐Ÿ‘€ย ๋ฌธ์ œ ์ •์˜

์ƒํ™ฉ: ์ฃผ๋ฌธ์žฌ๊ณ ๊ฐ€ ๋งˆ์ด๋„ˆ์Šค๊ฐ€ ๋˜๋Š” ๋ฌธ์ œ๋ฅผ ํ•ด๊ฒฐํ•˜๋ ค๊ณ  ๋น„๊ด€์  ๋™์‹œ์„ฑ ์ œ์–ด(select for update)๋ฅผ ์ ์šฉํ•จ

๋ฐฅ ๋จน๋‹ค๊ฐ€ ๋ฌธ๋“ ์ƒ๊ฐ๋‚œ ๊ฒŒ ๋น„๊ด€๋ฝ์ด ๋ฝ์„ ๋„ˆ๋ฌด ์˜ค๋ž˜ ์žก๊ณ  ์žˆ์–ด์„œ ๋น„ํšจ์œจ์ ์ด๋ผ๋Š”๋ฐ.. ์–ด๋””๊ฐ€ ๋น„ํšจ์œจ์ ์ด๋ผ๋Š” ๊ฑฐ์ง€? ๋น„๊ด€๋ฝ์œผ๋กœ X-Lock์„ ๊ฑธ์–ด์„œ ์กฐํšŒ๋„ ์•ˆ ๋œ๋‹ค๋Š” ๊ฑด๊ฐ€?

์กฐํšŒ์— ๋ฝ์„ ๊ฑธ๊ธดํ•˜์ง€๋งŒ ์žฌ๊ณ ์ˆ˜๋Ÿ‰ ๊ฒ€์ฆ ํ›„์— ๋ฐ”๋กœ update ์ฟผ๋ฆฌ๋ฅผ ์ˆ˜ํ–‰ํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์–ด์ฐจํ”ผ X-Lock์ด ๊ฑธ๋ฆฌ๊ณ  ๊ทธ ํ…€์ด ์งง์ง€ ์•Š์•„์„œ ์–ด์ฐจํ”ผ ๋ฝ์„ ๊ฑฐ๋‚˜ ์•ˆ ๊ฑฐ๋‚˜ ์„ฑ๋Šฅ์ฐจ์ด๋Š” ๊ทธ๋ ‡๊ฒŒ ํฌ์ง€ ์•Š์„ ๊ฒƒ ๊ฐ™๋‹ค๋Š” ์ƒ๊ฐ์„ ํ–ˆ๋‹ค.

์˜ˆ๋ฅผ๋“ค์–ด ๋‚ด๊ฐ€ ์ƒํ’ˆ์ฝ”๋“œ 10001๋ฒˆ์˜ ์ปจ๋ฒ„์Šค๋ฅผ ์ฃผ๋ฌธํ•˜๋ ค๊ณ  ํ•˜๋Š”๋ฐ, ์žฌ๊ณ ์ˆ˜๋Ÿ‰์„ ์กฐํšŒ๋ฅผ ํ•ด์˜ค๋ฉด์„œ ํ•ด๋‹น row์— ๋ฝ์„ ๊ฑธ๊ณ , ์ฃผ๋ฌธ์„ ํ•˜๋Š” ๋™์•ˆ ๋‹ค๋ฅธ ์‚ฌ์šฉ์ž๊ฐ€ 10001๋ฒˆ์˜ ์ƒํ’ˆ์„ ์ฃผ๋ฌธํ•˜๋ ค๊ณ ํ•˜๋ฉด(์ด๋•Œ ์žฌ๊ณ ์ˆ˜๋Ÿ‰๋„ ์กฐํšŒํ•ด์˜ด) X-Lock์ด ๊ฑธ๋ ค์žˆ์–ด์„œ ์กฐํšŒ๋„ ์•ˆ ๋˜์–ด์„œ ์„ฑ๋Šฅ์ด ์•ˆ ์ข‹๋‹ค๋Š” ๊ฑด๊ฐ€?

๊ทธ๋Ÿผ ์—„์ฒญ๋‚œ ์„ฑ๋Šฅ์ด ์•ˆ ์ข‹์€ ๊ฒŒ ๋งž๋‹คโ€ฆ ์ฃผ๋ฌธํ•  ๋•Œ๋งˆ๋‹ค ๋ฝ์ด ๊ฑธ๋ฆดํ…Œ๊ณ  ๊ทธ ์ƒํ’ˆ์„ ์กฐํšŒํ•˜๋Š” ์‚ฌ์šฉ์ž๋Š” ์—„์ฒญ๋‚œ ๋ถˆํŽธํ•จ์„ ๊ฒช์„ํ…Œ๋‹ˆ๊นŒ

โ“ย ๋ถ„์„&์‹œ๋„

์„ธ์…˜1: opt_comb_no์ธ row์— ๋ฐฐํƒ€๋ฝ์„ ๊ฑด๋‹ค.

start TRANSACTION;

SELECT PO.PROD_ID, PO.OPT_COMB_NO, PO.INV_QTY
FROM PROD_OPT PO
WHERE OPT_COMB_NO = 1
FOR UPDATE;

์„ธ์…˜2: ์„ธ์…˜1์—์„œ ๋ฝ์„ ๊ฑด row๋ฅผ ์กฐํšŒ๋ฅผ ํ•˜๋ ค๊ณ  ํ•œ๋‹ค.

start TRANSACTION;

select * from prod_opt where OPT_COMB_NO = 1;

์œ„์˜ ๊ฐ€์ •๋Œ€๋กœ๋ผ๋ฉด ์„ธ์…˜1์—์„œ ๊ฐ™์€ row๋ฅผ ์ด๋ฏธ ๋ฝ์„ ๊ฑธ์—ˆ์œผ๋‹ˆ๊นŒ ๋ฝ์ด ํ’€๋ฆด ๋•Œ๊นŒ์ง€ ์กฐํšŒ๊ฐ€ ์•ˆ ๋  ์ค„ ์•Œ์•˜์œผ๋‚˜ ์„ธ์…˜2์—์„œ ์กฐํšŒ๊ฐ€ ๋„ˆ๋ฌด ์ž˜ ๋จ

์‹œ๋„1

๋ฝ์ด ์ œ๋Œ€๋กœ ์•ˆ ๊ฑธ๋ฆฐ๊ฑด๊ฐ€? ์‹ถ์–ด์„œ ๋ฝ์„ ์กฐํšŒํ•ด๋ณด์•˜๋‹ค.

# ํ˜„์žฌ ๋ฐœ์ƒ ์ค‘์ธ ๋ชจ๋“  ๋ฐ์ดํ„ฐ ๋ฝ์— ๋Œ€ํ•œ ์ •๋ณด๋ฅผ ์ œ๊ณตํ•œ๋‹ค. ๋ฝ์„ ์žก๊ณ  ์žˆ๋Š” ํŠธ๋žœ์žญ์…˜, ๋ฝ ํƒ€์ž…, ๋ฝ ๋ชจ๋“œ ๋“ฑ ์ƒ์„ธํ•œ ์ •๋ณด๋ฅผ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋‹ค.
SELECT * FROM performance_schema.data_locks;
# ๋ฝ์œผ๋กœ ์ธํ•ด ๋Œ€๊ธฐ ์ค‘์ธ ํŠธ๋žœ์žญ์…˜๋“ค์— ๋Œ€ํ•œ ์ •๋ณด๋ฅผ ์ œ๊ณตํ•œ๋‹ค. ์–ด๋–ค ํŠธ๋žœ์žญ์…˜์ด ์–ด๋–ค ๋ฝ ๋•Œ๋ฌธ์— ๋Œ€๊ธฐ ์ค‘์ธ์ง€, ๋Œ€๊ธฐ ์ค‘์ธ ํŠธ๋žœ์žญ์…˜๊ณผ ๋ฝ์„ ๋ณด์œ ํ•œ ํŠธ๋žœ์žญ์…˜์˜ ์ •๋ณด๋ฅผ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋‹ค.
SELECT * FROM performance_schema.data_lock_waits;

์•„ ์™œ ๋ฝ์ด 2๊ฐœ์ธ๊ฐ€ ํ–ˆ๋”๋‹ˆโ€ฆ. ๊ทธ ํ…Œ์ด๋ธ”๋ฝ ๊ฑด๋‹ค๋Š” ๊ฒŒ ๊ทธ๋ง์ด์—ˆ๊ตฌ๋‚˜? ์นœ์ ˆํ•œ SQL ํŠœ๋‹์— ๋‚˜์˜จ

record ๋ฝ์ด ๊ฑธ๋ฆฐ ๋ถ€๋ถ„์„ ํ™•์ธํ•ด๋ณด๋ฉด(LOCK_TYPE์— RECORD), LOCK_MODE์— โ€˜Xโ€™๋ผ๊ณ  ์ นํ˜€์ ธ์žˆ๋Š” ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋‹ค.

X = X-Lock = Exclusive Lock = ๋ฐฐํƒ€๋ฝ = ์“ฐ๊ธฐ๋ฝ

๋ฝ์€ ์ œ๋Œ€๋กœ ๊ฑธ๋ ค์žˆ๋Š” ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋‹ค. ๊ทธ๋Ÿผ ์™œ ๋ฐฐํƒ€๋ฝ์ธ X๋ฝ์ด ๊ฑธ๋ ค์žˆ๋Š”๋ฐ ์–ด๋–ป๊ฒŒ ์กฐํšŒ๊ฐ€ ๊ฐ€๋Šฅํ•œ ๊ฒƒ์ผ๊นŒ?

๐Ÿ”Žย ์›์ธ

GPT์—๊ฒŒ ๋ฌผ์–ด๋ด์„œ ํžŒํŠธ๋ฅผ ์–ป์„ ์ˆ˜ ์žˆ์—ˆ๋‹ค.

MVCC๋ผ๋Š” ๊ฐœ๋…์€ ์•Œ๊ณ  ์žˆ์—ˆ๋Š”๋ฐ,

MySQL์˜ ๊ธฐ๋ณธ MVCC๋ฅผ ์‚ฌ์šฉํ•œ๋‹ค๋Š” ๊ฒƒ๊ณผ ๋ฝ์˜ ์ƒ๊ด€๊ด€๊ณ„?๋ฅผ ์ž˜ ๋งค์นญ์„ ๋ชป ์‹œ์ผฐ๋˜ ๊ฒƒ ๊ฐ™๋‹ค.

MySQL์˜ ๊ธฐ๋ณธ Isolation Level์€ Repeatable Read์ด๋‹ค. Repeatable Read๋Š” MVCC๋ฅผ ์‚ฌ์šฉํ•˜๊ธฐ ๋•Œ๋ฌธ์— ๋น„๋ก X-Lock(๋ฐฐํƒ€๋ฝ)์ด ๊ฑธ๋ฆฐ ํ–‰์ด ์žˆ๋”๋ผ๋„, ๊ฐ€์žฅ ์ตœ๊ทผ ์ปค๋ฐ‹๋œ ๋ฒ„์ „์„ ์ฝ์–ด์˜ฌ ์ˆ˜ ์žˆ๋‹ค.

์ด๋ž˜์„œ row์— X๋ฝ์ด ๊ฑธ๋ ค์žˆ๋Š” ์ƒํƒœ์—์„œ๋„ ๋‹ค๋ฅธ ํŠธ๋žœ์žญ์…˜์—์„œ ๋ฝ์„ ๊ฑด ํ–‰ ์กฐํšŒ๊ฐ€ ๊ฐ€๋Šฅํ•œ ๊ฒƒ์ด๋‹ค.

๐Ÿ”œย ๋‚˜๊ฐ€๋ฉฐ

์ฝ๊ธฐ๋ฝ/๋ฐฐํƒ€๋ฝ/๋‚™๊ด€๋ฝ์€ ์•Œ๊ณ  ์žˆ์—ˆ๋Š”๋ฐ Exclusive Lock, S-Lock, X-Lock์ด๋ผ๋Š” ์šฉ์–ด๋Š” ์ƒ์†Œํ•ด์„œ ์กฐ๊ธˆ ํ•ด๋ฉจ์—ˆ๋‹ค.

์ด๋ฒˆ ๊ธฐํšŒ๋กœ ๋ฝ ์ข…๋ฅ˜์— ๋Œ€ํ•ด ์•Œ์•„๋ณด๊ณ  ๊ณต๋ถ€ํ•˜๋Š” ๊ณ„๊ธฐ๊ฐ€ ๋˜์—ˆ๋‹ค.

๐Ÿ“–ย Ref


Uploaded by N2T