前言
前几天写过使用zookeeper、redis和数据库悲观锁实现分布式锁，好吧，今天想再研究一下使用数据库表示实现。
原理
先在数据库建一张资源表，包含资源名称resource_name、版本号version（可以是随机数、时间戳，与锁是否过期有关）等信息，将资源名称建立唯一索引
在程序尝试上锁时，根据资源名称查询数据，获取锁的版本号也就是时间戳
将当前时间与版本号作比较，如果当前时间大于等于版本号的时间，就以资源名称和查出来的版本号作为条件，更新数据库版本号为自己的版本号
自己的版本号怎么产生的呢？就是当前时间戳加上超时时间，这里我设置的超时时间为2000毫秒
如果更新返回的影响行数大于0就是更新成功，该线程获取了锁；如果更新返回的影响行数不大于0就是更新失败，该线程无法获取锁，然后睡眠一定时间再重试
我们可以设置重试时间，如果超过时间就不再获取锁了
如果版本号已经被更新了的话，那么其他线程以查出来的旧版本号就无法匹配得到数据行了，在这个过程中只有一个线程能够更新
释放锁的过程就是将版本号改为当前时间戳
建立数据表
CREATE TABLE `resource` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '主键',
`resource_name` varchar(64) NOT NULL DEFAULT '' COMMENT '资源名',
`share` varchar(64) NOT NULL DEFAULT '' COMMENT '状态',
`version` int(4) NOT NULL DEFAULT '' COMMENT '版本号',
`desc` varchar(1024) NOT NULL DEFAULT '备注信息',
`update_time` timestamp NOT NULL DEFAULT '' COMMENT '保存数据时间，自动生成',
PRIMARY KEY (`id`),
UNIQUE KEY `uidx_resource_name` (`resource_name `) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='资源';
创建数据库工具类
package com.tp.database;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.slf4j.LoggerFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
public class Database {
private static DataSource dataSource = null;
static {
Map properties = new HashMap<String, String>();
properties.put(DruidDataSourceFactory.PROP_DRIVERCLASSNAME, "com.mysql.jdbc.Driver");
properties.put(DruidDataSourceFactory.PROP_URL, "jdbc:mysql://localhost:3306/extmail?useUnicode=true&characterEncoding=utf8");
properties.put(DruidDataSourceFactory.PROP_USERNAME, "root");
properties.put(DruidDataSourceFactory.PROP_PASSWORD, "root");
properties.put(DruidDataSourceFactory.PROP_MAXACTIVE, "100");
properties.put(DruidDataSourceFactory.PROP_INITIALSIZE, "1");
properties.put(DruidDataSourceFactory.PROP_MAXWAIT, "60000");
properties.put(DruidDataSourceFactory.PROP_TIMEBETWEENEVICTIONRUNSMILLIS, "60000");
properties.put(DruidDataSourceFactory.PROP_MINEVICTABLEIDLETIMEMILLIS, "300000");
properties.put(DruidDataSourceFactory.PROP_VALIDATIONQUERY, "select 1 from dual");
properties.put(DruidDataSourceFactory.PROP_TESTWHILEIDLE, "true");
properties.put(DruidDataSourceFactory.PROP_TESTONBORROW, "false");
properties.put(DruidDataSourceFactory.PROP_TESTONRETURN, "false");
properties.put(DruidDataSourceFactory.PROP_MAXOPENPREPAREDSTATEMENTS, "20");
try {
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 插入或更新数据库
* @param sql
* @return
*/
public static Integer insertOrUpdate(String sql){
Integer flag = 0;
Connection connection = null;
PreparedStatement ps = null;
try{
connection = dataSource.getConnection();
ps = connection.prepareStatement(sql);
flag = ps.executeUpdate();
}catch (Exception e){
//e.printStackTrace();
}finally {
if (connection!=null){
try{
connection.close();
}catch (Exception e){
connection = null;
}
}
if (ps!=null){
try{
ps.close();
}catch (Exception e){
ps = null;
}
}
}
return flag;
}
/**
* 插入或更新数据库
* @param resouceName
* @return
*/
public static Map<String,Object> selectByResourceName(String resouceName){
Map result = null;
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "select id,resource_name,share,version,`desc`,update_time from resource where resource_name = ?";
try{
connection = dataSource.getConnection();
ps = connection.prepareStatement(sql);
ps.setString(1,resouceName);
rs = ps.executeQuery();
if (rs.next()){
result = new HashMap();
result.put("id",rs.getInt(1));
result.put("resource_name",rs.getString(2));
result.put("share",rs.getString(3));
result.put("version",rs.getLong(4));
result.put("desc",rs.getString(5));
result.put("update_time",rs.getTimestamp(6));
}
}catch (Exception e){
e.printStackTrace();
}finally {
if (connection!=null){
try{
connection.close();
}catch (Exception e){
connection = null;
}
}
if (ps!=null){
try{
ps.close();
}catch (Exception e){
ps = null;
}
}
if (rs!=null){
try{
rs.close();
}catch (Exception e){
rs = null;
}
}
}
return result;
}
}
创建锁工具类
在这里我是没有插入资源数据，一张空表，如果查询资源为空的话就会插入一条资源，因为刚开始有很多线程插表，由于资源名称建立了唯一索引，就会报错，但是不影响使用，符合逻辑
package com.tp.database;
import com.sun.org.apache.xpath.internal.operations.Bool;
import org.omg.CORBA.TIMEOUT;
import javax.sql.DataSource;
import javax.swing.text.StyledEditorKit;
import java.util.Map;
import java.util.Random;
import java.util.UUID;
import java.util.concurrent.TimeUnit;
public class MyLock {
private static final String resourceName = "myresource";
private static final Integer TIMEOUT = 10 * 1000;//超时10s
private static final Integer STEPTIME = 100;//每100毫秒尝试获取一次锁
private static final Integer RELEASETIME = 2*1000;//释放锁的时间
private Long version = null;
private String owner = null;
/**
* 获取锁
*/
public void tryLock(){
Integer timeOut = TIMEOUT;
while (timeOut > 0){
Long now = System.currentTimeMillis();
version = now + RELEASETIME;
Map<String,Object> result = Database.selectByResourceName(resourceName);
if (result==null){
//没有被获取锁，直接尝试获取锁
String sql = "insert into resource(resource_name,share,version,`desc`,update_time) values('"+resourceName+"','1',"+version+",'获取了锁',now())";
Integer flag = Database.insertOrUpdate(sql);
if (flag > 0){
System.out.println("获取锁成功...，version = " + version);
return;
}
} else {
//如果有线程获取锁，判断该锁是否超时
Long oldVersion = (Long) result.get("version");
if (now>=oldVersion){
//锁超时了，直接获得锁
String sql = "update resource set version = " + version + " where resource_name ='" + resourceName + "' and version = " + oldVersion;
Integer flag = Database.insertOrUpdate(sql);
if (flag>0){
System.out.println("获取锁（超时）成功，version = " + version);
return;
}
}
}
try{
Thread.sleep(STEPTIME);
}catch (Exception e){
e.printStackTrace();
}
timeOut = timeOut - STEPTIME;
}
}
/**
* 释放锁
*/
public void unLock(){
String sql = "update resource set version = "+System.currentTimeMillis()+" where resource_name = '" + resourceName +"' and version = " + version;
Integer flag = Database.insertOrUpdate(sql);
if (flag > 0){
System.out.println("释放锁成功，version = " + version);
}else{
System.out.println("释放锁失败，version = " + version);
}
}
}
测试类
package com.tp.database;
public class LockTest {
public static void main(String[] args) {
for (int i=0;i<100;i++){
new Thread(){
@Override
public void run() {
MyLock myLock = new MyLock();
myLock.tryLock();
System.out.println("=======乐观锁处理业务=======");
myLock.unLock();
}
}.start();
}
}
}
结果
获取锁（超时）成功，version = 1587728751610
=======乐观锁处理业务=======
释放锁成功，version = 1587728751610
获取锁（超时）成功，version = 1587728755892
=======乐观锁处理业务=======
释放锁成功，version = 1587728755892
获取锁（超时）成功，version = 1587728758461
=======乐观锁处理业务=======
释放锁成功，version = 1587728758461
获取锁（超时）成功，version = 1587728760054
=======乐观锁处理业务=======
释放锁成功，version = 1587728760054
获取锁（超时）成功，version = 1587728761553
=======乐观锁处理业务=======
释放锁成功，version = 1587728761553
获取锁（超时）成功，version = 1587728762604
=======乐观锁处理业务=======
释放锁成功，version = 1587728762604
总结
使用数据表实现与之前用redis实现差不多，都是锁定资源和版本号，通过版本号来判断是否超时。
2172
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
