分布式鎖mysql實(shí)現(xiàn)方式
方式1：唯一索引

• 創(chuàng)建鎖表，內(nèi)部存在字段表示資源名及資源描述，同一資源名使用數(shù)據(jù)庫(kù)唯一性限制。
• 多個(gè)進(jìn)程同時(shí)往數(shù)據(jù)庫(kù)鎖表中寫入對(duì)某個(gè)資源的占有記錄，當(dāng)某個(gè)進(jìn)程成功寫入時(shí)則表示其獲取鎖成功
• 其他進(jìn)程由于資源字段唯一性限制插入失敗陷入自旋并且失敗重試。
• 當(dāng)執(zhí)行完業(yè)務(wù)后持有該鎖的進(jìn)程則刪除該表內(nèi)的記錄，此時(shí)回到步驟一。
  
  表數(shù)據(jù)

create table `database_lock`(`id` BIGINT NOT NULL AUTO_INCREMENT,`resource` INT NOT NULL COMMENT '鎖資源',`description` varchar(1024) NOT NULL DEFAULT "" COMMENT '描述',PRIMARY KEY (`id`),UNIQUE KEY `resource` (`resource`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='數(shù)據(jù)庫(kù)分布式鎖表';

db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/distribute_lock?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=Asia/Shanghai
user=root
password=123456

• PropertiesReader

@Slf4j
public class PropertiesReader {// Properties緩存文件private static final Map<String, Properties> propertiesCache = new HashMap<String, Properties>();public static Properties getProperties(String propertiesName) throws IOException {if (propertiesCache.containsKey(propertiesName)) {return propertiesCache.get(propertiesName);}loadProperties(propertiesName);return propertiesCache.get(propertiesName);}private synchronized static void loadProperties(String propertiesName) throws IOException {FileReader fileReader = null;try {// 創(chuàng)建Properties集合類Properties pro = new Properties();// 獲取src路徑下的文件--->ClassLoader類加載器ClassLoader classLoader = PropertiesReader.class.getClassLoader();URL resource = classLoader.getResource(propertiesName);// 獲取配置路徑String path = resource.getPath();// 讀取文件fileReader = new FileReader(path);// 加載文件pro.load(fileReader);// 初始化propertiesCache.put(propertiesName, pro);} catch (IOException e) {log.error("讀取Properties文件失敗，Properties名為:" + propertiesName);throw e;} finally {try {if (fileReader != null) {fileReader.close();}} catch (IOException e) {log.error("fileReader關(guān)閉失敗！", e);}}}
}

• JDBCUtils

@Slf4j
public class JDBCUtils {private static String url;private static String user;private static String password;static {//讀取文件，獲取值try {Properties properties = PropertiesReader.getProperties("db.properties");url = properties.getProperty("url");user = properties.getProperty("user");password = properties.getProperty("password");String driver = properties.getProperty("driver");//4.注冊(cè)驅(qū)動(dòng)Class.forName(driver);} catch (IOException | ClassNotFoundException e) {log.error("初始化jdbc連接失敗！", e);}}/*** 獲取連接* @return 連接對(duì)象*/public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url, user, password);}/*** 釋放資源* @param rs* @param st* @param conn*/public static void close(ResultSet rs, Statement st, Connection conn) {if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (st != null) {try {st.close();} catch (SQLException e) {e.printStackTrace();}}if (conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}
}

數(shù)據(jù)庫(kù)操作類

/*** MySQL 鎖操作類（加鎖+釋放鎖）*/
@Slf4j
public class MySQLDistributedLockService {private static Connection connection;private static Statement statement;private static ResultSet resultSet;static{try {connection = JDBCUtils.getConnection();statement = connection.createStatement();resultSet = null;} catch (SQLException e) {log.error("數(shù)據(jù)庫(kù)連接失敗！");}}/*** 鎖表 - 獲取鎖* @param resource      資源* @param description   鎖描述* @return  是否操作成功*/public static boolean tryLock(int resource,String description){String sql = "insert into database_lock (resource,description) values (" + resource + ", '" + description + "');";//獲取數(shù)據(jù)庫(kù)連接try {int stat = statement.executeUpdate(sql);return stat == 1;} catch (SQLException e) {return false;}}/*** 鎖表-釋放鎖* @return*/public static boolean releaseLock(int resource) throws SQLException {String sql = "delete from database_lock where resource = " + resource;//獲取數(shù)據(jù)庫(kù)連接int stat = statement.executeUpdate(sql);return stat == 1;}/*** 關(guān)閉連接*/public static void close(){log.info("當(dāng)前線程： " + ManagementFactory.getRuntimeMXBean().getName().split("@")[0] +",關(guān)閉了數(shù)據(jù)庫(kù)連接！");JDBCUtils.close(resultSet,statement,connection);}
}

LockTable

/*** mysql分布式鎖*      執(zhí)行流程： 多進(jìn)程搶占數(shù)據(jù)庫(kù)某個(gè)資源，然后執(zhí)行業(yè)務(wù)，執(zhí)行完釋放資源*      鎖機(jī)制： 單一進(jìn)程獲取鎖時(shí)，則其他進(jìn)程提交失敗*/
@Slf4j
public class LockTable extends Thread {@Overridepublic void run() {super.run();//獲取Java虛擬機(jī)的進(jìn)程IDString pid = ManagementFactory.getRuntimeMXBean().getName().split("@")[0];try{while(true){log.info("當(dāng)前進(jìn)程PID：" + pid + ",嘗試獲取鎖資源！");if(MySQLDistributedLockService.tryLock(1,"測(cè)試鎖")){log.info("當(dāng)前進(jìn)程PID：" + pid + ",獲取鎖資源成功！");//sleep模擬業(yè)務(wù)處理過程log.info("開始處理業(yè)務(wù)！");Thread.sleep(10*1000);log.info("業(yè)務(wù)處理完成！");MySQLDistributedLockService.releaseLock(1);log.info("當(dāng)前進(jìn)程PID： " + pid + ",釋放了鎖資源！");break;}else{log.info("當(dāng)前進(jìn)程PID： " + pid + "，獲取鎖資源失敗！");Thread.sleep(2000);}}}catch (Exception e){log.error("搶占鎖發(fā)生錯(cuò)誤！",e);}finally {MySQLDistributedLockService.close();}}// 程序入口public static void main(String[] args) {new LockTable().start();}
}

測(cè)試
運(yùn)行時(shí)開啟并行執(zhí)行選項(xiàng)，每次運(yùn)行三個(gè)或三個(gè)以上進(jìn)程. Allow parallel run 運(yùn)行并行執(zhí)行


注意事項(xiàng)：

• 該鎖為非阻塞的
• 當(dāng)某進(jìn)程持有鎖并且掛死時(shí)候會(huì)造成資源一直不釋放的情況，造成死鎖，因此需要維護(hù)一個(gè)定時(shí)清理任務(wù)去清理持有過久的鎖
• 要注意數(shù)據(jù)庫(kù)的單點(diǎn)問題，最好設(shè)置備庫(kù)，進(jìn)一步提高可靠性
• 該鎖為非可重入鎖，如果要設(shè)置成可重入鎖需要添加數(shù)據(jù)庫(kù)字段記錄持有該鎖的設(shè)備信息以及加鎖次數(shù)

方式二：基于樂觀鎖

• 每次執(zhí)行業(yè)務(wù)前首先進(jìn)行數(shù)據(jù)庫(kù)查詢，查詢當(dāng)前的需要修改的資源值（或版本號(hào)）。
• 進(jìn)行資源的修改操作，并且修改前進(jìn)行資源（或版本號(hào)）的比對(duì)操作，比較此時(shí)數(shù)據(jù)庫(kù)中的值是否和上一步查詢結(jié)果相同。
• 查詢結(jié)果相同則修改對(duì)應(yīng)資源值，不同則回到第一步。
  

例子：數(shù)據(jù)庫(kù)中設(shè)定某商品基本信息（名為外科口罩，數(shù)量為10），多進(jìn)程對(duì)該商品進(jìn)行搶購(gòu)，當(dāng)商品數(shù)量為0時(shí)結(jié)束搶購(gòu)。

代碼實(shí)現(xiàn)

    /*** 樂觀鎖-獲取資源* @param id 資源ID* @return result*/public static ResultSet getGoodCount(int id) throws SQLException {String sql = "select  * from  database_lock_2 where id = " + id;//查詢數(shù)據(jù)resultSet = statement.executeQuery(sql);return resultSet;}/*** 樂觀鎖-修改資源* @param id        資源ID* @param goodCount 資源* @return  修改狀態(tài)*/public static boolean setGoodCount(int id, int goodCount) throws SQLException {String sql = "update database_lock_2 set good_count = good_count - 1 where id =" + id +"  and good_count = " + goodCount;int stat = statement.executeUpdate(sql);return stat == 1;}/*** 樂觀鎖-開啟事務(wù)自動(dòng)提交*/public static void AutoCommit(){try {connection.setAutoCommit(true);} catch (SQLException e) {log.error("開啟自動(dòng)提交！",e);}}

OptimisticLock測(cè)試類

/*** mysql分布式鎖-樂觀鎖*  執(zhí)行流程： 多進(jìn)程搶購(gòu)?fù)簧唐?#xff0c;每次搶購(gòu)成功商品數(shù)量-1，商品數(shù)據(jù)量為0時(shí)退出*  鎖機(jī)制： 單一進(jìn)程獲取鎖時(shí)，則其他進(jìn)程提交失敗*/
@Slf4j
public class OptimisticLock extends Thread{@Overridepublic void run() {super.run();String pid = ManagementFactory.getRuntimeMXBean().getName().split("@")[0];ResultSet resultSet = null;String goodName = null;int goodCount = 0;try {while(true){log.info("當(dāng)前線程：" + pid + "，開始搶購(gòu)商品！");//獲取當(dāng)前商品信息resultSet = MySQLDistributedLockService.getGoodCount(1);while (resultSet.next()){goodName = resultSet.getString("good_name");goodCount = resultSet.getInt("good_count");}log.info("獲取庫(kù)存成功，當(dāng)前商品名為：" + goodName + "，當(dāng)前庫(kù)存剩余量為：" + goodCount);//模擬執(zhí)行業(yè)務(wù)操作Thread.sleep(2*3000);if(0 == goodCount){log.info("搶購(gòu)失敗，當(dāng)前庫(kù)存為0！ ");break;}//修改庫(kù)存信息，庫(kù)存量-1if(MySQLDistributedLockService.setGoodCount(1,goodCount)){log.info("當(dāng)前線程：" + pid + " 搶購(gòu)商品：" + goodName + "成功，剩余庫(kù)存為：" + (goodCount -1));//模擬延遲，防止鎖每次被同一進(jìn)程獲取Thread.sleep(2 * 1000);}else{log.error("搶購(gòu)商品：" + goodName +"失敗，商品數(shù)量已被修改");}}}catch (Exception e){log.error("搶購(gòu)商品發(fā)生錯(cuò)誤！",e);}finally {if(resultSet != null){try {resultSet.close();} catch (SQLException e) {e.printStackTrace();log.error("關(guān)閉Result失敗！" , e);}}MySQLDistributedLockService.close();}}public static void main(String[] args) {new OptimisticLock().start();}
}

代碼測(cè)試
開啟三個(gè)進(jìn)程，查看執(zhí)行情況



注意事項(xiàng)：

• 該鎖為非阻塞的
• 該鎖對(duì)于業(yè)務(wù)具有侵入式，如果設(shè)置版本號(hào)校驗(yàn)則增加的額外的字段，增加了數(shù)據(jù)庫(kù)冗余
• 當(dāng)并發(fā)量過高時(shí)會(huì)有大量請(qǐng)求訪問數(shù)據(jù)庫(kù)的某行記錄，對(duì)數(shù)據(jù)庫(kù)造成很大的寫壓力
• 因此樂觀鎖適用于并發(fā)量不高，并且寫操作不頻繁的場(chǎng)景

方式三：悲觀鎖實(shí)現(xiàn)方式（利用事務(wù)加上行/表鎖）

實(shí)現(xiàn)思路

• 關(guān)閉jdbc連接自動(dòng)commit屬性
• 每次執(zhí)行業(yè)務(wù)前先使用查詢語(yǔ)句后接for update表示鎖定該行數(shù)據(jù)（注意查詢條件如果未命中主鍵或索引，此時(shí)將會(huì)從行鎖變?yōu)楸礞i）
• 執(zhí)行業(yè)務(wù)流程修改表資源
• 執(zhí)行commit操作

代碼實(shí)現(xiàn)
MySQLDistributedLockService

/*** 悲觀鎖-獲取資源* @param id 資源ID* @return result*/public static ResultSet getGoodCount2(int id) throws SQLException {String sql = "select  * from  database_lock_2 where id = " + id + "for update";//查詢數(shù)據(jù)resultSet = statement.executeQuery(sql);return resultSet;}/*** 悲觀鎖-修改資源* @param id        資源ID* @return  修改狀態(tài)*/public static boolean setGoodCount2(int id) throws SQLException {String sql = "update database_lock_2 set good_count = good_count - 1 where id =" + id;int stat = statement.executeUpdate(sql);return stat == 1;}/*** 悲觀鎖-關(guān)閉事務(wù)自動(dòng)提交*/public static void closeAutoCommit(){try {connection.setAutoCommit(false);} catch (SQLException e) {log.error("關(guān)閉自動(dòng)提交失敗！",e);}}/*** 悲觀鎖-提交事務(wù)*/public static void commit(String pid,String goodName,int goodCount) throws SQLException {connection.commit();log.info("當(dāng)前線程：" + pid + "搶購(gòu)商品： " + goodName + "成功，剩余庫(kù)存為：" + (goodCount-1));}/*** 悲觀鎖-回滾*/public static void rollBack() throws SQLException {connection.rollback();}

PessimisticLock

/*** mysql 分布式鎖-悲觀鎖*     執(zhí)行流程：多個(gè)進(jìn)程搶占同一個(gè)商品，執(zhí)行業(yè)務(wù)完畢則通過connection.commit() 釋放鎖*     鎖機(jī)制：單一進(jìn)程獲取鎖時(shí)，則其他進(jìn)程將阻塞等待*/
@Slf4j
public class PessimisticLock extends Thread {@Overridepublic void run() {super.run();ResultSet resultSet = null;String goodName = null;int goodCount = 0;String pid = ManagementFactory.getRuntimeMXBean().getName().split("@")[0];//關(guān)閉自動(dòng)提交MySQLDistributedLockService.closeAutoCommit();try{while(true){log.info("當(dāng)前線程：" + pid + "");//獲取庫(kù)存resultSet = MySQLDistributedLockService.getGoodCount2(1);while (resultSet.next()) {goodName = resultSet.getString("good_name");goodCount = resultSet.getInt("good_count");}log.info("獲取庫(kù)存成功，當(dāng)前商品名稱為:" + goodName + ",當(dāng)前庫(kù)存剩余量為:" + goodCount);// 模擬執(zhí)行業(yè)務(wù)事件Thread.sleep(2 * 1000);if (0 == goodCount) {log.info("搶購(gòu)失敗，當(dāng)前庫(kù)存為0！");break;}// 搶購(gòu)商品if (MySQLDistributedLockService.setGoodCount2(1)) {// 模擬延時(shí)，防止鎖每次被同一進(jìn)程獲取MySQLDistributedLockService.commit(pid, goodName, goodCount);Thread.sleep(2 * 1000);} else {log.error("搶購(gòu)商品:" + goodName + "失敗!");}}}catch (Exception e){//搶購(gòu)失敗log.error("搶購(gòu)商品發(fā)生錯(cuò)誤！",e);try {MySQLDistributedLockService.rollBack();} catch (SQLException ex) {log.error("回滾失敗！ ",e);}}finally {if(resultSet != null){try {resultSet.close();} catch (SQLException e) {log.error("Result關(guān)閉失敗！",e);}}MySQLDistributedLockService.close();}}public static void main(String[] args) {new PessimisticLock().start();}
}

測(cè)試結(jié)果



注意事項(xiàng)：

• 該鎖為阻塞鎖
• 每次請(qǐng)求存在額外加鎖的開銷
• 在并發(fā)量很高的情況下會(huì)造成系統(tǒng)中存在大量阻塞的請(qǐng)求，影響系統(tǒng)的可用性
• 因此悲觀鎖適用于并發(fā)量不高，讀操作不頻繁的寫場(chǎng)景

總結(jié)：

• 在實(shí)際使用中，由于受到性能以及穩(wěn)定性約束，對(duì)于關(guān)系型數(shù)據(jù)庫(kù)實(shí)現(xiàn)的分布式鎖一般很少被用到。但是對(duì)于一些并發(fā)量不高、系統(tǒng)僅提供給內(nèi)部人員使用的單一業(yè)務(wù)場(chǎng)景可以考慮使用關(guān)系型數(shù)據(jù)庫(kù)分布式鎖，因?yàn)槠鋸?fù)雜度較低，可靠性也能夠得到保證。