分布式锁Mysql实现方式 方式1:唯一索引 创建锁表,内部存在字段表示资源名及资源描述,同一资源名使用数据库唯一性限制。多个进程同时往数据库锁表中写入对某个资源的占有记录,当某个进程成功写入时则表示
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='数据库分布式锁表';
db.properties
driver=com.mysql.cj.jdbc.Driverurl=jdbc:mysql://localhost:3306/distribute_lock?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=Asia/Shanghaiuser=rootpassWord=123456
@Slf4jpublic 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 { // 创建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关闭失败!", e); } } }}
@Slf4jpublic 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.注册驱动 Class.forName(driver); } catch (IOException | ClassNotFoundException e) { log.error("初始化jdbc连接失败!", e); } } public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, user, password); } 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(); } } }}
数据库操作类
@Slf4jpublic 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("数据库连接失败!"); } } public static boolean tryLock(int resource,String description){ String sql = "insert into database_lock (resource,description) values (" + resource + ", '" + description + "');"; //获取数据库连接 try { int stat = statement.executeUpdate(sql); return stat == 1; } catch (SQLException e) { return false; } } public static boolean releaseLock(int resource) throws SQLException { String sql = "delete from database_lock where resource = " + resource; //获取数据库连接 int stat = statement.executeUpdate(sql); return stat == 1; } public static void close(){ log.info("当前线程: " + ManagementFactory.getRuntimeMXBean().getName().split("@")[0] + ",关闭了数据库连接!"); JDBCUtils.close(resultSet,statement,connection); }}
LockTable
@Slf4jpublic class LockTable extends Thread { @Override public void run() { super.run(); //获取Java虚拟机的进程ID String pid = ManagementFactory.getRuntimeMXBean().getName().split("@")[0]; try{ while(true){ log.info("当前进程PID:" + pid + ",尝试获取锁资源!"); if(MySQLDistributedLockService.tryLock(1,"测试锁")){ log.info("当前进程PID:" + pid + ",获取锁资源成功!"); //sleep模拟业务处理过程 log.info("开始处理业务!"); Thread.sleep(10*1000); log.info("业务处理完成!"); MySQLDistributedLockService.releaseLock(1); log.info("当前进程PID: " + pid + ",释放了锁资源!"); break; }else{ log.info("当前进程PID: " + pid + ",获取锁资源失败!"); Thread.sleep(2000); } } }catch (Exception e){ log.error("抢占锁发生错误!",e); }finally { MySQLDistributedLockService.close(); } } // 程序入口 public static void main(String[] args) { new LockTable().start(); }}
测试
运行时开启并行执行选项,每次运行三个或三个以上进程. Allow parallel run 运行并行执行
注意事项:
方式二:基于乐观锁
例子:数据库中设定某商品基本信息(名为外科口罩,数量为10),多进程对该商品进行抢购,当商品数量为0时结束抢购。
代码实现
public static ResultSet getGoodCount(int id) throws SQLException { String sql = "select * from database_lock_2 where id = " + id; //查询数据 resultSet = statement.executeQuery(sql); return resultSet; } 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; } public static void AutoCommit(){ try { connection.setAutoCommit(true); } catch (SQLException e) { log.error("开启自动提交!",e); } }
OptimisticLock测试类
@Slf4jpublic class OptimisticLock extends Thread{ @Override public 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("当前线程:" + pid + ",开始抢购商品!"); //获取当前商品信息 resultSet = MySQLDistributedLockService.getGoodCount(1); while (resultSet.next()){ goodName = resultSet.getString("good_name"); goodCount = resultSet.getInt("good_count"); } log.info("获取库存成功,当前商品名为:" + goodName + ",当前库存剩余量为:" + goodCount); //模拟执行业务操作 Thread.sleep(2*3000); if(0 == goodCount){ log.info("抢购失败,当前库存为0! "); break; } //修改库存信息,库存量-1 if(MySQLDistributedLockService.setGoodCount(1,goodCount)){ log.info("当前线程:" + pid + " 抢购商品:" + goodName + "成功,剩余库存为:" + (goodCount -1)); //模拟延迟,防止锁每次被同一进程获取 Thread.sleep(2 * 1000); }else{ log.error("抢购商品:" + goodName +"失败,商品数量已被修改"); } } }catch (Exception e){ log.error("抢购商品发生错误!",e); }finally { if(resultSet != null){ try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); log.error("关闭Result失败!" , e); } } MySQLDistributedLockService.close(); } } public static void main(String[] args) { new OptimisticLock().start(); }}
代码测试
开启三个进程,查看执行情况
注意事项:
方式三:悲观锁实现方式(利用事务加上行/表锁)
实现思路
代码实现
MySQLDistributedLockService
public static ResultSet getGoodCount2(int id) throws SQLException { String sql = "select * from database_lock_2 where id = " + id + "for update"; //查询数据 resultSet = statement.executeQuery(sql); return resultSet; } 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; } public static void closeAutoCommit(){ try { connection.setAutoCommit(false); } catch (SQLException e) { log.error("关闭自动提交失败!",e); } } public static void commit(String pid,String goodName,int goodCount) throws SQLException { connection.commit(); log.info("当前线程:" + pid + "抢购商品: " + goodName + "成功,剩余库存为:" + (goodCount-1)); } public static void rollBack() throws SQLException { connection.rollback(); }
PessimisticLock
@Slf4jpublic class PessimisticLock extends Thread { @Override public void run() { super.run(); ResultSet resultSet = null; String goodName = null; int goodCount = 0; String pid = ManagementFactory.getRuntimeMXBean().getName().split("@")[0]; //关闭自动提交 MySQLDistributedLockService.closeAutoCommit(); try{ while(true){ log.info("当前线程:" + pid + ""); //获取库存 resultSet = MySQLDistributedLockService.getGoodCount2(1); while (resultSet.next()) { goodName = resultSet.getString("good_name"); goodCount = resultSet.getInt("good_count"); } log.info("获取库存成功,当前商品名称为:" + goodName + ",当前库存剩余量为:" + goodCount); // 模拟执行业务事件 Thread.sleep(2 * 1000); if (0 == goodCount) { log.info("抢购失败,当前库存为0!"); break; } // 抢购商品 if (MySQLDistributedLockService.setGoodCount2(1)) { // 模拟延时,防止锁每次被同一进程获取 MySQLDistributedLockService.commit(pid, goodName, goodCount); Thread.sleep(2 * 1000); } else { log.error("抢购商品:" + goodName + "失败!"); } } }catch (Exception e){ //抢购失败 log.error("抢购商品发生错误!",e); try { MySQLDistributedLockService.rollBack(); } catch (SQLException ex) { log.error("回滚失败! ",e); } }finally { if(resultSet != null){ try { resultSet.close(); } catch (SQLException e) { log.error("Result关闭失败!",e); } } MySQLDistributedLockService.close(); } } public static void main(String[] args) { new PessimisticLock().start(); }}
测试结果
注意事项:
总结:
来源地址:https://blog.csdn.net/Forbidden_City/article/details/132052106
--结束END--
本文标题: MySQL做分布式锁
本文链接: https://www.lsjlt.com/news/408564.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-21
2024-05-21
2024-05-21
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
一口价域名售卖能注册吗?域名是网站的标识,简短且易于记忆,为在线用户提供了访问我们网站的简单路径。一口价是在域名交易中一种常见的模式,而这种通常是针对已经被注册的域名转售给其他人的一种方式。
一口价域名买卖的过程通常包括以下几个步骤:
1.寻找:买家需要在域名售卖平台上找到心仪的一口价域名。平台通常会为每个可售的域名提供详细的描述,包括价格、年龄、流
443px" 443px) https://www.west.cn/docs/wp-content/uploads/2024/04/SEO图片294.jpg https://www.west.cn/docs/wp-content/uploads/2024/04/SEO图片294-768x413.jpg 域名售卖 域名一口价售卖 游戏音频 赋值/切片 框架优势 评估指南 项目规模
0