Database

[Java][Spring][Database] 主流事務操作 @Transactional & TransactionTemplate

NickDatabase, Java, MySQL, SQL

Intro 在 Spring 專案開發中，@Transactional 註解因其高易用性，成為處理資料庫事務（Transaction）的首選方式。 然而，在處理批次資料寫入（Bulk Insert/Update）、大批次資料對帳或長流程業務邏輯時，若習慣性地在方法（Method）層級套用 @Transactional，容易引發「長事務（Long Transaction）」，對資料庫與系統穩定性帶來嚴重隱患： 資料庫鎖定與超時 (Lock Wait Timeout)：長事務會長時間持有資料列鎖或間隙鎖（Gap Lock），導致其他併發的線上請求因無法取得鎖而排隊超時。 Undo Log 膨脹與效能下滑：未 Commit 的事務會阻止 MySQL (InnoDB) 進行 Undo Log 的清理（Purge），導致膨脹的 Undo Log 鏈大幅拖慢全系統的查詢速度。 失敗代價過高：若在處理上萬筆資料的最後一刻發生異常，整個長事務會強制全數回滾（Rollback），導致先前耗費的 CPU 與 I/O 資源完全浪費。 本文旨在釐清 聲明式事務（@Transactional） 與 編程式事務（TransactionTemplate） 的適用場景，並針對批次處理（Chunk / Batch […]

Read more

[Database][MySQL] 樂觀鎖 & 悲觀鎖指南 – OCC & PCC (FOR SHARE|FOR UPDATE)

NickDatabase, MySQL, SQL

Intro Database 中，鎖的實現分類上目前可分為兩種： 樂觀鎖（Optimistic Locking） 悲觀鎖（Pessimistic Locking） 樂觀鎖 – OCC 樂觀並行控制 – Optimistic Concurrency Control，縮寫「OCC」 不利用內建鎖機制，而是利用應用層邏輯解決衝突 可透過讀取資料時分派一個版本或時間戳，通過校驗後寫入否則中斷(實作上可利用寫入連帶驗證版本達成) 適合交易之間資料競爭(data race)概率較小的應用情境 MySQL 實現範例 CAS (Compare And Swap) — 確保「我現在改的」是我剛才「看到的」那一版 UPDATE page SET content = '第六版…', version = version + 1 WHERE id […]

Read more

[MySQL] Character Sets & Collations (字符集與排序規則) – utf8 or utf8mb4

NickDatabase, MySQL, SQL

Intro Character Set: 字符集合，定義資料如何儲存與表示。 MySQL 支援多種字符集，例如： latin1, utf8mb4 Collation: 基於字符集，定義了字符集中的字符如何進行排序和比較。 MySQL 的 Collation 一般以以下格式命名，舉 utf8mb4_general_ci 為例： utf8mb4: 字符集名稱 general: 簡化的排序規則 (unicode為精準 Unicode 排序規則) ci(case insensitive): 大小寫不敏感 MySQL 字符集上在 8.4 版本後已經清楚且標準很多 MySQL 8.4 Reference Manual :: 12.2 Character Sets and Collations […]

Read more

[SQL] 產生唯一的交易編號 – Generating unique transaction IDs

NickDatabase, MySQL, SQL

Intro 在交易的應用場景下，可能需求為透過資料庫符合 ACID 快速產生唯一的交易編號作為 Primary key 或是 Unique key。 MySQL 實作 假設定義一組16碼的交易編號，格式為YYYYMMDD(8) + 流水號(8)。 這前提下代表一天內不建立超過8位數的 record，就可以利用 MySQL auto_increment 搭配 last_insert_id() 來實現： START TRANSACTION; INSERT INTO transactions (transaction_date, amount) VALUES (NOW(), 100); SET @last_id = LAST_INSERT_ID(); SET @fixed_length = 8; SET […]

Read more

[Mongo] MongoDB 指南

NickDatabase, NoSQL

Intro MongoDB – Wikipedia Introduction to MongoDB Cluster MongoDB Clusters Sharding MongoDB Sharding 分散式儲存架構建置 (概念篇) MongoDB Sharding 分散式儲存架構建置 (實作篇) Replica MongoDB Replica Set 高可用性架構搭建 Query / Mongo Shell [Mongo] Query & Mongo Shell 指南

Read more

[並行控制] Concurrency control 指南 – 樂觀鎖|悲觀鎖

NickDatabase, MySQL, OS

Intro Concurrency control – 並行控制 並行控制的核心目標是確保 ACID 中的一致性 (Consistency) 與隔離性 (Isolation)。在實作上，我們主要透過『鎖』這項工具來管理多個事務對同一資源的存取順序。 鎖的實現分類上目前可分為兩種： 樂觀鎖（Optimistic Locking） 悲觀鎖（Pessimistic Locking） Database – 資料庫管理系統 資料庫鎖已經獨立出一篇文章，請參考以下: [Database][MySQL] 樂觀鎖 & 悲觀鎖指南 – OCC|PCC (FOR SHARE & FOR UPDATE) OS – 作業系統 IPC (Inter-Process Communication) – 行程間通訊 Mutex […]

Read more

[ELK] Elastic Stack (OpenSearch): Elasticsearch, Logstash, Kibana 指南

NickDatabase, Full-text search

Intro Elasticsearch 是與名為 Logstash 的資料收集和紀錄檔解析引擎以及名為 Kibana 的分析和視覺化平台一起開發。這三個產品被設計成一個整合解決方案，稱為「Elastic Stack」（以前稱為「ELK stack」）。 Wiki – Elasticsearch ELK Stack: Elasticsearch, Logstash, Kibana | Elastic OpenSearch OpenSearch 包含了搜尋引擎與 OpenSearch Dashboards（此搜尋引擎的資料可視化儀錶板）。此軟體始於2021年，作為 Elasticsearch 與 Kibana 的分支，主要由AWS(亞馬遜雲端運算服務)開發。(Wikipedia) OpenSearch Github: OpenSearch Project opensearch-project/OpenSearch: 🔎 Open source distributed and RESTful search […]

Read more

[Web] 資料過濾與驗證指南 – Data Validation & Filter (XSS, nl2br)

NickDatabase, JavaScript, jQuery, MySQL, PHP

Intro 防範XSS、SQL Injection以及驗證資料已經是基本開發流程，但要如何標準化的去做團隊開發就是一門學問。 資料驗證 顧名思義，就是在寫入Datebase之前處理Input資料驗證。 以MVC架構來說，Validator最佳的實踐流程點是在Model/Entity上，也就是Model已定義好驗證規則，所以所有經過此Model寫入的資料都會是經過經過規則驗證通過的，提供絕佳的共用性與嚴謹度。 Yii 2 – Validating Input (Yii 2 透過Model定義Validator實踐開發) CodeIgniter/Model – Validation (CI擴充框架中，一樣透過Model定義整合Validator實踐開發) Filter前處理or後處理 以XSS來說，到底要前處理還是後處理? 兩個都沒錯，這全看你後端/資料庫那個欄位到底是定義成Plain Text還是HTML Content。 平均開發上，只要一開始寫資料庫沒特別定義，不外乎都是Plain Text，意即做後處理比較直覺(畢竟過往資料都寫進去了)。 後處理缺點 每次在前端(HTML)顯示時都須謹記後處理，漏一個沒處理就中了廣義XSS 前處理缺點 因為資料預設就是HTML，所以應付前端不須處理；相反的在需要Plain Text(例如Excel Plain Text)時就需要在後處理反轉回來 無法保障資料驗證，因為輸入過濾將影響驗證一致性，例如最大字數通過過濾的不確定性造成不精準 遇到textarea給值時需要Decode回來 (歸類input，正規給值方式value或jQuery val()，用html不標準) 綜合以上，優缺點是一體兩面的，取決於需求。 例如80/20法則，前端顯示開發較多、Plain Text顯示開發較少，那選前處理絕對較省成本，且較嚴謹(畢竟反轉與否不影響安全性)，所以依照專案與團隊自行評估即可。 我個人對於Pattern來說會選擇後處理為主，因為儲存資料定義為原始輸入資料(Plain […]

Read more