MySQL Transaction、Lock 與並行控制完整指南

MySQL Transaction、Lock 與並行控制完整指南

Jesse Yen - 9月 05, 2026

單一使用者操作資料庫時，事情很簡單。真正複雜的是： 很多人同時修改資料時，MySQL 怎麼確保資料不會亂掉？ 例如銀行帳戶有 10,000 元，同一時間發生提款；或者購物網站只剩最後一件商品，卻有兩個使用者同時下單。 這些問題都牽涉到資料庫最核心的幾個概念： Transaction（交易） ACID (Atomicity/Consistency/Isolation/Durability) Isolation Level（隔離層級） MVCC (多版本並行控制) Lock（鎖定） Lock Wait Deadlock（死結） 它們共同構成 MySQL 的 Transaction 與 並行控制（Concurrency Control） 。 Transaction 是什麼？ Transaction 可以理解為： 一組必須被視為「同一件事情」的資料庫操作。 最常見的例子就是轉帳。 START TRANSACTION; UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1; UPDATE accounts SET balance = b…

MySQL 備份、還原與災難復原完整指南

MySQL 備份、還原與災難復原完整指南

Jesse Yen - 9月 05, 2026

資料庫管理最重要的工作之一，不只是建立 Database，也不是寫 SQL，而是  資料不見的時候，能不能救回來？ 這件事情很多人並不會在意，直到你碰上了。 很多人知道要備份 MySQL，但真正的資料庫管理不能只問「有沒有 Backup？」而是還要關注： 如果今天資料庫真的壞了，我可以恢復到什麼時間點？需要多久？ 這就牽涉到： Backup（備份） Restore（還原） Recovery（復原） Full Backup（完整備份） Incremental Backup（增量備份） Binary Log Point-in-Time Recovery Disaster Recovery RPO RTO Backup 不等於 Recovery 假設每天凌晨 2:00 完成一次 Backup，今天下午 16:00 資料庫發生問題。 02:00 ───────────────── 16:00 Backup 故障 如果只能 Restore 凌晨 2:00 的 Backup，代表中間 14 小時的資料可能遺失。 有 Backup，不代表具備完整的 Recover…

MySQL 使用者、權限與資料庫安全完整指南

MySQL 使用者、權限與資料庫安全完整指南

Jesse Yen - 9月 05, 2026

安裝完 MySQL 之後，最方便的操作方式可能是什麼事情都使用 root。但是在真正的 Production Environment 中，這卻是非常危險的做法。 資料庫安全（Database Security）的核心原則不是只有「有沒有設定密碼」，還要考慮： 誰可以從哪裡登入，登入之後可以對哪些資料做什麼事情？ 因此 MySQL Security 至少包括： User Account (使用者帳戶) Authentication (驗證) Privilege (權限) GRANT (授予權限) REVOKE (取消權限) Role (角色) Least Privilege (最小權限) Network Access (網路存取) Encryption (加密) Audit (監看) Backup Security (備份安全) MySQL Account 不只是 Username MySQL 帳號通常由： 'user'@'host' 包含帳號以及後綴來共同決定，後綴就是決定這個帳號可以從何處登入。 例如： 'app'@'…

AI 時代如何評鑑學生的學習效果? 以 MySQL 為例

AI 時代如何評鑑學生的學習效果? 以 MySQL 為例

Jesse Yen - 7月 09, 2026

AI 時代如何評鑑學生的學習效果? 就是 考試的題目讓生成式 AI 也無法回答 ，但是怎麼辦到呢? 請慢慢讀下去。 現在的老師越來越難當，撇開學生的態度跟尊師重道低落不談，光是「教什麼」跟「怎麼教」就已經可以讓老師很為難了，至於「考什麼」更是困難。 以 MySQL 等資料庫的課程為例，老師要教的內容都可以從網路上或是生成式 AI 取得，給學生作業根本無法鑑別程度，因為大家都讓生成式 AI 產生答案。如果考試是 Open-Book，學生只是在練習抄答案。如果考試是 Close-Book，讓學生背那些無聊的語法，意義也不大。  「教什麼」? 要教「模式」而非僅是「知識」。 「怎麼教」? 應該從「問題」講起，在實作中再帶入「觀念」。 " 善用ChatGPT來教學MySQL : 教導模式而非單純知識 "提到，教學的重點應該是「模式」而非僅是「知識」。 這篇" 別再追逐工具：給老師的 AI 轉型指南 —— 從工具焦慮到「思考習慣」的系統升級 "也提到培養「思考習慣」，其實跟「模式」很像。 老師講課通常會先講觀念，但是學生總會認為「老師都教一些沒用的東西」，因此在" MySQL達人筆記 : 有了生成式AI，老師應該怎麼教MySQL資料庫知識? …

實作練習：員工午餐訂購系統的 Stored Procedure、Stored Function 與 Trigger 設計

實作練習：員工午餐訂購系統的 Stored Procedure、Stored Function 與 Trigger 設計

Jesse Yen - 6月 10, 2026

員工午餐訂購系統是一個常見的小系統，應該包含[訂餐系統]、[訂單管理系統]、[員工部門管理系統]、[合作店家與菜單管理系統] ，讓管理者可以維護員工、部門、合作店家與菜單，讓員工每天可以選擇餐廳與餐點，在中午前彙整所有訂單，每筆訂單要記錄訂購人、餐點、數量、價格與狀態，在月底可以統計每位員工的餐費，作為薪資扣款依據。 系統規模雖然小，但是已經具備線上電商的雛形。 這篇文章將使用 stored procedure、stored function、以及 trigger 來實作[訂餐系統]的程序，這是員工午餐訂購系統的核心作業。 本篇完成三件事： (1)使用 Trigger 防止庫存數量出現負數 (2)使用 Stored Function 查詢餐點剩餘庫存數量 (3)使用 Stored Procedure 完成訂餐扣庫存流程 [訂餐系統]需要考量以下幾個需求 : (1) 員工訂餐後，合作店家必須能夠供應，這就牽涉到餐點庫存管理。 (2) 餐點並非無限量供應，這就牽涉到訂單確認的先後順序。 (3) 一筆訂單有多項餐點時，又有多種處理方式 ~ 部分餐點庫存不足時，是否先確認有庫存的餐點? 還是整筆訂單取消? 還是由員工選擇? (4) 合作店家供餐時，是否允許任意分批出餐? 還是必須同筆項…

MySQL Lock 的搶票實作練習

MySQL Lock 的搶票實作練習

Jesse Yen - 6月 03, 2026

在這篇" MySQL Lock : Table Lock與Row Lock "，我們在兩個連線中，用指令展示了各種類型的鎖定，但是似乎還是有點不夠白話。 因此寫了一個演唱會搶票實作練習，來看看不同的鎖定，會造成什麼結果?  先看結論，如下圖 :  三個演唱會的售票統計中，可以看到 :  (1)「完全不用鎖定」的售票，會造成「超賣」的結果。 (2) 「使用 Select ... Lock in share mode 鎖定」的售票，一陣搶票中大家鎖成一團，結果票沒有賣完。 (3) 「使用 Select ... For Update 鎖定」的售票，雖然搶票，但是門票很正常的完全售完。 再來看看搶票的成功與失敗的統計，如下圖 :   在使用 Select ... Lock in share mode 中，搶到鎖定權的順利買到票，但是有人成為 Deadlock。而其他兩種情況 : 不使用鎖定與使用 Select ... for update 鎖定，搶票都沒有出現錯誤。 這個搶票實作練習，可以看到幾個很重要的結論。 (1) 不使用鎖定的搶票，容易產生「競爭條件 Race Condition」。 所謂競爭條件，不是指資料庫壞掉，也不是指 SQL 寫錯，而是多個使用者幾乎在同一…

實作練習 : 員工與部門資料管理系統

實作練習 : 員工與部門資料管理系統

Jesse Yen - 5月 06, 2026

現在要建立一套管理公司員工與部門資料的系統，讓企業能有效掌握：員工基本資料、部門組織架構、 員工與部門隸屬關係、 員工異動歷程、 聯絡資訊管理、 進行查詢、報表、統計分析。 每位員工的基本資料要能夠允許多筆地址、多筆電話、多筆電子郵件，並且要能夠紀錄員工的職務名稱，部門要能夠紀錄誰是部門經理、課長、主任等主管的關聯，並且要允許員工可以擔任多個部門的主管，例如某員工是技術部經理，並兼任管理部資訊長。其他未述明的需求，請自行合理化的假設。 (1) 請根據以上需求，畫出這個系統的實體關係圖 (Entity Relationship Diagram)， 請以陳品山博士提出的模式來繪製。  陳品山的實體關係圖模式可以參考這篇" 實體關係模型(Entity-relationship model) " 從以上的需求，可以明顯看出有兩個實體 : 員工、部門。 並且由於員工異動歷程也要記錄，因此需要再透過一個任職的關聯 (這個關聯也會形成資料表)。再來還有一個需求，允許員工可以擔任多個部門的主管，這個部分也可以放在任職的關聯上，開個職務資料表，與任職以編號欄位來對應。 並且因為員工的基本資料要能夠允許多筆地址、多筆電話、多筆電子郵件，員工可以擔任多個部門的主管，所以可以設計如下 …