์ผ | ์ | ํ | ์ | ๋ชฉ | ๊ธ | ํ |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | ||
6 | 7 | 8 | 9 | 10 | 11 | 12 |
13 | 14 | 15 | 16 | 17 | 18 | 19 |
20 | 21 | 22 | 23 | 24 | 25 | 26 |
27 | 28 | 29 | 30 | 31 |
- IEEE 802
- ์ฐ๋ถํฌdb
- mariadb
- ํ๋ก์ด๋์์
- til
- ํ ํฐ ๋ฒ์ค
- ํญํด99
- ํฐ์คํ ๋ฆฌ์ฑ๋ฆฐ์ง
- xv6
- ์์๋ฒํธ
- ์ค๋ฅ์ ์ด
- git merge
- ๋ฐ์ดํฐ ์ ์ก
- ์ค๋ฅ๊ฒ์ถ
- leetcode
- tcp ์ธ๊ทธ๋จผํธ
- i-type
- ์ค๋ธ์
- ์ฃผ๊ธฐ์ ํธ
- ๋น์ฃผ๊ธฐ์ ํธ
- ์๋น์ค ํ๋ฆฌ๋ฏธํฐ๋ธ
- ์ฝ๋ฉํ ์คํธ์ค๋น
- ๊ฐ๋ฐ์์ทจ์
- well known ํฌํธ
- ์ค๋ ๋
- reducible
- 99ํด๋ฝ
- ํ๋ ์ ๊ตฌ์กฐ
- ๊ทธ๋ฆฌ๋ ์๊ณ ๋ฆฌ์ฆ
- tcp ํ๋กํ ์ฝ
- Today
- Total
Unfazedโ๏ธ๐ฏ
๋ฐ์ดํฐ๋ฒ ์ด์ค MVCC ๋ชจ๋ธ: Oracle, MySQL, PostgreSQL์ ๋ค์ค ๋ฒ์ ๋์์ฑ ์ ์ด ๋ณธ๋ฌธ
๋ฐ์ดํฐ๋ฒ ์ด์ค MVCC ๋ชจ๋ธ: Oracle, MySQL, PostgreSQL์ ๋ค์ค ๋ฒ์ ๋์์ฑ ์ ์ด
9taetae9 2025. 6. 1. 18:15DBMS์์ ์ฌ๋ฌ ํธ๋์ญ์ ์ด ๋์์ ๊ฐ์ ๋ฐ์ดํฐ์ ์ ๊ทผํ ๋ ๋ฐ์ํ ์ ์๋ ์ถฉ๋์ ์ด๋ป๊ฒ ์ฒ๋ฆฌํ ๊ฒ์ธ์ง(๋์์ฑ ์ ์ด)๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค ์ฑ๋ฅ๊ณผ ์ ๋ขฐ์ฑ์ ๊ฒฐ์ ํ๋ ์ค์ํ ์์์ ๋๋ค.
๋์์ฑ ์ ์ด์ ์ ๊ทผ ๋ฐฉ์
๋์์ฑ ์ ์ด๋ ํฌ๊ฒ ๋ ๊ฐ์ง ๋ฐฉ์์ผ๋ก ๊ตฌ๋ถ๋ฉ๋๋ค.
๋น๊ด์ ์ ๊ธ(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): ์ฐ๊ธฐ ์์ ์ฉ, ์ฝ๊ธฐ์ ์ฐ๊ธฐ ๋ชจ๋ ์ฐจ๋จ

์ด๋ฌํ ์ ๊ธ ๋ฐฉ์์ ๋ฐ์ดํฐ ์ผ๊ด์ฑ์ ๋ณด์ฅํ์ง๋ง, ๋ฝ ๊ฒฝํฉ(lock contention)์ผ๋ก ์ธํ ์ฑ๋ฅ ์ ํ์ ํ์ฅ์ฑ ๋ฌธ์ ๋ฅผ ์ผ๊ธฐํฉ๋๋ค. ํนํ ์ฝ๊ธฐ ์ค์ฌ์ ์ํฌ๋ก๋์์ ๋ถํ์ํ ๋๊ธฐ์๊ฐ์ด ๋ฐ์ํ์ฌ ์ ์ฒด ์์คํ ์ฒ๋ฆฌ๋์ด ์ ํ๋ฉ๋๋ค.
1. MVCC(Multi-Version Concurrency Control)
MVCC๋ ์ด๋ฌํ ์ ํต์ ์ธ ๋ฝ ๊ธฐ๋ฐ ๋์์ฑ ์ ์ด์ ํ๊ณ๋ฅผ ๊ทน๋ณตํ๊ธฐ ์ํด ๋ฑ์ฅํ ๊ธฐ์ ์ ๋๋ค. ๊ธฐ๋ณธ ์๋ฆฌ๋ ํ๋์ ๋ฐ์ดํฐ ํ์ ๋ํด ์ฌ๋ฌ ๋ฒ์ ์ ๋์์ ์ ์งํ์ฌ, ์ฝ๊ธฐ ์์ ๊ณผ ์ฐ๊ธฐ ์์ ๊ฐ์ ์ฐจ๋จ(block)๋๋ ์ํฉ์ ํด๊ฒฐํ๋ ๊ฒ์ ๋๋ค.
MVCC์ ์ฃผ์ ํน์ง
- ์ฝ๊ธฐ๋ ์ฐ๊ธฐ๋ฅผ ์ฐจ๋จํ์ง ์์
- ์ฐ๊ธฐ๋ ์ฝ๊ธฐ๋ฅผ ์ฐจ๋จํ์ง ์์
- ํธ๋์ญ์ ๊ฒฉ๋ฆฌ์ฑ ๋ณด์ฅ
- ์ค๋ ์ท ๊ฒฉ๋ฆฌ๋ฅผ ํตํ ์ผ๊ด๋ ์ฝ๊ธฐ ์ ๊ณต
MVCC์ ์ฃผ์ ์ด์
- ๋ฝ ๊ฒฝํฉ ์ต์ํ๋ก ๋์์ฑ ํฅ์
- ๋ฐ๋๋ฝ ๋ฐ์ ๊ฐ๋ฅ์ฑ ๊ฐ์
- ์ฝ๊ธฐ ์ฑ๋ฅ ๊ฐ์
- ํธ๋์ญ์ ๊ฒฉ๋ฆฌ์ฑ์ ์ ์งํ๋ฉด์๋ ์ฑ๋ฅ ์ ํ ์ต์ํ
๋ค์ํ ๊ตฌํ ๋ฐฉ์
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๋ก ์ด์ ๋ฒ์ ๋ค์ ์ฒด์ธ ๊ตฌ์กฐ๋ก ์ฐ๊ฒฐํ์ฌ ํ์คํ ๋ฆฌ ์ถ์ ๊ด๋ฆฌ)

InnoDB์ ์ธ๋ ๋ก๊ทธ๋ ์์คํ ํ ์ด๋ธ์คํ์ด์ค, ์ธ๋ ํ ์ด๋ธ์คํ์ด์ค, ๋๋ ์์ ํ ์ด๋ธ์คํ์ด์ค์ ์ ์ฅ๋ฉ๋๋ค.
๋ณ๊ฒฝ ์์ ์ ๋ง์น๊ณ commit ๋ช ๋ น์ ์คํํ๋ฉด InnoDB๋ ํ์ฌ์ ์ํ๋ฅผ ์๊ตฌ์ ์ธ ๋ฐ์ดํฐ๋ก ๋ง๋ญ๋๋ค. ๋ง์ฝ commit์ด ์๋ rollback์ ์คํํ๋ฉด InnoDB๋ ์ธ๋ ์์ญ์ ์๋ ๋ฐฑ์ ๋ ๋ฐ์ดํฐ๋ฅผ InnoDB ๋ฒํผ ํ๋ก ๋ค์ ๋ณต๊ตฌํ๊ณ , ์ธ๋ ์์ญ์ ๋ด์ฉ์ ์ญ์ ํฉ๋๋ค.
(commit์ด ๋๋ค๊ณ ์ธ๋ ์์ญ์ ๋ฐฑ์ ๋ฐ์ดํฐ๊ฐ ํญ์ ๋ฐ๋ก ์ญ์ ๋์ง๋ ์๊ณ , ์ธ๋ ์์ญ์ ํ์๋ก ํ๋ ํธ๋์ญ์ ์ด ์์ ๋ ์ญ์ ๋ฉ๋๋ค.)

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