Java使用悲观锁和乐观锁解决数据库更新操作行锁的问题案例分享
应用场景:有一个账户,当发生交易时,需要实时更新账户余额,这样子就需要更新数据库数据,在这个过程中就需要涉及到并发所带来的问题而导致账户余额混乱,这种情况对于公司和客户来说都是很危险的一件事。
解决这类型问题,有2种方式:
1、 悲观锁:Pessimistic Lock,来解决该问题。
2、乐观锁:Optimistic Lock,来解决该问题。
假设有1个账户,同时进行收入、支出的操作,需要保证账户金额不能出错,即,收入了账户增加余额,支出了账户减少余额,如果操作出错,则需要用户第二次重新操作。
1、悲观锁解决方案
环境:MySQL+JDBC
数据库表:
create table t_account( userid varchar(50), money decimal(12,4) ); insert into t_account(userid,money) value('what21',123456.78);
公用类:
package com.what21.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JdbcUtil { /** * 创建数据库连接 * * @return */ public static Connection createConnection(){ Connection connection = null; try { Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test","root","123124"); } catch (Exception e) { e.printStackTrace(); } return connection; } /** * 释放连接 * @param conn */ private static void close(Connection conn) { if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } conn=null; } /** * 释放statement * @param statement */ private static void close(Statement statement) { if(statement!=null){ try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } statement=null; } /** * 释放resultset * @param rs */ private static void close(ResultSet rs) { if(rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } rs = null; } /** * 释放资源 * * @param conn * @param statement * @param rs */ public static void close(Connection conn, Statement statement, ResultSet rs) { if (rs != null) { close(rs); } if (statement != null) { close(statement); } if (conn != null) { close(conn); } } }
DAO类:
package com.what21.dao; public interface IAccountDao { /** * 查询账户金额 */ public float find(String userid); /** * 修改账户金额 */ public void modify(String userid,float moery); }
package com.what21.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class AccountDaoImpl implements IAccountDao { @Override public float find(String userid) { float moery = 0; Connection connection = JdbcUtil.createConnection(); PreparedStatement pstat = null; ResultSet rs = null; try { String sql = "select money from t_account where userid=?"; pstat = connection.prepareStatement(sql); pstat.setString(1, userid); rs = pstat.executeQuery(); if(rs.next()){ moery =rs.getFloat(1); } } catch (SQLException e) { e.printStackTrace(); }finally{ JdbcUtil.close(connection, pstat, null); } return moery; } @Override public void modify(String userid, float moery) { Connection connection = JdbcUtil.createConnection(); PreparedStatement pstat = null; try { String sql = "update t_account set money=? where userid=?"; // 2. 创建PreparedStatement pstat = connection.prepareStatement(sql); pstat.setFloat(1, moery); pstat.setString(2, userid); pstat.execute(); } catch (SQLException e) { e.printStackTrace(); }finally{ JdbcUtil.close(connection, pstat, null); } } }
Service类:
package com.what21.service; public interface IAccountService { /** * 收入(增加账户余额) * * @param userid * @param moery */ public float income(String userid,float moery); /** * 支出(减少账户余额) * * @param userid * @param moery */ public float expenses(String userid,float moery); }
package com.what21.service; import com.what21.dao.AccountDaoImpl; import com.what21.dao.IAccountDao; public class AccountServiceImpl implements IAccountService { private IAccountDao accountDao = new AccountDaoImpl(); @Override public float income(String userid, float moery) { return this.changeMoery(userid, moery, 1); } @Override public float expenses(String userid, float moery) { return this.changeMoery(userid, moery, 2); } /** * 使用悲观锁来解决 * * @param userid 用户ID * @param moery 钱 * @param type 类型:1、收入(增加),2、支出(减少) */ private float changeMoery(String userid, float moery,int type){ // 查找账户金额 float totalAmount = accountDao.find(userid); if(type!=1){ // 支出,账户金额一定要够 if(totalAmount<moery){ throw new RuntimeException("账户金额不够"); } } synchronized(this){ // 查找账户金额 float totalAmount2 = accountDao.find(userid); // 支出,账户金额一定要够 if(totalAmount<moery){ throw new RuntimeException("账户金额不够"); } if(totalAmount==totalAmount2){ if(type==1){ totalAmount = totalAmount + moery; }else{ totalAmount = totalAmount - moery; } accountDao.modify(userid, totalAmount); }else{ throw new RuntimeException("账户已经变更,请重新操作"); } } return totalAmount; } }
场景模拟实现:
package com.what21.main; import com.what21.service.IAccountService; public class AccountThread extends Thread { private IAccountService accountService; private String userid; private float moery; public AccountThread(IAccountService accountService,String userid,float moery){ this.accountService = accountService; this.userid = userid; this.moery = moery; } @Override public void run() { float totalAmount = 0; try{ if(moery>0){ totalAmount = accountService.income(userid, moery); log("账户:" + userid + ",收入:" + moery + ",操作成功"); }else{ totalAmount = accountService.expenses(userid, Math.abs(moery)); log("账户:" + userid + ",支出:" + Math.abs(moery) + ",操作成功"); } log("账户:" + userid + ",总金额为:" + totalAmount); }catch(RuntimeException e){ if(moery>0){ log("账户:" + userid + ",收入:" + moery + ",操作失败,原因:" + e.getMessage()); }else{ log("账户:" + userid + ",支出:" + Math.abs(moery) + ",操作失败,原因:" + e.getMessage()); } } } static void log(Object obj){ System.out.println(obj); } }
package com.what21.main; import com.what21.service.AccountServiceImpl; import com.what21.service.IAccountService; public class Main { public static void main(String[] args) { IAccountService accountService = new AccountServiceImpl(); String userid = "what21"; // 收入100 new AccountThread(accountService,userid,100).start(); // 支出200 new AccountThread(accountService,userid,-200).start(); // 收入100 new AccountThread(accountService,userid,100).start(); } }
2、乐观锁解决方案
环境:MySQL+JDBC
数据库表:
create table t_account2( userid varchar(50), money decimal(12,4), version int(3) default 0 ); insert into t_account2(userid,money) value('what21',123456.78);
DAO类:
package com.what21.dao; import java.util.Map; public interface IAccountDao { /** * 查询账户金额 */ public Map<String,Object> find(String userid); /** * 修改账户金额 */ public int modify(String userid,Map<String,Object> paramMap); }
package com.what21.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashMap; import java.util.Map; public class AccountDaoImpl implements IAccountDao { @Override public Map<String,Object> find(String userid) { Map<String,Object> resultMap = new HashMap<String,Object>(); Connection connection = JdbcUtil.createConnection(); PreparedStatement pstat = null; ResultSet rs = null; try { String sql = "select money,version from t_account2 where userid=?"; pstat = connection.prepareStatement(sql); pstat.setString(1, userid); rs = pstat.executeQuery(); if(rs.next()){ resultMap.put("money", rs.getFloat(1)); resultMap.put("version", rs.getInt(2)); } } catch (SQLException e) { e.printStackTrace(); }finally{ JdbcUtil.close(connection, pstat, null); } return resultMap; } @Override public int modify(String userid,Map<String,Object> paramMap) { int result = 0; Connection connection = JdbcUtil.createConnection(); PreparedStatement pstat = null; try { float money = (Float)paramMap.get("money"); int version = (Integer)paramMap.get("version"); String sql = "update t_account2 set money=?,version=? where userid=? and version=?"; pstat = connection.prepareStatement(sql); pstat.setFloat(1, money); pstat.setInt(2, version+1); pstat.setString(3, userid); pstat.setInt(4, version); result = pstat.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ JdbcUtil.close(connection, pstat, null); } return result; } }
Service类:
package com.what21.service; public interface IAccountService { /** * 收入(增加账户余额) * * @param userid * @param moery */ public float income(String userid,float moery); /** * 支出(减少账户余额) * * @param userid * @param moery */ public float expenses(String userid,float moery); }
package com.what21.service; import java.util.HashMap; import java.util.Map; import com.what21.dao.AccountDaoImpl; import com.what21.dao.IAccountDao; public class AccountServiceImpl implements IAccountService { private IAccountDao accountDao = new AccountDaoImpl(); @Override public float income(String userid, float moery) { return this.changeMoery(userid, moery, 1); } @Override public float expenses(String userid, float moery) { return this.changeMoery(userid, moery, 2); } /** * 使用乐观锁来解决 * * @param userid 用户ID * @param moery 钱 * @param type 类型:1、收入(增加),2、支出(减少) */ private float changeMoery(String userid, float moery,int type){ // 查找账户金额 Map<String,Object> accountMap = accountDao.find(userid); float totalAmount = (Float)accountMap.get("money"); int version = (Integer)accountMap.get("version"); if(type!=1){ // 支出,账户金额一定要够 if(totalAmount<moery){ throw new RuntimeException("账户金额不够"); } } if(type==1){ totalAmount = totalAmount + moery; }else{ totalAmount = totalAmount - moery; } accountMap = new HashMap<String,Object>(); accountMap.put("money", totalAmount); accountMap.put("version", version); int result = accountDao.modify(userid, accountMap); if(result<=0){ throw new RuntimeException("账户已经变更,请重新操作"); } return totalAmount; } }
场景模拟调用:
package com.what21.main; import com.what21.service.IAccountService; public class AccountThread extends Thread { private IAccountService accountService; private String userid; private float moery; public AccountThread(IAccountService accountService,String userid,float moery){ this.accountService = accountService; this.userid = userid; this.moery = moery; } @Override public void run() { float totalAmount = 0; try{ if(moery>0){ totalAmount = accountService.income(userid, moery); log("账户:" + userid + ",收入:" + moery + ",操作成功"); }else{ totalAmount = accountService.expenses(userid, Math.abs(moery)); log("账户:" + userid + ",支出:" + Math.abs(moery) + ",操作成功"); } log("账户:" + userid + ",总金额为:" + totalAmount); }catch(RuntimeException e){ if(moery>0){ log("账户:" + userid + ",收入:" + moery + ",操作失败,原因:" + e.getMessage()); }else{ log("账户:" + userid + ",支出:" + Math.abs(moery) + ",操作失败,原因:" + e.getMessage()); } } } static void log(Object obj){ System.out.println(obj); } }
package com.what21.main; import com.what21.service.AccountServiceImpl; import com.what21.service.IAccountService; public class Main { public static void main(String[] args) { IAccountService accountService = new AccountServiceImpl(); String userid = "what21"; // 收入100 new AccountThread(accountService,userid,100).start(); // 支出200 new AccountThread(accountService,userid,-200).start(); // 收入100 new AccountThread(accountService,userid,100).start(); } }
总结:悲观锁与乐观锁都可以解决该问题,乐观锁需要加字段version来标识版本,就效率而言,乐观所大与悲观所。
评论