mysql for update是鎖表還是鎖行

架构成长指南發表於2024-03-12
MySql

轉載至我的部落格 https://www.infrastack.cn ，公眾號：架構成長指南

在併發一致性控制場景中，我們常常用for update悲觀鎖來進行一致性的保證，但是如果不瞭解它的機制，就進行使用，很容易出現事故，比如for update進行了鎖表導致其他請求只能等待，從而拖垮系統，因此瞭解它的原理是非常必要的，下面我們透過一系列示例進行測試，來看看到底是什麼場景下鎖表什麼場景下鎖行

驗證

示例說明

建立一個賬戶表，插入基礎資料，以唯一索引、普通索引、主鍵、普通欄位4 個維度進行select ... for update查詢，檢視是進行鎖表還是鎖行

表建立

建立一個賬戶表，指定account_no為唯一索引、id為主鍵、user_no為普通欄位、curreny為普通索引

CREATE TABLE `account_info` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID' ,
	`account_no` int NOT NULL COMMENT '賬戶編號',
	`user_no` varchar(32) NOT NULL COMMENT '使用者 Id',
	`currency` varchar(10) NOT NULL COMMENT '幣種',
  `amount` DECIMAL(10,2) NOT NULL COMMENT '金額',
	`freeze_amount` DECIMAL(10,2) NOT NULL COMMENT '凍結金額',
  `create_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間',
  `update_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改時間',
  PRIMARY KEY (`id`) USING BTREE,
	UNIQUE KEY `uni_idx_account_no` (`account_no`) ,
	KEY `idx_currency_` (`currency`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='賬戶資訊表';

插入基礎資料

insert into account_info values (1,1,'ur001','RMB',100,0,now(),now());
insert into account_info values (2,2,'ur002','RMB',1000,0,now(),now());
insert into account_info values (3,3,'ur002','DOLLAR',200,0,now(),now());

根據主鍵查詢

在事務 1 中，根據主鍵id=1 進行 for update查詢時，事務2、事務 3 都進行阻塞，而事務 4 由於更新的id=2 所以成功，因此判定，根據主鍵進行 for update 查詢時是行鎖

根據唯一索引查詢

在事務 1 中，根據唯一索引account_no=1 進行 for update查詢時，事務2、事務 3 都進行阻塞，而事務 4 由於更新的account_no=2 所以成功，因此判定，根據唯一索引進行 for update 查詢時是行鎖

根據普通索引查詢

在事務 1 中，根據普通索引currency='RMB' 進行 for update查詢時，事務2、事務 3 都進行阻塞，而事務 4 由於更新的currency='DOLLAR`所以成功，因此判定，根據普通索引進行 for update 查詢時是行鎖

根據普通欄位查詢

在事務 1 中，根據普通欄位user_no='ur001' 進行 for update查詢時，事務2、事務 3 都進行阻塞，而事務 4查詢的是user_no='ur002'也進行阻塞，因此判定，根據普通欄位進行 for update 查詢時是表鎖

總結

如果查詢條件是索引/主鍵欄位，那麼select ..... for update會進行行鎖

如果查詢條件是普通欄位(沒有索引/主鍵)，那麼select ..... for update會進行鎖表，這點一定要注意。

掃描下面的二維碼關注我們的微信公眾帳號，在微信公眾帳號中回覆◉加群◉即可加入到我們的技術討論群裡面共同學習。

相關文章