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来标识版本，就效率而言，乐观所大与悲观所。
评论