DB 예상 면접 질문) "ACID가 뭐에요? 어디에 쓰여요?"

Atomicity: 원자성

- Even if a transaction is composed of several tasks (queries), a transaction should be processed in each transaction unit

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100000 WHERE name = 'A';
UPDATE accounts SET balane = balance + 100000 WHERE name = 'B';
COMMIT;

Consistancy: 일관성

- ensure that database rules are always followed before and after a transaction

예: balance는 음수가 될 수 없다 라는 constraint 가 있을 시 이는 transaction 시 지켜져야함

UPDATE accounts SET balance = -5000 WHERE name = 'A'; --failed since balance is >= 0

Isolation: 고립성

- If a transaction modifies data A, then another transaction modifying data A can only be processed after ongoing transaction is finished.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Durability: 지속성

- If a transaction is committed, despite the data not being written on the disk due to a problem, the system should be able to recover the data so that it can be written onto the disk and be reflected on the data table.

COMMIT; -- data is written to WAL before permanently written onto the disk

- WAL: 빠르게 로그 형태로 commit 될 내용을 쓰고 바로 디스크로 flush 함 (fsync)

- Redo 로그: WAL을 COMMIT 반영에 사용

- Undo 로그: WAL을 ROLLBACK 에 사용

- WAL로 발생하는 disk I/O 부하 최소화 방법:

• 주기적으로 디스크에 기록
• OS cache 사용

- DBMS transaction 속도는 durability를 지키기 위해 disk write 속도에 크게 의존하게 됨. 안전할 수록 느려짐.