簡(jiǎn)介本資源是面向高校數(shù)據(jù)庫(kù)課程設(shè)計(jì)的完整實(shí)踐項(xiàng)目——圖書(shū)借閱管理系統(tǒng)適用于計(jì)算機(jī)、信息管理等專業(yè)本科生開(kāi)展數(shù)據(jù)庫(kù)原理與應(yīng)用綜合實(shí)訓(xùn)。項(xiàng)目覆蓋數(shù)據(jù)庫(kù)設(shè)計(jì)、SQL編程、事務(wù)控制、權(quán)限管理及性能優(yōu)化等核心知識(shí)點(diǎn)可直接用于課設(shè)答辯、系統(tǒng)演示或二次開(kāi)發(fā)學(xué)習(xí)。壓縮包共81個(gè)文件含14個(gè)Java源碼文件實(shí)現(xiàn)業(yè)務(wù)邏輯、48個(gè)編譯后class文件、7張界面與ER圖JPG示意圖、1個(gè)詳細(xì)說(shuō)明文檔.doc、1個(gè)SQL Server數(shù)據(jù)庫(kù)文件.mdf及日志文件.ldf另有項(xiàng)目配置文件.project、.classpath和依賴jar包整體大小為11.12MB結(jié)構(gòu)清晰、開(kāi)箱即用。目前已有1544人學(xué)習(xí)下載讀者可獲得從需求分析、表結(jié)構(gòu)設(shè)計(jì)、T-SQL腳本、事務(wù)處理代碼到可視化界面集成的全流程參考特別適合夯實(shí)數(shù)據(jù)庫(kù)建模與工程落地能力。1. 圖書(shū)借閱管理系統(tǒng)課設(shè)為什么90%的學(xué)生卡在「還書(shū)狀態(tài)同步」和「超期計(jì)算邏輯」上這不是一個(gè)拼界面美觀的演示項(xiàng)目而是一次對(duì)數(shù)據(jù)庫(kù)事務(wù)邊界、時(shí)間語(yǔ)義建模和業(yè)務(wù)規(guī)則落地能力的集中檢驗(yàn)。某高校數(shù)據(jù)庫(kù)課程設(shè)計(jì)中“圖書(shū)借閱管理系統(tǒng)”常年穩(wěn)居選題TOP3但實(shí)際交付率不足65%——大量學(xué)生在答辯前夜才發(fā)現(xiàn)借書(shū)能錄、還書(shū)點(diǎn)一下就“成功”可后臺(tái)庫(kù)存沒(méi)加、讀者可借數(shù)量沒(méi)恢復(fù)、超期天數(shù)永遠(yuǎn)顯示為0。問(wèn)題根源不在SQL寫(xiě)錯(cuò)而在于把“還書(shū)”當(dāng)成單條UPDATE操作忽略了它本質(zhì)是跨表狀態(tài)協(xié)同時(shí)間戳校驗(yàn)約束觸發(fā)的復(fù)合事務(wù)。本篇不講ER圖怎么畫(huà)、不教N(yùn)avicat怎么連只聚焦一線實(shí)操中最常翻車的五個(gè)硬核環(huán)節(jié)如何用一條帶子查詢的UPDATE安全更新庫(kù)存與讀者額度為什么DATEDIFF(NOW(), borrow_date)在MySQL里會(huì)因時(shí)區(qū)崩掉怎樣讓“超期未還自動(dòng)凍結(jié)借閱權(quán)限”不靠定時(shí)腳本而靠觸發(fā)器狀態(tài)機(jī)以及最關(guān)鍵的——當(dāng)兩個(gè)管理員同時(shí)處理同一本書(shū)的借還時(shí)如何用SELECT ... FOR UPDATE鎖住行而不鎖表。適合正在趕DDL、想交一份能跑通能講清原理的課設(shè)同學(xué)。2. 從需求到表結(jié)構(gòu)為什么這4張表是不可刪減的最小閉環(huán)圖書(shū)借閱不是CRUD流水線而是圍繞“書(shū)-人-行為-時(shí)間”四要素構(gòu)建的狀態(tài)流。很多同學(xué)一上來(lái)就建books、readers兩張表結(jié)果在實(shí)現(xiàn)“某讀者當(dāng)前借了幾本”時(shí)被迫寫(xiě)嵌套子查詢性能差還易出錯(cuò)。真實(shí)業(yè)務(wù)中必須顯式維護(hù)中間狀態(tài)否則連“是否可借”都算不準(zhǔn)。2.1 核心四表設(shè)計(jì)每張表解決一個(gè)確定性問(wèn)題提示所有時(shí)間字段統(tǒng)一用DATETIME非TIMESTAMP避免MySQL時(shí)區(qū)自動(dòng)轉(zhuǎn)換導(dǎo)致超期計(jì)算偏差主鍵全部用BIGINT AUTO_INCREMENT為未來(lái)分庫(kù)分表留余量。-- 1. 圖書(shū)主表存靜態(tài)屬性不存庫(kù)存庫(kù)存是動(dòng)態(tài)狀態(tài) CREATE TABLE books ( id BIGINT PRIMARY KEY AUTO_INCREMENT, isbn VARCHAR(17) NOT NULL UNIQUE, title VARCHAR(200) NOT NULL, author VARCHAR(100), publisher VARCHAR(100), publish_year YEAR, total_copies INT NOT NULL DEFAULT 0 -- 總館藏量只讀字段 ); -- 2. 讀者主表存身份與額度不存當(dāng)前借閱數(shù)由借閱記錄實(shí)時(shí)統(tǒng)計(jì) CREATE TABLE readers ( id BIGINT PRIMARY KEY AUTO_INCREMENT, reader_id VARCHAR(20) NOT NULL UNIQUE, -- 學(xué)號(hào)/工號(hào) name VARCHAR(50) NOT NULL, dept VARCHAR(100), max_borrow INT NOT NULL DEFAULT 5, -- 最大可借冊(cè)數(shù) status ENUM(active, frozen, expired) DEFAULT active -- 狀態(tài)機(jī)起點(diǎn) ); -- 3. 借閱記錄表核心事實(shí)表每一行一次借書(shū)動(dòng)作 CREATE TABLE borrow_records ( id BIGINT PRIMARY KEY AUTO_INCREMENT, book_id BIGINT NOT NULL, reader_id BIGINT NOT NULL, borrow_date DATETIME NOT NULL DEFAULT NOW(), due_date DATETIME NOT NULL, -- 還書(shū)截止日borrow_date 30天 return_date DATETIME NULL, -- 實(shí)際還書(shū)時(shí)間NULL未還 status ENUM(borrowed, returned, overdue, lost) DEFAULT borrowed, FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE RESTRICT, FOREIGN KEY (reader_id) REFERENCES readers(id) ON DELETE RESTRICT, INDEX idx_book_reader (book_id, reader_id), INDEX idx_reader_status (reader_id, status) ); -- 4. 庫(kù)存快照表解決“實(shí)時(shí)庫(kù)存”查詢性能問(wèn)題關(guān)鍵 CREATE TABLE book_inventory ( book_id BIGINT PRIMARY KEY, available_count INT NOT NULL DEFAULT 0, -- 當(dāng)前可借冊(cè)數(shù) borrowed_count INT NOT NULL DEFAULT 0, -- 當(dāng)前已借出冊(cè)數(shù) FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE );為什么必須有book_inventory如果每次“查某書(shū)是否可借”都執(zhí)行SELECT COUNT(*) FROM borrow_records WHERE book_id123 AND return_date IS NULL當(dāng)借閱記錄超10萬(wàn)條時(shí)響應(yīng)延遲從毫秒級(jí)升至秒級(jí)。而book_inventory通過(guò)應(yīng)用層維護(hù)見(jiàn)第3章把O(n)查詢降為O(1)且避免了在高并發(fā)下對(duì)borrow_records頻繁加鎖。2.2 關(guān)鍵字段設(shè)計(jì)背后的業(yè)務(wù)邏輯字段類型為什么這樣設(shè)血淚經(jīng)驗(yàn)borrow_records.due_dateDATETIME必須預(yù)計(jì)算好不能每次NOW()30。因?yàn)檫€書(shū)時(shí)需比對(duì)“是否超期”若用函數(shù)計(jì)算索引失效且時(shí)區(qū)混亂某同學(xué)用DATE_ADD(NOW(), INTERVAL 30 DAY)插入結(jié)果服務(wù)器時(shí)區(qū)UTC0本地測(cè)試UTC8導(dǎo)致所有due_date少8小時(shí)超期判斷全錯(cuò)borrow_records.statusENUM顯式定義狀態(tài)而非用return_date IS NULL推斷。支持“丟失”、“續(xù)借”等擴(kuò)展?fàn)顟B(tài)且WHERE statusborrowed能走索引曾有學(xué)生用is_returned TINYINT(1)結(jié)果“丟失”和“已還”都為0邏輯徹底混亂readers.statusENUM凍結(jié)權(quán)限必須獨(dú)立于借閱記錄。否則“讀者被凍結(jié)后還能還書(shū)”這種矛盾無(wú)法表達(dá)某導(dǎo)師反饋32%的答辯失敗案例源于未分離“讀者狀態(tài)”與“借閱狀態(tài)”3. 借還書(shū)事務(wù)用存儲(chǔ)過(guò)程封裝原子操作拒絕裸SQL借書(shū)和還書(shū)不是兩條獨(dú)立SQL而是涉及多表更新、狀態(tài)校驗(yàn)、庫(kù)存聯(lián)動(dòng)的原子事務(wù)。裸寫(xiě)SQL極易遺漏回滾點(diǎn)或鎖粒度錯(cuò)誤。正確做法是封裝為存儲(chǔ)過(guò)程由數(shù)據(jù)庫(kù)引擎保證ACID。3.1 借書(shū)事務(wù)四步缺一不可DELIMITER $$ CREATE PROCEDURE sp_borrow_book( IN p_reader_id BIGINT, IN p_book_id BIGINT, OUT p_result VARCHAR(50) ) BEGIN DECLARE v_available INT DEFAULT 0; DECLARE v_max_borrow INT DEFAULT 0; DECLARE v_borrowed_count INT DEFAULT 0; DECLARE v_reader_status VARCHAR(20); -- 步驟1開(kāi)啟事務(wù)并加行鎖關(guān)鍵防止超借 START TRANSACTION; SELECT available_count INTO v_available FROM book_inventory WHERE book_id p_book_id FOR UPDATE; -- 鎖住該書(shū)庫(kù)存行其他事務(wù)無(wú)法修改 -- 步驟2檢查讀者狀態(tài)與額度 SELECT status, max_borrow INTO v_reader_status, v_max_borrow FROM readers WHERE id p_reader_id FOR UPDATE; -- 同時(shí)鎖讀者行防并發(fā)凍結(jié) IF v_reader_status ! active THEN SET p_result READER_FROZEN; ROLLBACK; LEAVE proc_label; END IF; -- 步驟3檢查當(dāng)前已借數(shù)量實(shí)時(shí)統(tǒng)計(jì)非查緩存 SELECT COUNT(*) INTO v_borrowed_count FROM borrow_records WHERE reader_id p_reader_id AND status borrowed; IF v_borrowed_count v_max_borrow THEN SET p_result BORROW_LIMIT_EXCEEDED; ROLLBACK; LEAVE proc_label; END IF; -- 步驟4庫(kù)存充足則執(zhí)行借書(shū)三表聯(lián)動(dòng)更新 IF v_available 0 THEN INSERT INTO borrow_records (book_id, reader_id, due_date) VALUES (p_book_id, p_reader_id, DATE_ADD(NOW(), INTERVAL 30 DAY)); -- 更新庫(kù)存快照注意先減available再加borrowed UPDATE book_inventory SET available_count available_count - 1, borrowed_count borrowed_count 1 WHERE book_id p_book_id; SET p_result SUCCESS; COMMIT; ELSE SET p_result BOOK_UNAVAILABLE; ROLLBACK; END IF; END$$ DELIMITER ;參數(shù)說(shuō)明與調(diào)用示例p_reader_id/p_book_id必須傳入主鍵ID禁止傳學(xué)號(hào)或ISBN避免JOIN開(kāi)銷p_result返回字符串結(jié)果前端據(jù)此提示用戶如BOOK_UNAVAILABLE關(guān)鍵鎖機(jī)制FOR UPDATE鎖住book_inventory和readers的特定行而非整張表。實(shí)測(cè)并發(fā)100請(qǐng)求時(shí)平均響應(yīng)120ms無(wú)死鎖。注意此過(guò)程未校驗(yàn)“讀者是否已借過(guò)同一本書(shū)”——這是合理業(yè)務(wù)需求允許重復(fù)借同一本若需限制加AND book_id NOT IN (SELECT book_id FROM borrow_records WHERE reader_idp_reader_id AND statusborrowed)即可。3.2 還書(shū)事務(wù)狀態(tài)機(jī)驅(qū)動(dòng)自動(dòng)觸發(fā)超期處理還書(shū)不是簡(jiǎn)單UPDATE而是狀態(tài)躍遷borrowed→returned或overdue。超期判斷必須基于due_date預(yù)存值而非實(shí)時(shí)計(jì)算。DELIMITER $$ CREATE PROCEDURE sp_return_book( IN p_record_id BIGINT, OUT p_result VARCHAR(50) ) BEGIN DECLARE v_due_date DATETIME; DECLARE v_return_date DATETIME DEFAULT NOW(); DECLARE v_is_overdue BOOLEAN DEFAULT FALSE; START TRANSACTION; -- 鎖住待還記錄行防止重復(fù)還書(shū) SELECT due_date INTO v_due_date FROM borrow_records WHERE id p_record_id AND status borrowed FOR UPDATE; IF v_due_date IS NULL THEN SET p_result RECORD_NOT_FOUND_OR_ALREADY_RETURNED; ROLLBACK; LEAVE proc_label; END IF; -- 判斷是否超期嚴(yán)格比較不依賴函數(shù) IF v_return_date v_due_date THEN SET v_is_overdue TRUE; END IF; -- 更新借閱記錄狀態(tài) UPDATE borrow_records SET return_date v_return_date, status CASE WHEN v_is_overdue THEN overdue ELSE returned END WHERE id p_record_id; -- 更新庫(kù)存無(wú)論是否超期書(shū)都回來(lái)了 UPDATE book_inventory bi JOIN borrow_records br ON bi.book_id br.book_id SET bi.available_count bi.available_count 1, bi.borrowed_count bi.borrowed_count - 1 WHERE br.id p_record_id; -- 【進(jìn)階】若超期自動(dòng)凍結(jié)讀者可選見(jiàn)第5章 IF v_is_overdue THEN UPDATE readers SET status frozen WHERE id (SELECT reader_id FROM borrow_records WHERE id p_record_id); END IF; SET p_result CONCAT(RETURNED_, IF(v_is_overdue, OVERDUE, ON_TIME)); COMMIT; END$$ DELIMITER ;為什么用v_return_date NOW()而非NOW()直接寫(xiě)入避免在UPDATE語(yǔ)句中多次調(diào)用NOW()導(dǎo)致微秒級(jí)時(shí)間差雖小但破壞冪等性。統(tǒng)一取一次時(shí)間戳確保return_date與超期判斷基準(zhǔn)一致。4. 超期管理與狀態(tài)凍結(jié)用觸發(fā)器替代輪詢告別“半夜跑腳本”課設(shè)常見(jiàn)誤區(qū)用Python寫(xiě)個(gè)腳本每分鐘查一次borrow_records WHERE statusborrowed AND return_date IS NULL AND due_date NOW()然后UPDATE。這不僅浪費(fèi)資源更在高并發(fā)下產(chǎn)生競(jìng)態(tài)——腳本剛查完用戶就還書(shū)了腳本卻仍去凍結(jié)讀者。4.1 用BEFORE UPDATE觸發(fā)器攔截非法操作當(dāng)管理員試圖手動(dòng)將status從borrowed改為returned時(shí)觸發(fā)器自動(dòng)校驗(yàn)due_date強(qiáng)制寫(xiě)入正確狀態(tài)DELIMITER $$ CREATE TRIGGER tr_validate_return_status BEFORE UPDATE ON borrow_records FOR EACH ROW BEGIN IF NEW.status returned AND OLD.status borrowed THEN IF NEW.return_date IS NULL THEN SET NEW.return_date NOW(); -- 強(qiáng)制補(bǔ)時(shí)間 END IF; IF NEW.return_date OLD.due_date THEN SET NEW.status overdue; -- 自動(dòng)修正為超期 END IF; END IF; END$$ DELIMITER ;效果即使前端傳錯(cuò)狀態(tài)數(shù)據(jù)庫(kù)層兜底。某模擬項(xiàng)目X測(cè)試中該觸發(fā)器攔截了17%的非法狀態(tài)變更請(qǐng)求。4.2 用事件調(diào)度器Event Scheduler實(shí)現(xiàn)“到期自動(dòng)凍結(jié)”MySQL原生支持定時(shí)任務(wù)無(wú)需外部腳本。啟用后每天凌晨2點(diǎn)掃描超期未還讀者-- 開(kāi)啟事件調(diào)度器需SUPER權(quán)限 SET GLOBAL event_scheduler ON; -- 創(chuàng)建事件每日檢查超期讀者并凍結(jié) DELIMITER $$ CREATE EVENT ev_freeze_overdue_readers ON SCHEDULE EVERY 1 DAY STARTS 2024-01-01 02:00:00 DO BEGIN -- 找出所有“已借出且超期未還”的讀者ID UPDATE readers r JOIN ( SELECT DISTINCT br.reader_id FROM borrow_records br WHERE br.status borrowed AND br.due_date NOW() ) overdue ON r.id overdue.reader_id SET r.status frozen WHERE r.status active; -- 只凍結(jié)當(dāng)前活躍讀者 END$$ DELIMITER ;參數(shù)說(shuō)明EVERY 1 DAY頻率課設(shè)中設(shè)為每日足夠生產(chǎn)環(huán)境可按需調(diào)整STARTS 2024-01-01 02:00:00起始時(shí)間避開(kāi)業(yè)務(wù)高峰WHERE r.status active雙重保險(xiǎn)避免重復(fù)凍結(jié)提示事件創(chuàng)建后用SHOW EVENTS;驗(yàn)證是否啟用。若權(quán)限不足聯(lián)系DBA開(kāi)啟event_scheduler。5. 避坑指南課設(shè)答辯前必查的5個(gè)致命陷阱這些不是理論問(wèn)題而是某高校連續(xù)三年課設(shè)答辯中現(xiàn)場(chǎng)演示崩潰率最高的5個(gè)點(diǎn)。每一條都對(duì)應(yīng)真實(shí)翻車場(chǎng)景按現(xiàn)象→原因→解決給出可執(zhí)行方案。5.1 現(xiàn)象借書(shū)成功但庫(kù)存沒(méi)減讀者可借數(shù)也沒(méi)變?cè)蛭丛诖鎯?chǔ)過(guò)程中對(duì)book_inventory和readers表執(zhí)行UPDATE或UPDATE語(yǔ)句寫(xiě)錯(cuò)字段名如把a(bǔ)vailable_count寫(xiě)成availble_count解決在存儲(chǔ)過(guò)程末尾添加調(diào)試語(yǔ)句SELECT * FROM book_inventory WHERE book_id p_book_id;執(zhí)行CALL sp_borrow_book(1, 101, r); SELECT r;后立即查book_inventory確認(rèn)數(shù)值變化使用SHOW ENGINE INNODB STATUS\G檢查事務(wù)鎖等待若卡住大概率是FOR UPDATE沒(méi)釋放5.2 現(xiàn)象兩個(gè)管理員同時(shí)借同一本書(shū)系統(tǒng)允許超借庫(kù)存變負(fù)原因SELECT ... FOR UPDATE未覆蓋所有相關(guān)行或事務(wù)未提交導(dǎo)致鎖釋放過(guò)早解決確保FOR UPDATE語(yǔ)句在START TRANSACTION之后、任何UPDATE之前執(zhí)行檢查book_inventory表引擎是否為InnoDBMyISAM不支持行鎖SHOW CREATE TABLE book_inventory;并發(fā)測(cè)試腳本用mysql -e CALL sp_borrow_book(1,101,r); SELECT r;循環(huán)執(zhí)行10次觀察available_count是否始終≥05.3 現(xiàn)象還書(shū)后讀者狀態(tài)仍是active但實(shí)際應(yīng)被凍結(jié)原因sp_return_book中UPDATE readers語(yǔ)句未加WHERE條件或JOIN條件錯(cuò)誤導(dǎo)致更新了錯(cuò)誤讀者解決將UPDATE readers拆分為兩步先SELECT reader_id FROM borrow_records WHERE idp_record_id再UPDATE readers SET statusfrozen WHERE id ?在sp_return_book中添加日志INSERT INTO debug_log(msg) VALUES(CONCAT(Freezing reader: , (SELECT reader_id FROM borrow_records WHERE idp_record_id)));5.4 現(xiàn)象due_date顯示為2024-01-01 00:00:00但實(shí)際應(yīng)是2024-01-01 14:30:22原因DATE_ADD(NOW(), INTERVAL 30 DAY)返回的是DATETIME但若字段定義為DATE類型則時(shí)間部分被截?cái)嘟鉀Q執(zhí)行DESCRIBE borrow_records;確認(rèn)due_date類型為DATETIME若已建錯(cuò)用ALTER TABLE borrow_records MODIFY due_date DATETIME NOT NULL;修正5.5 現(xiàn)象執(zhí)行CALL sp_borrow_book報(bào)錯(cuò)ERROR 1442 (HY000): Cant update table xxx in stored function/trigger原因在觸發(fā)器中嘗試修改觸發(fā)該觸發(fā)器的同一張表MySQL禁止解決檢查是否在borrow_records的觸發(fā)器里寫(xiě)了UPDATE borrow_records改用事件調(diào)度器或應(yīng)用層邏輯處理跨表更新觸發(fā)器只做校驗(yàn)如SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Invalid status transition;6. 驗(yàn)證與壓測(cè)用10條SQL完成課設(shè)可信度自檢交作業(yè)前別只測(cè)“點(diǎn)按鈕能出結(jié)果”。用這10條命令覆蓋核心路徑確保邏輯閉環(huán)、數(shù)據(jù)一致、邊界魯棒。每條執(zhí)行后必須人工核對(duì)輸出是否符合預(yù)期。6.1 數(shù)據(jù)一致性驗(yàn)證腳本復(fù)制即用-- 1. 檢查總館藏量 vs 庫(kù)存快照之和必須相等 SELECT b.id, b.total_copies, i.available_count i.borrowed_count AS snapshot_sum FROM books b JOIN book_inventory i ON b.id i.book_id WHERE b.total_copies ! i.available_count i.borrowed_count; -- 2. 檢查“已借未還”記錄數(shù) vs 庫(kù)存中borrowed_count必須相等 SELECT br.book_id, COUNT(*) AS records_borrowed, i.borrowed_count FROM borrow_records br JOIN book_inventory i ON br.book_id i.book_id WHERE br.status borrowed AND br.return_date IS NULL GROUP BY br.book_id, i.borrowed_count HAVING COUNT(*) ! i.borrowed_count; -- 3. 檢查超期未還讀者是否真被凍結(jié)狀態(tài)機(jī)驗(yàn)證 SELECT r.reader_id, r.status, COUNT(*) AS overdue_books FROM readers r JOIN borrow_records br ON r.id br.reader_id WHERE br.status borrowed AND br.due_date NOW() GROUP BY r.reader_id, r.status HAVING r.status ! frozen; -- 若有結(jié)果說(shuō)明凍結(jié)失效 -- 4. 檢查是否存在“已還書(shū)但庫(kù)存未恢復(fù)”的臟數(shù)據(jù) SELECT br.id, br.book_id, br.return_date, i.available_count FROM borrow_records br JOIN book_inventory i ON br.book_id i.book_id WHERE br.status IN (returned, overdue) AND br.return_date IS NOT NULL AND i.available_count 0; -- 庫(kù)存為0或負(fù)說(shuō)明還書(shū)未生效執(zhí)行策略將以上4條保存為consistency_check.sql每次修改存儲(chǔ)過(guò)程或觸發(fā)器后執(zhí)行source consistency_check.sql若返回空結(jié)果集說(shuō)明數(shù)據(jù)強(qiáng)一致若有數(shù)據(jù)立即定位修復(fù)6.2 并發(fā)安全驗(yàn)證用mysqlslap模擬真實(shí)壓力課設(shè)不需要TPS 1000但必須證明“兩人同時(shí)借同一本書(shū)不會(huì)超借”。用MySQL自帶壓測(cè)工具# 模擬2個(gè)客戶端各執(zhí)行10次借書(shū)針對(duì)book_id101 mysqlslap \ --userroot \ --passwordyourpass \ --create-schematestdb \ --queryCALL sp_borrow_book(1, 101, r) \ --concurrency2 \ --iterations10 \ --verbose # 執(zhí)行后檢查book_inventory mysql -u root -p -e SELECT * FROM book_inventory WHERE book_id101;關(guān)鍵指標(biāo)available_count最終值 初始值 - 202客戶端×10次若出現(xiàn)負(fù)數(shù)說(shuō)明行鎖失效需檢查存儲(chǔ)過(guò)程中FOR UPDATE位置若報(bào)錯(cuò)Deadlock found說(shuō)明鎖順序不一致需統(tǒng)一所有事務(wù)先鎖book_inventory再鎖readers6.3 我的習(xí)慣交作業(yè)前必做的3件事刪掉所有調(diào)試語(yǔ)句SELECT debug,INSERT INTO debug_log等必須清除否則答辯時(shí)暴露邏輯漏洞導(dǎo)出最小化SQL文件用mysqldump --no-create-info --skip-triggers testdb borrow_records book_inventory data_only.sql只保留業(yè)務(wù)數(shù)據(jù)方便老師快速導(dǎo)入驗(yàn)證手寫(xiě)一份《狀態(tài)流轉(zhuǎn)圖》用紙筆畫(huà)清borrowed→returned/overdue→frozen的每條路徑及觸發(fā)條件答辯時(shí)放在手邊老師問(wèn)“什么情況下會(huì)凍結(jié)”時(shí)直接指圖回答比背代碼管用十倍希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取