# database transection & ACID
###### tags: `backend` `technic`

database transection
https://oldmo860617.medium.com/database-transaction-acid-156a3b75845e

Transaction 主要是解決需要一起發生的事件但事件或事件的參與者不同時或不一致的問題。

ACID:
- Atomicity
- each transaction is treated as a single "unit"
- succeeds completely, or fails completely
- Consistency
- a transaction can only bring the database from one valid state to another
- maintain database invariants
- Isolation
- concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially
- Durability
- once a transaction has been committed, it will remain committed even in the case of a system failure
- completed transactions (or their effects) are recorded in non-volatile memory

https://blog.kennycoder.io/2020/01/21/RDBMS-%E9%97%9C%E8%81%AF%E5%BC%8F%E8%B3%87%E6%96%99%E5%BA%AB-ACID%E5%9F%BA%E7%A4%8E%E8%A7%80%E5%BF%B5/

重點：
RDBMS 支持 atomic check-and-set 模式，可以在同一句 Update Statement 下同時檢查用戶是否有足夠錢可以提款，如果有足夠錢才會進行用戶扣款的更新。

之後會發一系列有關 RDBMS 跟 ORM 的文章，這些都很基礎，是操作資料庫都要知道的知識，之前的我太依賴 ORM 了，現在吃了苦頭要用 RAW SQL 的時候才知道基礎的重要性，ORM 很好用但絕對要知道其包裝底下的 SQL 語句到底長怎樣，這才是重要的，否則當系統很慢出在資料庫身上時，就會很難找到答案的。

延伸閱讀：
Statement 執行錯誤 Transaction Rollback 使用的場合
NoSQL 的 2pc
避免 Race Condition， LOCK 機制