๊ด€๋ฆฌ ๋ฉ”๋‰ด

Unfazedโ—๏ธ๐ŸŽฏ

๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค MVCC ๋ชจ๋ธ: Oracle, MySQL, PostgreSQL์˜ ๋‹ค์ค‘ ๋ฒ„์ „ ๋™์‹œ์„ฑ ์ œ์–ด ๋ณธ๋ฌธ

Database (๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค)

๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค MVCC ๋ชจ๋ธ: Oracle, MySQL, PostgreSQL์˜ ๋‹ค์ค‘ ๋ฒ„์ „ ๋™์‹œ์„ฑ ์ œ์–ด

9taetae9 2025. 6. 1. 18:15
728x90

DBMS์—์„œ ์—ฌ๋Ÿฌ ํŠธ๋žœ์žญ์…˜์ด ๋™์‹œ์— ๊ฐ™์€ ๋ฐ์ดํ„ฐ์— ์ ‘๊ทผํ•  ๋•Œ ๋ฐœ์ƒํ•  ์ˆ˜ ์žˆ๋Š” ์ถฉ๋Œ์„ ์–ด๋–ป๊ฒŒ ์ฒ˜๋ฆฌํ•  ๊ฒƒ์ธ์ง€(๋™์‹œ์„ฑ ์ œ์–ด)๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์„ฑ๋Šฅ๊ณผ ์‹ ๋ขฐ์„ฑ์„ ๊ฒฐ์ •ํ•˜๋Š” ์ค‘์š”ํ•œ ์š”์†Œ์ž…๋‹ˆ๋‹ค.

๋™์‹œ์„ฑ ์ œ์–ด์˜ ์ ‘๊ทผ ๋ฐฉ์‹

๋™์‹œ์„ฑ ์ œ์–ด๋Š” ํฌ๊ฒŒ ๋‘ ๊ฐ€์ง€ ๋ฐฉ์‹์œผ๋กœ ๊ตฌ๋ถ„๋ฉ๋‹ˆ๋‹ค.

๋น„๊ด€์  ์ž ๊ธˆ(Pessimistic Locking)

  • ์ถฉ๋Œ์ด ๋ฐœ์ƒํ•  ๊ฒƒ์„ ์ „์ œํ•˜๊ณ  ์ž ๊ธˆ์„ ๊ฑธ์–ด ์ถฉ๋Œ์„ ๋ฐฉ์ง€ํ•˜๋Š” ๋ฐฉ์‹
  • ex) Read/Write locks, 2PL(Two-Phase Locking)

๋‚™๊ด€์  ์ž ๊ธˆ(Optimistic Locking)

  • ์ถฉ๋Œ์„ ํ—ˆ์šฉํ•˜๋˜ ํƒ์ง€ํ•˜์—ฌ ์ฒ˜๋ฆฌํ•˜๋Š” ๋ฐฉ์‹
  • ex) Logical clock, MVCC(Multi-Version Concurrency Control)

2PL์˜ ํ•œ๊ณ„

์ „ํ†ต์ ์ธ 2PL(Two-Phase Locking) ๋ฐฉ์‹์—์„œ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์€ ์ž ๊ธˆ ๋ฉ”์ปค๋‹ˆ์ฆ˜์„ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค.

  • ๊ณต์œ  ๋ฝ(Shared Lock): ์ฝ๊ธฐ ์ž‘์—…์šฉ, ๋‹ค๋ฅธ ์ฝ๊ธฐ๋Š” ํ—ˆ์šฉํ•˜์ง€๋งŒ ์“ฐ๊ธฐ๋Š” ์ฐจ๋‹จ
  • ๋ฐฐํƒ€ ๋ฝ(Exclusive Lock): ์“ฐ๊ธฐ ์ž‘์—…์šฉ, ์ฝ๊ธฐ์™€ ์“ฐ๊ธฐ ๋ชจ๋‘ ์ฐจ๋‹จ

Two-Phase Locking

์ด๋Ÿฌํ•œ ์ž ๊ธˆ ๋ฐฉ์‹์€ ๋ฐ์ดํ„ฐ ์ผ๊ด€์„ฑ์„ ๋ณด์žฅํ•˜์ง€๋งŒ, ๋ฝ ๊ฒฝํ•ฉ(lock contention)์œผ๋กœ ์ธํ•œ ์„ฑ๋Šฅ ์ €ํ•˜์™€ ํ™•์žฅ์„ฑ ๋ฌธ์ œ๋ฅผ ์•ผ๊ธฐํ•ฉ๋‹ˆ๋‹ค. ํŠนํžˆ ์ฝ๊ธฐ ์ค‘์‹ฌ์˜ ์›Œํฌ๋กœ๋“œ์—์„œ ๋ถˆํ•„์š”ํ•œ ๋Œ€๊ธฐ์‹œ๊ฐ„์ด ๋ฐœ์ƒํ•˜์—ฌ ์ „์ฒด ์‹œ์Šคํ…œ ์ฒ˜๋ฆฌ๋Ÿ‰์ด ์ €ํ•˜๋ฉ๋‹ˆ๋‹ค.


1. MVCC(Multi-Version Concurrency Control)

MVCC๋Š” ์ด๋Ÿฌํ•œ ์ „ํ†ต์ ์ธ ๋ฝ ๊ธฐ๋ฐ˜ ๋™์‹œ์„ฑ ์ œ์–ด์˜ ํ•œ๊ณ„๋ฅผ ๊ทน๋ณตํ•˜๊ธฐ ์œ„ํ•ด ๋“ฑ์žฅํ•œ ๊ธฐ์ˆ ์ž…๋‹ˆ๋‹ค. ๊ธฐ๋ณธ ์›๋ฆฌ๋Š” ํ•˜๋‚˜์˜ ๋ฐ์ดํ„ฐ ํ–‰์— ๋Œ€ํ•ด ์—ฌ๋Ÿฌ ๋ฒ„์ „์„ ๋™์‹œ์— ์œ ์ง€ํ•˜์—ฌ, ์ฝ๊ธฐ ์ž‘์—…๊ณผ ์“ฐ๊ธฐ ์ž‘์—… ๊ฐ„์˜ ์ฐจ๋‹จ(block)๋˜๋Š” ์ƒํ™ฉ์„ ํ•ด๊ฒฐํ•˜๋Š” ๊ฒƒ์ž…๋‹ˆ๋‹ค. 

 

MVCC์˜ ์ฃผ์š” ํŠน์ง•

  • ์ฝ๊ธฐ๋Š” ์“ฐ๊ธฐ๋ฅผ ์ฐจ๋‹จํ•˜์ง€ ์•Š์Œ
  • ์“ฐ๊ธฐ๋Š” ์ฝ๊ธฐ๋ฅผ ์ฐจ๋‹จํ•˜์ง€ ์•Š์Œ
  • ํŠธ๋žœ์žญ์…˜ ๊ฒฉ๋ฆฌ์„ฑ ๋ณด์žฅ
  • ์Šค๋ƒ…์ƒท ๊ฒฉ๋ฆฌ๋ฅผ ํ†ตํ•œ ์ผ๊ด€๋œ ์ฝ๊ธฐ ์ œ๊ณต

MVCC์˜ ์ฃผ์š” ์ด์ 

  1. ๋ฝ ๊ฒฝํ•ฉ ์ตœ์†Œํ™”๋กœ ๋™์‹œ์„ฑ ํ–ฅ์ƒ
  2. ๋ฐ๋“œ๋ฝ ๋ฐœ์ƒ ๊ฐ€๋Šฅ์„ฑ ๊ฐ์†Œ
  3. ์ฝ๊ธฐ ์„ฑ๋Šฅ ๊ฐœ์„ 
  4. ํŠธ๋žœ์žญ์…˜ ๊ฒฉ๋ฆฌ์„ฑ์„ ์œ ์ง€ํ•˜๋ฉด์„œ๋„ ์„ฑ๋Šฅ ์ €ํ•˜ ์ตœ์†Œํ™”

๋‹ค์–‘ํ•œ ๊ตฌํ˜„ ๋ฐฉ์‹

MVCC๋Š” Oracle, MySQL(InnoDB), PostgreSQL ๋“ฑ ์ฃผ์š” RDBMS์—์„œ ๋„๋ฆฌ ์ฑ„ํƒ๋˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค.

๊ฐ DBMS๋Š” ์„œ๋กœ ๋‹ค๋ฅธ ์ฒ ํ•™๊ณผ ๊ตฌํ˜„ ๋ฐฉ์‹์„ ํ†ตํ•ด MVCC๋ฅผ ๊ตฌํ˜„ํ•˜๊ณ  ์žˆ์œผ๋ฉฐ, ์ด๋Š” ๊ฐ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ์„ฑ๋Šฅ ํŠน์„ฑ๊ณผ ์šด์˜ ๋ฐฉ์‹์— ์˜ํ–ฅ์„ ๋ฏธ์นฉ๋‹ˆ๋‹ค.

๋ณธ ๊ธ€์—์„œ๋Š” ์ด๋“ค ์ฃผ์š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ MVCC ๊ตฌํ˜„ ๋ฐฉ์‹์„ ๋น„๊ต ๋ถ„์„ํ•˜๊ณ , ๊ฐ๊ฐ์˜ ํŠธ๋ ˆ์ด๋“œ์˜คํ”„๋ฅผ ์ข…ํ•ฉ์ ์œผ๋กœ ์‚ดํŽด๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

 

2. Oracle์˜ MVCC: Undo Segment, SCN ๊ธฐ๋ฐ˜ ์‹œ์Šคํ…œ

Oracle์€ ๋ฐ์ดํ„ฐ๋ฅผ ๋‘ ๊ฐ€์ง€ ๋ชจ๋“œ๋กœ ์ฝ์Šต๋‹ˆ๋‹ค.

Current ๋ชจ๋“œ: ๋””์Šคํฌ์—์„œ ๋ฒ„ํผ ์บ์‹œ๋กœ ์ ์žฌ๋œ ์›๋ณธ ๋ธ”๋ก์„ ํ˜„์žฌ ์ƒํƒœ ๊ทธ๋Œ€๋กœ ์ฝ๋Š” ๋ฐฉ์‹

Consistent ๋ชจ๋“œ:  ์ฟผ๋ฆฌ ์‹œ์ž‘ ์‹œ์ ์˜ ์ผ๊ด€๋œ ๋ฐ์ดํ„ฐ๋ฅผ ์ œ๊ณตํ•˜๊ธฐ ์œ„ํ•ด SCN(System Commit Number)์„ ํ™œ์šฉ

Oracle์˜ SCN ๋ฉ”์ปค๋‹ˆ์ฆ˜

์‹œ์Šคํ…œ ์ „์ฒด์—์„œ ๋งˆ์ง€๋ง‰ ์ปค๋ฐ‹์ด ๋ฐœ์ƒํ•œ ์‹œ์ ์„ Global ๋ณ€์ˆ˜๋กœ ๊ด€๋ฆฌํ•˜๋ฉฐ, ๋ชจ๋“  ๋ธ”๋ก ํ—ค๋”์—๋„ ํ•ด๋‹น ๋ธ”๋ก์ด ๋งˆ์ง€๋ง‰์œผ๋กœ ๋ณ€๊ฒฝ๋œ ์‹œ์ ์˜ SCN์„ ๊ธฐ๋กํ•ฉ๋‹ˆ๋‹ค. ์ฟผ๋ฆฌ ์‹คํ–‰ ์‹œ ์ฟผ๋ฆฌ SCN๊ณผ ๋ธ”๋ก SCN์„ ๋น„๊ตํ•˜์—ฌ, ๋ธ”๋ก SCN์ด ์ฟผ๋ฆฌ SCN๋ณด๋‹ค ํฐ ๊ฒฝ์šฐ CR(Consistent Read) ๋ธ”๋ก์„ ์ƒ์„ฑํ•˜๊ณ  Undo ๋ฐ์ดํ„ฐ๋ฅผ ์ ์šฉํ•ฉ๋‹ˆ๋‹ค. ์ฆ‰, ๋ณ€๊ฒฝ์ด ๋ฐœ์ƒํ•œ ๋ธ”๋ก์ด ์žˆ์„ ๋•Œ๋Š” ํ˜„์žฌ์˜ Current ๋ธ”๋ก์œผ๋กœ๋ถ€ํ„ฐ CR ๋ธ”๋ก์„ ์ƒ์„ฑํ•ด์„œ ์ฟผ๋ฆฌ๊ฐ€ ์‹œ์ž‘๋œ ์‹œ์ ์œผ๋กœ ๋˜๋Œ๋ฆฐ ํ›„ ํ•ด๋‹น ๋ธ”๋ก์„ ์ฝ์Šต๋‹ˆ๋‹ค. 

 

SCN(System Commit Number)

- ์ผ์ข…์˜ ์‹œ๊ฐ„ ์ •๋ณด๋กœ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ์ผ๊ด€์„ฑ ์žˆ๋Š” ์ƒํƒœ๋ฅผ ์‹๋ณ„ํ•˜๋Š”๋ฐ ์‚ฌ์šฉ,

- ์‚ฌ์šฉ์ž๊ฐ€ ์ปค๋ฐ‹ํ•  ๋•Œ๋งˆ๋‹ค 1์”ฉ ์ฆ๊ฐ€ ๋˜๋Š” (์ปค๋ฐ‹์ด ์—†์–ด๋„) ์˜ค๋ผํด ๋ฐฑ๊ทธ๋ผ์šด๋“œ ํ”„๋กœ์„ธ์Šค์— ์˜ํ•ด ์กฐ๊ธˆ์”ฉ ์ฆ๊ฐ€

Current ๋ธ”๋ก: ๋””์Šคํฌ๋กœ๋ถ€ํ„ฐ ์ฝํžŒ ํ›„ ์‚ฌ์šฉ์ž์˜ ๊ฐฑ์‹ ์‚ฌํ•ญ์ด ๋ฐ˜์˜๋œ ์ตœ์ข… ์ƒํƒœ์˜ ์›๋ณธ ๋ธ”๋ก

CR ๋ธ”๋ก: Current ๋ธ”๋ก์— ๋Œ€ํ•œ ๋ณต์‚ฌ๋ณธ

CR ๋ธ”๋ก์€ Current ๋ธ”๋ก์— ๋Œ€ํ•œ ๋ณต์‚ฌ๋ณธ์œผ๋กœ CR ๋ธ”๋ก์€ ์—ฌ๋Ÿฌ ๋ฒ„์ „์ด ์กด์žฌ ๊ฐ€๋Šฅํ•˜๋‚˜ Current ๋ธ”๋ก์€ ์˜ค์ง ํ•˜๋‚˜์ž…๋‹ˆ๋‹ค.

์œ„ ๊ทธ๋ฆผ์„ ํ†ตํ•ด Oracle์˜ MVCC ๋ฉ”์ปค๋‹ˆ์ฆ˜ ๋™์ž‘ ๊ณผ์ •์„ ๋” ์ƒ์„ธํžˆ ํŒŒ์•…ํ•ด ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

์ƒํ™ฉ: ์ฟผ๋ฆฌ SCN 123์œผ๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ์ฝ๊ธฐ

๋ชฉํ‘œ: ํ•ด๋‹น ์‹œ์ ์˜ ์ผ๊ด€๋œ ๋ฐ์ดํ„ฐ๋ฅผ ์ฝ์–ด์•ผ ํ•จ

 

๋ธ”๋ก๋ณ„ ์ฒ˜๋ฆฌ ๊ณผ์ •

1๋ฒˆ ๋ธ”๋ก (SCN 121), 2๋ฒˆ ๋ธ”๋ก (SCN 121) - Current ๋ธ”๋ก ์ง์ ‘ ์ฝ๊ธฐ

Current ๋ธ”๋ก SCN(121) โ‰ค ์ฟผ๋ฆฌ SCN (123) : Current ๋ธ”๋ก์€ ์ฟผ๋ฆฌ ์‹œ์ž‘ ์ „์— ์ปค๋ฐ‹๋œ ๋ฐ์ดํ„ฐ

 

3๋ฒˆ ๋ธ”๋ก (SCN 138) - CR ๋ธ”๋ก ์ƒ์„ฑ ํ•„์š”

Current ๋ธ”๋ก SCN (138) > ์ฟผ๋ฆฌ SCN (123): Current ๋ธ”๋ก์ด ์ฟผ๋ฆฌ ์‹œ์ž‘ ํ›„ ๋ณ€๊ฒฝ๋˜์—ˆ์Œ

1) Undo Segment์—์„œ ์ด์ „ ๋ฒ„์ „ ๋ณต๊ตฌ

2) SCN 123 ์‹œ์ ์˜ CR ๋ธ”๋ก ์ƒ์„ฑ

3) CR ๋ธ”๋ก ์ฝ๊ธฐ

 

4๋ฒˆ ๋ธ”๋ก (SCN 98) - Current ๋ธ”๋ก ์ง์ ‘ ์ฝ๊ธฐ

Current ๋ธ”๋ก SCN (98) โ‰ค ์ฟผ๋ฆฌ SCN (123) : Current ๋ธ”๋ก์€ ์ฟผ๋ฆฌ ์‹œ์ž‘ ์ „์— ์ปค๋ฐ‹๋œ ๋ฐ์ดํ„ฐ

 

์œ„์™€ ๊ฐ™์€ ๋ฐฉ์‹์œผ๋กœ 5๋ฒˆ ๋ธ”๋ก(SCN129)๋„ CR ๋ธ”๋ก์ด ์ƒ์„ฑ๋˜๊ณ , 6,7๋ฒˆ ๋ธ”๋ก์€ Current ๋ธ”๋ก์„ ์ง์ ‘ ์ฝ์Šต๋‹ˆ๋‹ค.

 

๋‹ค์‹œ ํ•œ๋ฒˆ Consistent ๋ชจ๋“œ ์ฝ๊ธฐ์™€ Current ๋ชจ๋“œ ์ฝ๊ธฐ์˜ ์ฐจ์ด๋ฅผ ์ •๋ฆฌํ•ด ๋ณด๋ฉด,

Consistent ๋ชจ๋“œ ์ฝ๊ธฐ๋Š” SCN ํ™•์ธ ๊ณผ์ •์„ ๊ฑฐ์น˜๋ฉฐ ์ฟผ๋ฆฌ๊ฐ€ ์‹œ์ž‘๋œ ์‹œ์ ์„ ๊ธฐ์ค€์œผ๋กœ ์ผ๊ด€์„ฑ ์žˆ๋Š” ์ƒํƒœ๋กœ ๋ธ”๋ก์„ ์ ‘๊ทผํ•˜๊ธฐ ๋•Œ๋ฌธ์— ํ•ญ์ƒ ์ฟผ๋ฆฌ๊ฐ€ ์‹œ์ž‘๋œ ์‹œ์ ์˜ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜ต๋‹ˆ๋‹ค.

Current ๋ชจ๋“œ ์ฝ๊ธฐ๋Š” SQL๋ฌธ์ด ์‹œ์ž‘๋œ ์‹œ์ ์ด ์•„๋‹ˆ๋ผ ๋ฐ์ดํ„ฐ๋ฅผ ์ฐพ์•„๊ฐ„ ์‹œ์ ์˜ ์ตœ์ข… ๊ฐ’์„ ์ฝ๊ธฐ ์œ„ํ•ด ๋ธ”๋ก์„ ์ ‘๊ทผํ•ฉ๋‹ˆ๋‹ค. ๋ธ”๋ก SCN์ด ์ฟผ๋ฆฌ SCN์„ ๋น„๊ตํ•˜์ง€ ์•Š์œผ๋ฉฐ, ์ปค๋ฐ‹๋œ ๊ฐ’์ด๋ผ๋ฉด ๊ทธ๋Œ€๋กœ ์ฝ์Šต๋‹ˆ๋‹ค.  

 

์ด๋ ‡๊ฒŒ Oracle์€ Consistent ๋ชจ๋“œ๋กœ ๋Œ€์ƒ ๋ ˆ์ฝ”๋“œ๋ฅผ ์ฐพ๊ณ , ์ดํ›„ Current ๋ชจ๋“œ๋กœ ์›๋ณธ ๋ธ”๋ก์—์„œ ์‹ค์ œ ๋ณ€๊ฒฝ ์ž‘์—…์„ ์ˆ˜ํ–‰ํ•˜๋Š” ๋ฐฉ์‹์œผ๋กœ ๋ฌธ์žฅ ์ˆ˜์ค€ ์ฝ๊ธฐ ์ผ๊ด€์„ฑ์„ ๋ณด์žฅํ•ฉ๋‹ˆ๋‹ค. 

 

3. MySQL InnoDB์˜ MVCC: Undo Log

MySQL InnoDB๋Š” ์–ธ๋‘ ๋กœ๊ทธ(Undo Log)๋ฅผ ํ•ต์‹ฌ์œผ๋กœ ํ•˜๋Š” MVCC๋ฅผ ๊ตฌํ˜„ํ•ฉ๋‹ˆ๋‹ค. ์ด ๋ฐฉ์‹์˜ ๊ฐ€์žฅ ํฐ ํŠน์ง•์€ ๋ฉ”์ธํ…Œ์ด๋ธ”์—์„œ๋Š” ํ•ญ์ƒ ์ตœ์‹  ๋ฒ„์ „์˜ ๋ฐ์ดํ„ฐ๋งŒ ์œ ์ง€ํ•˜๊ณ , ์ด์ „ ๋ฒ„์ „๋“ค์€ ๋ณ„๋„์˜ ์–ธ๋‘ ๊ณต๊ฐ„์— ๋ณ€๊ฒฝ๋œ ๋ถ€๋ถ„(๋ณ€๊ฒฝ ์ „ ์นผ๋Ÿผ ๊ฐ’)๋งŒ ์ €์žฅํ•œ๋‹ค๋Š” ์ ์ž…๋‹ˆ๋‹ค. (DB_ROLL_PTR๋กœ ์ด์ „ ๋ฒ„์ „๋“ค์„ ์ฒด์ธ ๊ตฌ์กฐ๋กœ ์—ฐ๊ฒฐํ•˜์—ฌ ํžˆ์Šคํ† ๋ฆฌ ์ถ”์  ๊ด€๋ฆฌ)

MySQL 8.0 InnoDB ๊ตฌ์กฐ

 

InnoDB์˜ ์–ธ๋‘ ๋กœ๊ทธ๋Š” ์‹œ์Šคํ…œ ํ…Œ์ด๋ธ”์ŠคํŽ˜์ด์Šค, ์–ธ๋‘ ํ…Œ์ด๋ธ”์ŠคํŽ˜์ด์Šค, ๋˜๋Š” ์ž„์‹œ ํ…Œ์ด๋ธ”์ŠคํŽ˜์ด์Šค์— ์ €์žฅ๋ฉ๋‹ˆ๋‹ค.

๋ณ€๊ฒฝ ์ž‘์—…์„ ๋งˆ์น˜๊ณ  commit ๋ช…๋ น์„ ์‹คํ–‰ํ•˜๋ฉด InnoDB๋Š” ํ˜„์žฌ์˜ ์ƒํƒœ๋ฅผ ์˜๊ตฌ์ ์ธ ๋ฐ์ดํ„ฐ๋กœ ๋งŒ๋“ญ๋‹ˆ๋‹ค. ๋งŒ์•ฝ commit์ด ์•„๋‹Œ rollback์„ ์‹คํ–‰ํ•˜๋ฉด InnoDB๋Š” ์–ธ๋‘ ์˜์—ญ์— ์žˆ๋Š” ๋ฐฑ์—…๋œ ๋ฐ์ดํ„ฐ๋ฅผ InnoDB ๋ฒ„ํผ ํ’€๋กœ ๋‹ค์‹œ ๋ณต๊ตฌํ•˜๊ณ , ์–ธ๋‘ ์˜์—ญ์˜ ๋‚ด์šฉ์„ ์‚ญ์ œํ•ฉ๋‹ˆ๋‹ค.

(commit์ด ๋œ๋‹ค๊ณ  ์–ธ๋‘ ์˜์—ญ์˜ ๋ฐฑ์—… ๋ฐ์ดํ„ฐ๊ฐ€ ํ•ญ์ƒ ๋ฐ”๋กœ ์‚ญ์ œ๋˜์ง€๋Š” ์•Š๊ณ , ์–ธ๋‘ ์˜์—ญ์„ ํ•„์š”๋กœ ํ•˜๋Š” ํŠธ๋žœ์žญ์…˜์ด ์—†์„ ๋•Œ ์‚ญ์ œ๋ฉ๋‹ˆ๋‹ค.) 

์ž ๊ธˆ ์—†๋Š” ์ผ๊ด€๋œ ์ฝ๊ธฐ(Non-Locking Consistent Read)

 InnoDB ์Šคํ† ๋ฆฌ์ง€ ์—”์ง„์€ MVCC ๊ธฐ์ˆ ์„ ์ด์šฉํ•ด ๋ฝ ์—†์ด ์ฝ๊ธฐ ์ž‘์—…์„ ์ˆ˜ํ–‰ํ•ฉ๋‹ˆ๋‹ค. ๋”ฐ๋ผ์„œ ๋‹ค๋ฅธ ํŠธ๋žœ์žญ์…˜์ด ํš๋“ํ•œ ๋ฝ์„ ๊ธฐ๋‹ค๋ฆฌ์ง€ ์•Š๊ณ , ์ฝ๊ธฐ ์ž‘์—…์ด ๊ฐ€๋Šฅํ•ฉ๋‹ˆ๋‹ค.

์ˆœ์ˆ˜ํ•œ ์ฝ๊ธฐ(select) ์ž‘์—…์€ ๋‹ค๋ฅธ ํŠธ๋žœ์žญ์…˜์˜ ๋ณ€๊ฒฝ ์ž‘์—…๊ณผ ๊ด€๊ณ„์—†์ด ํ•ญ์ƒ ๋ฝ ํš๋“์„ ์œ„ํ•œ ๋Œ€๊ธฐ ์—†์ด ๋ฐ”๋กœ ์‹คํ–‰๋ฉ๋‹ˆ๋‹ค. (๊ฒฉ๋ฆฌ ์ˆ˜์ค€ SERIALIABLE ์ œ์™ธ)

 ์œ„์˜ ๊ทธ๋ฆผ์—์„œ ํŠน์ • ์‚ฌ์šฉ์ž๊ฐ€ "์„œ์šธ"์ด์—ˆ๋˜ ๋ ˆ์ฝ”๋“œ๋ฅผ "๊ฒฝ๊ธฐ"๋กœ ๋ณ€๊ฒฝํ•˜๊ณ  ์ปค๋ฐ‹์„ ์ˆ˜ํ–‰ํ•˜์ง€ ์•Š์€ ์ƒํ™ฉ์ด์–ด๋„, ๋‹ค๋ฅธ ์‚ฌ์šฉ์ž์˜ SELECT ์ž‘์—…์„ ๋ฐฉํ•ดํ•˜์ง€ ์•Š๋Š” ๊ฒƒ์„ ๋ณผ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ด๋ฅผ ์ž ๊ธˆ ์—†๋Š” ์ผ๊ด€๋œ ์ฝ๊ธฐ(Non-Locking Consistent Read)๋ผ ํ•˜๋ฉฐ, InnoDB์—์„œ๋Š” ๋ณ€๊ฒฝ๋˜์ง€ ์ „ ๋ฐ์ดํ„ฐ๋ฅผ ์ฝ๊ธฐ ์œ„ํ•ด ์–ธ๋‘ ๋กœ๊ทธ๋ฅผ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค.

์ด๋Ÿฌํ•œ ์ผ๊ด€๋œ ์ฝ๊ธฐ๋ฅผ ์œ„ํ•ด์„  ์–ธ๋‘ ๋กœ๊ทธ๋ฅผ ์‚ญ์ œํ•˜์ง€ ์•Š๊ณ  ํ•ด๋‹น ํŠธ๋žœ์žญ์…˜ ๋™์•ˆ ๊ณ„์† ์œ ์ง€ํ•ด์•ผ ๋˜๋Š”๋ฐ ์ด ๋•Œ๋ฌธ์— MySQL์˜ ์„œ๋ฒ„๊ฐ€ ๋А๋ ค์ง€๋Š” ๋“ฑ์˜ ๋ฌธ์ œ๊ฐ€ ๋ฐœ์ƒํ•  ์ˆ˜ ์žˆ์–ด, ํŠธ๋žœ์žญ์…˜์€ ๋กค๋ฐฑ์ด๋‚˜ ์ปค๋ฐ‹์„ ํ†ตํ•ด ๋นจ๋ฆฌ ์™„๋ฃŒ๋  ์ˆ˜ ์žˆ๋„๋ก ํ•˜๋Š” ๊ฒƒ์ด ๊ถŒ์žฅ๋ฉ๋‹ˆ๋‹ค.

 ์ถ”๊ฐ€๋กœ ๋ฐ๋“œ๋ฝ ๋ฐœ์ƒ ์‹œ ๊ด€๋ จ ํŠธ๋žœ์žญ์…˜๋“ค์„ ์ฐพ์•„ ๊ฐ•์ œ ์ข…๋ฃŒํ•ด์•ผ ๋˜๋Š”๋ฐ ์ด๋•Œ ์–ด๋А ํŠธ๋žœ์žญ์…˜์„ ๊ฐ•์ œ ์ข…๋ฃŒํ• ์ง€๋ฅผ "ํŠธ๋žœ์žญ์…˜์˜ ์–ธ๋‘ ๋กœ๊ทธ ์–‘"์œผ๋กœ ํŒ๋‹จํ•ฉ๋‹ˆ๋‹ค. ์–ธ๋‘ ๋กœ๊ทธ ๋ ˆ์ฝ”๋“œ๋ฅผ ๋” ์ ๊ฒŒ ๊ฐ€์ง„ ํŠธ๋žœ์žญ์…˜์€ ๋กค๋ฐฑ์„ ํ•ด๋„ ์–ธ๋‘ ์ฒ˜๋ฆฌ๋ฅผ ํ•ด์•ผ ๋  ๋‚ด์šฉ์ด ๋” ์ ์œผ๋ฏ€๋กœ, ๊ฐ•์ œ ๋กค๋ฐฑ์œผ๋กœ ์ธํ•œ ์„œ๋ฒ„ ๋ถ€ํ•˜๋„ ๋œ ์œ ๋ฐœ๋˜๊ธฐ ๋•Œ๋ฌธ์ž…๋‹ˆ๋‹ค.

 

4. PostgreSQL์˜ MVCC: XID(xmin, xmax)

PostgreSQL์€ MVCC๋ฅผ ๊ฐ€์žฅ ๋„ค์ดํ‹ฐ๋ธŒ ํ•˜๊ฒŒ ๊ตฌํ˜„ํ•œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์ž…๋‹ˆ๋‹ค. ๋ณ„๋„์˜ ์–ธ๋‘ ์˜์—ญ ์—†์ด ํ…Œ์ด๋ธ” ๋‚ด๋ถ€์— ๋ชจ๋“  ๋ฒ„์ „์„ ์ €์žฅํ•˜๋Š” ๋…ํŠนํ•œ ๋ฐฉ์‹์„ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค.

PostgreSQL์˜ ํŠœํ”Œ ๋ฒ„์ „ ๊ด€๋ฆฌ

๊ฐ ํŠœํ”Œ(ํ–‰)์—๋Š” ๋‹ค์Œ ๋ฉ”ํƒ€๋ฐ์ดํ„ฐ๊ฐ€ ์กด์žฌํ•ฉ๋‹ˆ๋‹ค.

  • xmin: ํ•ด๋‹น ํŠœํ”Œ์„ ์ƒ์„ฑํ•œ ํŠธ๋žœ์žญ์…˜ ID.
  • xmax: ์‚ญ์ œ ๋˜๋Š” ์—…๋ฐ์ดํŠธํ•œ ํŠธ๋žœ์žญ์…˜ ID (0์ด๋ฉด ์ตœ์‹  ๋ฒ„์ „)
  • ctid: ํ–‰์˜ ๋ฌผ๋ฆฌ์  ์œ„์น˜(ํŽ˜์ด์ง€, ์Šฌ๋กฏ)

์˜ˆ์ œ๋ฅผ ํ†ตํ•ด PostgreSQL์˜ MVCC ๋ฐฉ์‹์„ ์ดํ•ดํ•ด ๋ด…์‹œ๋‹ค.

create table account(id int, balance money);
insert into account values (1, 500);
 xmin | id | balance 
------+----+---------
  733 |  1 | $500.00
(1 row)

 

์œ„์ฒ˜๋Ÿผ account ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๊ณ  (1,500) ๋ฐ์ดํ„ฐ๋ฅผ insert ํ–ˆ์Šต๋‹ˆ๋‹ค.

์œ„์—์„œ xmin์€ ํ•ด๋‹น insert๋ฌธ์„ ์‹คํ–‰ํ–ˆ๋˜ ํŠธ๋žœ์žญ์…˜ id๊ฐ€ 733 ์ž„์„ ์˜๋ฏธํ•ฉ๋‹ˆ๋‹ค.

select ctid, xmin, xmax, * from account;
 ctid  | xmin | xmax | id | balance 
-------+------+------+----+---------
 (0,1) |  733 |    0 |  1 | $500.00
(1 row)

์œ„์ฒ˜๋Ÿผ ํžˆ๋“  ์นผ๋Ÿผ๋“ค์„ ๋ช…์‹œํ•˜์—ฌ ํ™•์ธํ•˜๋ฉด ๋” ๋งŽ์€ ์ •๋ณด๋“ค์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

insert into account values (2, 600), (3, 700);

์œ„์ฒ˜๋Ÿผ 2๊ฐœ์˜ ๋ฐ์ดํ„ฐ๋ฅผ ์ถ”๊ฐ€๋กœ ์‚ฝ์ž…ํ•˜๊ณ  ๋‹ค์‹œ account์˜ ํŠœํ”Œ๋“ค์„ ์กฐํšŒํ•ด ๋ด…์‹œ๋‹ค.

 ctid  | xmin | xmax | id | balance

-------+------+------+----+---------
(0,1) |  733 |    0 |  1 | $500.00
(0,2) |  734 |    0 |  2 | $600.00
(0,3) |  734 |    0 |  3 | $700.00
(3 rows)

์ตœ์ดˆ๋กœ ์‚ฝ์ž…ํ–ˆ๋˜ ํŠœํ”Œ id:1, balance:500์€ ์•„๋ž˜ ๋‘ ๊ฐœ์˜ ํŠœํ”Œ์ด ์ถ”๊ฐ€๋˜์—ˆ๋Š”๋ฐ, ๋™์ผ ํŠธ๋žœ์žญ์…˜ ๋‚ด์—์„œ ์‚ฝ์ž…๋œ ํŠœํ”Œ์ธ ๊ฒƒ์„ xmin 734๋ฅผ ํ†ตํ•ด ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

-- ์ฒซ ๋ฒˆ์งธ ํ–‰์˜ ํ˜„์žฌ ๊ฐ’ ํ™•์ธ
select ctid, xmin, xmax, * from account where id = 1;
ctid  | xmin | xmax | id | balance
-------+------+------+----+---------
(0,1) |  743 |    0 |  1 | $500.00
  • ctid (0,1): 0๋ฒˆ ํŽ˜์ด์ง€์˜ 1๋ฒˆ ์Šฌ๋กฏ
  • xmin 743: ํŠธ๋žœ์žญ์…˜ 743์ด ์ด ํ–‰์„ ์ƒ์„ฑ
  • xmax 0: ๊ฐ€์žฅ ์ตœ์‹  ๋ฒ„์ „

์ƒˆ ํŠธ๋žœ์žญ์…˜ 745 ๋‚ด์—์„œ ์—…๋ฐ์ดํŠธ

์ƒˆ๋กœ์šด ํŠธ๋žœ์žญ์…˜ ๋‚ด์—์„œ id 1์ธ ํŠœํ”Œ์— ๋Œ€ํ•ด ์—…๋ฐ์ดํŠธ๋ฅผ ์ง„ํ–‰ํ•˜๊ณ  ๊ฒฐ๊ณผ๋ฅผ ํ™•์ธํ•ด ๋ด…์‹œ๋‹ค.

begin;
update account set balance=400 where id = 1;
select ctid, xmin, xmax, * from account where id = 1;
ctid  | xmin | xmax | id | balance
-------+------+------+----+---------
(0,4) |  745 |    0 |  1 | $400.00

id = 1์— ๋Œ€ํ•ด balance๋ฅผ 400์œผ๋กœ ์—…๋ฐ์ดํŠธ ํ•œ ๋’ค select ๋ฌธ์„ ์‹คํ–‰ํ•œ ๊ฒฐ๊ณผ์ž…๋‹ˆ๋‹ค.

  • ctid๊ฐ€ (0,1)์—์„œ (0,4)๋กœ ๋ณ€๊ฒฝ โ†’ ์ƒˆ๋กœ์šด ์œ„์น˜์— ์ƒˆ ๋ฒ„์ „ ์ƒ์„ฑ
  • xmin์ด 745๋กœ ๋ณ€๊ฒฝ โ†’ ํ˜„์žฌ ํŠธ๋žœ์žญ์…˜์ด ์ƒ์„ฑํ•œ ๋ฒ„์ „ 

ํ˜„์žฌ ํŠธ๋žœ์žญ์…˜ ๋‚ด์—์„œ ์‚ฌ์šฉ์ž์—๊ฒŒ๋Š” ์—…๋ฐ์ดํŠธ๋œ ํŠœํ”Œ์ด ์กฐํšŒ๋˜๋Š” ๊ฒƒ์„ ์•Œ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

๋™์‹œ ์ ‘๊ทผ: ๋‹ค๋ฅธ ์„ธ์…˜์—์„œ ์ฝ๊ธฐ

ํŠธ๋žœ์žญ์…˜ 745๋ฅผ ์•„์ง ์ปค๋ฐ‹ํ•˜์ง€ ์•Š์€ ์ƒํƒœ์—์„œ ๋‹ค๋ฅธ ์„ธ์…˜์—์„œ id = 1์ธ ํŠœํ”Œ์„ ์กฐํšŒํ•ด ๋ณธ ๊ฒฐ๊ณผ๋Š” ์•„๋ž˜์™€ ๊ฐ™์Šต๋‹ˆ๋‹ค.

-- ๋‹ค๋ฅธ ์„ธ์…˜์—์„œ
select ctid, xmin, xmax, * from account where id = 1;
ctid  | xmin | xmax | id | balance
-------+------+------+----+---------
(0,1) |  743 |  745 |  1 | $500.00

ํŠธ๋žœ์žญ์…˜ 745์—์„œ select ๋ฌธ์„ ์‹คํ–‰ํ–ˆ๋˜ ๊ฒฐ๊ณผ์™€ ๋‹ฌ๋ฆฌ ์—ฌ์ „ํžˆ id1์˜ balance๋Š” 500์œผ๋กœ ์กฐํšŒ๊ฐ€ ๋ฉ๋‹ˆ๋‹ค.

  • ์ด์ „ ๋ฒ„์ „($500.00)์„ ์—ฌ์ „ํžˆ ๋ณผ ์ˆ˜ ์žˆ์Œ
  • xmax๊ฐ€ 745๋กœ ์„ค์ •๋จ โ†’ "ํŠธ๋žœ์žญ์…˜ 745๊ฐ€ ์ด ํ–‰์„ ์ˆ˜์ •ํ–ˆ์ง€๋งŒ ์•„์ง ์ปค๋ฐ‹๋˜์ง€ ์•Š์Œ"

xmax๋Š” 0์ด ์•„๋‹Œ 745๋กœ ์„ค์ •๋˜์–ด ํ•ด๋‹น ํŠœํ”Œ์ด ์ตœ์‹  ๋ฒ„์ „์ด ์•„๋‹˜์„ ์•Œ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

์ปค๋ฐ‹ ํ›„ ์ƒํƒœ

์ฒซ ๋ฒˆ์งธ ์„ธ์…˜์—์„œ commit ํ›„, ๋‹ค๋ฅธ ์„ธ์…˜์—์„œ ๋‹ค์‹œ ์กฐํšŒํ•œ ๊ฒฐ๊ณผ์ž…๋‹ˆ๋‹ค.

select ctid, xmin, xmax, * from account where id = 1;
ctid  | xmin | xmax | id | balance 
-------+------+------+----+---------
(0,4) |  745 |    0 |  1 | $400.00

์ด์ œ ์ƒˆ ๋ฒ„์ „(id:1, balane: 400)์ด ๋ชจ๋“  ์„ธ์…˜์—์„œ ๋ณด์ž…๋‹ˆ๋‹ค. ๋˜ํ•œ xmax๊ฐ€ 0(์ตœ์‹  ๋ฒ„์ „)์ธ ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. 

 

์ด๋Ÿฌํ•œ ๊ตฌํ˜„ ๋ฐฉ์‹์˜ ์žฅ์ ์€ ๋‹จ์ˆœ์„ฑ๊ณผ ๋ช…ํ™•์„ฑ์ž…๋‹ˆ๋‹ค. ํ•˜์ง€๋งŒ ๋ณ€๊ฒฝ๋œ ๋ฐ์ดํ„ฐ๊ฐ€ ๋™์ผํ•œ ํŽ˜์ด์ง€์— ๋ˆ„์ ๋˜์–ด ์‹ค์ œ ํ…Œ์ด๋ธ” ํฌ๊ธฐ๊ฐ€ ๊ณ„์† ์ฆ๊ฐ€ํ•˜๊ณ , ์ฟผ๋ฆฌ ์„ฑ๋Šฅ์ด ์ €ํ•˜๋  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ด๋ฅผ ํ•ด๊ฒฐํ•˜๊ธฐ ์œ„ํ•ด VACUUM ์ž‘์—…์ด ํ•„์ˆ˜์ ์ž…๋‹ˆ๋‹ค.

PostgreSQL์˜ VACUUM ์œ ํ˜•

1) Standard VACUUM: ๋ฐ๋“œ ํŠœํ”Œ์„ ์ •๋ฆฌํ•˜์ง€๋งŒ ํ…Œ์ด๋ธ”์˜ ํฌ๊ธฐ๋Š” ์ค„์ด์ง€ ์•Š์Œ

2) VACUUM FULL: ํ…Œ์ด๋ธ”์„ ์™„์ „ํžˆ ์žฌ๊ตฌ์„ฑํ•˜์ง€๋งŒ Exclusive Lock(๋ฐฐํƒ€์  ์ž ๊ธˆ) ํ•„์š”

3) Auto VACUUM: ํŠน์ • ์กฐ๊ฑด(๋ฐ๋“œ ํŠœํ”Œ ๋น„์œจ, ํŠธ๋žœ์žญ์…˜ ID age ๋“ฑ)์„ ๋งŒ์กฑํ•  ๋•Œ ์ž๋™ ์‹คํ–‰

 

PostgreSQL์—์„œ ์ฃผ์˜ํ•ด์•ผ ํ•  ์ ์€ ํŠธ๋žœ์žญ์…˜ ID Wraparound ๋ฌธ์ œ์ž…๋‹ˆ๋‹ค. ํŠธ๋žœ์žญ์…˜ ID๋Š” 32๋น„ํŠธ๋กœ ์•ฝ 40์–ต ๊ฐœ์˜ ID๋ฅผ ์ œ๊ณตํ•˜๋ฉฐ, ์ ˆ๋ฐ˜์”ฉ ๊ณผ๊ฑฐ์™€ ๋ฏธ๋ž˜๋ฅผ ์œ„ํ•ด ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค. ID๊ฐ€ ์ˆœํ™˜ํ•˜๋ฉด์„œ ๊ณผ๊ฑฐ ๋ฐ์ดํ„ฐ๊ฐ€ ๋ฏธ๋ž˜ ๋ฐ์ดํ„ฐ๋กœ ๋ณด์ด๋Š” ๋ฌธ์ œ๋ฅผ ๋ฐฉ์ง€ํ•˜๊ธฐ ์œ„ํ•ด Anti-Wraparound VACUUM์ด ์ž๋™์œผ๋กœ ์‹คํ–‰๋ฉ๋‹ˆ๋‹ค.

 

5. ๊ฐ ์‹œ์Šคํ…œ์˜ ์ฃผ์š” ์ฐจ์ด์ 

1) ๋ฒ„์ „ ๊ด€๋ฆฌ ๋ฐฉ์‹

Oracle์€ ์›๋ณธ ๋ธ”๋ก๊ณผ CR ๋ธ”๋ก์„ ๋ถ„๋ฆฌํ•˜์—ฌ ๊ด€๋ฆฌํ•˜๋ฉฐ, Undo Segment์—์„œ ์ด์ „ ๋ฒ„์ „์„ ๊ด€๋ฆฌํ•ฉ๋‹ˆ๋‹ค.

MySQL InnoDB๋Š” ์–ธ๋‘ ๋กœ๊ทธ์— ๋ณ€๊ฒฝ ์ด๋ ฅ์„ ์ฒด์ธ ํ˜•ํƒœ๋กœ ์ €์žฅํ•ฉ๋‹ˆ๋‹ค.

PostgresSQL์€ ๋ชจ๋“  ๋ฒ„์ „์„ ๋™์ผํ•œ ํ…Œ์ด๋ธ” ํŽ˜์ด์ง€์— ์ €์žฅํ•ฉ๋‹ˆ๋‹ค.

 

2) ์„ฑ๋Šฅ

Oracle์˜ ๋ฐฉ์‹์€ ๋ณต์žกํ•˜์ง€๋งŒ ๋งค์šฐ ํšจ์œจ์ ์ž…๋‹ˆ๋‹ค. CR ๋ธ”๋ก ์ƒ์„ฑ ๋น„์šฉ์ด ์žˆ์ง€๋งŒ, ์–ธ๋‘ ์„ธ๊ทธ๋จผํŠธ์˜ ํšจ์œจ์  ๊ด€๋ฆฌ๋กœ ์•ˆ์ •์ ์ธ ์„ฑ๋Šฅ์„ ์ œ๊ณตํ•ฉ๋‹ˆ๋‹ค.

MySQL InnoDB๋Š” ์–ธ๋‘ ๋กœ๊ทธ ์ฒด์ธ์„ ๋”ฐ๋ผ๊ฐ€๋Š” ๋น„์šฉ์ด ์žˆ์ง€๋งŒ, Buffer Pool(๋ฒ„ํผ ํ’€)๊ณผ์˜ ํ†ตํ•ฉ์œผ๋กœ ์ข‹์€ ์„ฑ๋Šฅ์„ ๋ณด์ž…๋‹ˆ๋‹ค.

PostgreSQL์€ ๋‹จ์ˆœํ•œ ๊ตฌ์กฐ๋กœ ๋น ๋ฅธ ์ฝ๊ธฐ๊ฐ€ ๊ฐ€๋Šฅํ•˜์ง€๋งŒ, VACUUM ์˜ค๋ฒ„ํ—ค๋“œ์™€ Table Bloating(ํ…Œ์ด๋ธ” ๋ถ€ํ’€๋ฆผ) ๋ฌธ์ œ๊ฐ€ ์žˆ์Šต๋‹ˆ๋‹ค.

 

3) ๊ณต๊ฐ„ ํšจ์œจ์„ฑ

Oracle์€ ์–ธ๋‘ ์„ธ๊ทธ๋จผํŠธ์˜ ์ˆœํ™˜ ์žฌ์‚ฌ์šฉ์œผ๋กœ ๊ณต๊ฐ„ ํšจ์œจ์ ์ž…๋‹ˆ๋‹ค.

MySQL InnoDB๋„ ์–ธ๋‘ ๋กœ๊ทธ์˜ ์ž๋™ ์ •๋ฆฌ๋กœ ํšจ์œจ์ ์ž…๋‹ˆ๋‹ค.

PostSQL์€ VACUUM ์ž‘์—…์ด ์ ์ ˆํžˆ ์ˆ˜ํ–‰๋˜์ง€ ์•Š์œผ๋ฉด ์‹ฌ๊ฐํ•œ ๊ณต๊ฐ„ ๋‚ญ๋น„๊ฐ€ ๋ฐœ์ƒํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 

4) ๊ฒฉ๋ฆฌ ์ˆ˜์ค€

Oracle์€ READ COMMITTED๊ฐ€ ๊ธฐ๋ณธ์ด๋ฉฐ, SERIALIZABLE์„ ์ง€์›ํ•˜์ง€๋งŒ REPEATABLE READ๋Š” ๋ช…์‹œ์ ์œผ๋กœ ์ง€์›ํ•˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.

MySQL InnoDB๋Š” REPEATABLE READ๊ฐ€ ๊ธฐ๋ณธ์ด๋ฉฐ, ๋ชจ๋“  ํ‘œ์ค€ ๊ฒฉ๋ฆฌ ์ˆ˜์ค€์„ ์ง€์›ํ•ฉ๋‹ˆ๋‹ค.

PostgresSQL๋„ ๋ชจ๋“  ํ‘œ์ค€ ๊ฒฉ๋ฆฌ ์ˆ˜์ค€์„ ์ง€์›ํ•˜๋ฉฐ, ํŠนํžˆ SERIALIZEABLE์—์„œ ๊ฐ•๋ ฅํ•œ ์ผ๊ด€์„ฑ์„ ์ œ๊ณตํ•ฉ๋‹ˆ๋‹ค.

 

 

 

 

์ฐธ๊ณ  ์ž๋ฃŒ:

์นœ์ ˆํ•œ SQL ํŠœ๋‹(DBian) - ์กฐ์‹œํ˜• ์ง€์Œ

https://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT020

 

Data Concurrency and Consistency

16/34 The script content on this page is for navigation purposes only and does not alter the content in any way. 9 Data Concurrency and Consistency Introduction to Data Concurrency and Consistency In a single-user database, a user can modify data without c

docs.oracle.com

https://www.mydbops.com/blog/an-overview-to-innodb-undo-log

 

An Overview to InnoDB Undo Log

Learn about InnoDB Undo Logs, their role in consistent reads, rollback segments, history lists, and performance optimization in MySQL InnoDB.

www.mydbops.com

https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-logs.html

 

MySQL :: MySQL 8.0 Reference Manual :: 17.6.6 Undo Logs

An undo log is a collection of undo log records associated with a single read-write transaction. An undo log record contains information about how to undo the latest change by a transaction to a clustered index record. If another transaction needs to see t

dev.mysql.com

https://www.alibabacloud.com/blog/598966

 

An In-Depth Analysis of UNDO Logs in InnoDB

This article introduces Undo Log in InnoDB, including its role, design ideas, record content, organizational structure, and various functional implementations.

www.alibabacloud.com

 

https://dev.mysql.com/doc/refman/8.4/en/innodb-consistent-read.html

 

MySQL :: MySQL 8.4 Reference Manual :: 17.7.2.3 Consistent Nonlocking Reads

17.7.2.3 Consistent Nonlocking Reads A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point in time, a

dev.mysql.com

https://postgresql.kr/blog/postgresql_table_bloating.html

 

PostgreSQL ํ…Œ์ด๋ธ” ๋ถ€ํ’€๋ฆผ ํ˜„์ƒ ๋Œ€์ฒ˜ ๋ฐฉ์•ˆ

PostgreSQL์—์„œ์˜ ํ…Œ์ด๋ธ” ๋ถ€ํ’€๋ ค์ง€๋Š” ๋ฌธ์ œ๋ฅผ ์„ค๋ช…ํ•˜๊ณ , ๊ทธ ๋Œ€์ฒ˜ ๋ฐฉ์•ˆ์„ ์ฐพ์•„๋ด…๋‹ˆ๋‹ค.

postgresql.kr

https://postgrespro.com/blog/pgsql/5967899

 

MVCC in PostgreSQL โ€” 4. Snapshots

After having discussed isolation problems and having made a digression regarding the low-level data structure , last time we explored row versions and observed how different operations changed tuple header fields. Now we will look at how consistent data sn

postgrespro.com

https://www.youtube.com/watch?v=TBmDBw1IIoY

 

728x90