软件开发培训班 >> 编程开发 >> MySQL


MySQL悲观锁与乐观锁的实现方案

鍓嶈█

鎮茶閿佸拰涔愯閿佹槸鐢ㄦ潵瑙ｅ喅骞跺彂闂鐨勪袱绉嶆�濇兂锛屽湪涓嶅悓鐨勫钩鍙版湁鐫�鍚勮嚜鐨勫疄鐜般�備緥濡傚湪Java涓紝synchronized灏卞彲浠ヨ涓烘槸鎮茶閿佺殑瀹炵幇(涓嶄弗璋紝鏈夐攣鍗囩骇鐨勮繃绋嬶紝鍗囩骇鍒伴噸閲忕骇閿佹墠绠?锛孉tomic***鍘熷瓙绫诲彲浠ヨ涓烘槸涔愯閿佺殑瀹炵幇銆?/p>

鎮茶閿?/strong>

鍏锋湁寮虹儓鐨勭嫭鍗犲拰鎺掍粬鐗规�э紝鍦ㄦ暣涓鐞嗚繃绋嬩腑灏嗘暟鎹浜庨攣瀹氱姸鎬侊紝涓�鑸槸閫氳繃绯荤粺鐨勪簰鏂ラ噺鏉ュ疄鐜般�傚綋鍏朵粬绾跨▼鎯宠鑾峰彇閿佹椂浼氳闃诲锛岀洿鍒版寔鏈夐攣鐨勭嚎绋嬮噴鏀鹃攣銆?/p>

涔愯閿?/strong>

瀵规暟鎹殑淇敼鍜岃闂寔涔愯鎬佸害锛屽亣璁句笉浼氬彂鐢熷啿绐侊紝鍙湁褰撴暟鎹彁浜ゆ洿鏂版椂鎵嶄細瀵规暟鎹啿绐佷笌鍚﹁繘琛屾娴嬶紝濡傛灉娌℃湁鍐茬獊鍒欓『鍒╂彁浜ゆ洿鏂帮紝鍚﹀垯蹇�熷け璐ワ紝杩斿洖涓�涓敊璇粰鐢ㄦ埛锛岃鐢ㄦ埛閫夋嫨鎺ヤ笅鏉ヨ濡備綍鍘诲仛锛屼竴鑸潵璇村け璐ュ悗浼氱户缁噸璇曪紝鐩村埌鎻愪氦鏇存柊鎴愬姛涓烘銆?/p>

MySQL鏈韩灏辨敮鎸侀攣鏈哄埗锛屼緥濡傛垜浠湁涓�涓�屽厛鏌ュ啀鍐欍�嶇殑闇�姹傦紝鎴戜滑甯屾湜鏁翠釜娴佺▼鏄竴涓師瀛愭搷浣滐紝涓棿涓嶈兘琚墦鏂紝杩欐椂鍊欏氨鍙互閫氳繃缁欐煡璇㈢殑鏁版嵁琛屽姞銆屾帓浠栭攣銆嶆潵瀹炵幇銆傚彧瑕佸綋鍓嶄簨鍔′笉閲婃斁閿侊紝鍏朵粬浜嬪姟瑕佹兂鑾峰緱鎺掍粬閿侊紝MySQL灏变細灏嗗叾闃诲锛岀洿鍒板綋鍓嶄簨鍔￠噴鏀鹃攣銆傝繖绉峂ySQL搴曞眰鐨勬帓浠栭攣灏辩О浣溿�屾偛瑙傞攣銆嶃�?/p>

MySQL鏈韩涓嶆彁渚涗箰瑙傞攣鐨勫姛鑳斤紝闇�瑕佸紑鍙戣�呰嚜宸卞疄鐜般�傛櫘閬嶇殑鍋氭硶鏄湪琛ㄤ腑鍔犱竴涓獀ersion鍒楋紝鐢ㄦ潵鏍囪鏁版嵁琛岀殑鐗堟湰锛屽綋鎴戜滑闇�瑕佹洿鏂版暟鎹椂锛屽繀椤绘瘮瀵箆ersion鐗堟湰锛寁ersion涓�鑷磋鏄庤繖涓湡闂存暟鎹病鏈夎鍏朵粬浜嬪姟淇敼杩囷紝鍚﹀垯璇存槑鏁版嵁宸茬粡琚叾浠栦簨鍔′慨鏀癸紝闇�瑕佽嚜鏃嬮噸璇曚簡銆?/p>

瀹炴垬

鍋囪鏁版嵁搴撴湁涓ゅ紶琛細鍟嗗搧琛ㄥ拰璁㈠崟琛ㄣ�?/p>

鐢ㄦ埛涓嬪崟鍚庨渶瑕佹墽琛屼袱涓搷浣滐細

  1. 鍟嗗搧琛ㄥ噺鍘诲簱瀛樸�?/li>
  2. 璁㈠崟琛ㄥ垱寤轰竴鏉¤褰曘�?/li>

鍒濆鏁版嵁锛欼D涓?鐨勫晢鍝佹湁100鐨勫簱瀛橈紝璁㈠崟琛ㄦ暟鎹负绌恒�?/p>

瀹㈡埛绔惎鍔?0涓嚎绋嬪苟鍙戜笅鍗曪紝鍒嗗埆鍦ㄦ棤閿併�佹偛瑙傞攣銆佷箰瑙傞攣鐨勫満鏅笅鏈夊摢浜涜〃鐜般�?/p>

濡備笅鏄垱寤鸿〃鐨剆ql璇彞锛?/p> 

-- 鍟嗗搧琛?
CREATE TABLE `goods` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(50) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `stock` int(11) DEFAULT '0',
  `version` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

-- 璁㈠崟琛?
CREATE TABLE `t_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `goods_id` bigint(20) NOT NULL,
  `order_time` datetime NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

1銆佹棤閿?/h2>

涓嶅仛浠讳綍澶勭悊銆?/p> 

// 涓嬪崟
private boolean order(){
    Goods goods = goodsMapper.selectById(1L);
    boolean success = false;
    if (goods.getStock() > 0) {
        goods.setStock(goods.getStock() - 1);
        // 鏇存柊搴撳瓨
        goodsMapper.updateById(goods);
        // 鍒涘缓璁㈠崟
        orderMapper.save(goods.getId());
        success = true;
    }
    return success;
}

鎺у埗鍙拌緭鍑虹粨鏋滐細

2銆佹偛瑙傞攣

鏌ヨ鍟嗗搧鏃跺姞FOR UPDATE锛岀粰鏁版嵁琛屽姞鎺掍粬閿侊紝杩欐牱鍏朵粬绾跨▼鍐嶆煡璇㈡椂灏变細琚樆濉烇紝鐩村埌褰撳墠绾跨▼鐨勪簨鍔℃彁浜ゅ苟閲婃斁閿侊紝鍏朵粬绾跨▼鎵嶈兘缁х画涓嬪崟銆傝繖绉嶆柟寮忓苟鍙戞�ц兘涓嶉珮銆?/p>

sql璇彞

@Select("SELECT * FROM goods WHERE id = #{id} FOR UPDATE")
Goods selectForUpdate(Long id);

鎺у埗鍙拌緭鍑虹粨鏋滐細

娉ㄦ剰锛欶OR UPDATE蹇呴』鍦ㄤ簨鍔′腑鎵嶆湁鏁堬紝鏌ヨ鍜屾洿鏂板繀椤诲湪鍚屼竴涓簨鍔′腑锛侊紒锛?/p>

3銆佷箰瑙傞攣

瀹炵幇鎬濊矾鏄細姣忔鏇存柊鏃舵牎楠岀増鏈彿锛屽鏋滅増鏈彿涓�鑷磋鏄庢湡闂存暟鎹病鏈夎鍏朵粬绾跨▼鏀硅繃锛屽綋鍓嶇嚎绋嬪彲浠ユ甯告彁浜ゆ洿鏂帮紝鍚﹀垯璇存槑鏁版嵁宸茬粡琚叾浠栫嚎绋嬫敼杩囦簡锛屽綋鍓嶇嚎绋嬮渶瑕佽嚜鏃嬮噸璇曪紝鐩村埌涓氬姟鎴愬姛涓烘銆?/p>

鏇存柊鏁版嵁鐨勫悓鏃剁増鏈彿蹇呴』鑷锛侊紒锛?/p> 

@Update("UPDATE goods SET stock = #{stock},version = version+1 WHERE id = #{id} AND version = #{version}")
int updateByVersion(Long id, Integer stock, Integer version);

涓氬姟浠ｇ爜

boolean order(){
    Goods goods = goodsMapper.selectById(1L);
    boolean success = false;
    if (goods.getStock() > 0) {
        goods.setStock(goods.getStock() - 1);
        // 鏇存柊搴撳瓨锛屽甫涓婄増鏈彿
        int result = goodsMapper.updateByVersion(goods.getId(), goods.getStock(), goods.getVersion());
        if (result <= 0) {
            // 鏇存柊澶辫触锛岃鏄庢湡闂存暟鎹凡缁忚鍏朵粬绾跨▼淇敼锛岄渶瑕侀�掑綊閲嶈瘯
            return order();
        }
        // 鍒涘缓璁㈠崟
        orderMapper.save(goods.getId());
        success = true;
    }
    return success;
}

鎺у埗鍙拌緭鍑虹粨鏋滐細

鎬荤粨

鍒版杩欑瘒鍏充簬MySQL鎮茶閿佷笌涔愯閿佹柟妗堢殑鏂囩珷灏变粙缁嶅埌杩欎簡,鏇村鐩稿叧MySQL鎮茶閿佷笌涔愯閿佸唴瀹硅鎼滅储鑴氭湰涔嬪浠ュ墠鐨勬枃绔犳垨缁х画娴忚涓嬮潰鐨勭浉鍏虫枃绔犲笇鏈涘ぇ瀹朵互鍚庡澶氭敮鎸佽剼鏈箣瀹讹紒