用MySQL数据库实现分布式锁

对于分布式锁的实现，现在绝大多数都是用redis来实现的，就是大名鼎鼎的setnx命令。这次我们用MySQL数据库来实现一下。

思路有很多种，这里我介绍一个非常简单的实现。建立一张表，比如lock_table,这个表中有个字段叫 lock_name,并且向lock_name 字段建立

唯一索引。每次获取锁的时候向这张表插入一条记录,lock_name字段值任意。如果插入成功，那么说明加锁成功了，因为lock_name建立了

唯一索引，插入失败说明有人在你之前插入成功了，也就意味着别人获取到了锁。那么在业务执行完毕之后，删除这条记录，就是解锁操作。

直接上代码：

SQL语句

DROP TABLE IF EXISTS `lock_table`;
CREATE TABLE `lock_table`  (
  `lock_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '分布式锁字段',
  UNIQUE INDEX `lock_name_index`(`lock_name`) USING BTREE COMMENT '分布式锁字段'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

package cn.hongwei.lock;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Description:
 * 基于MySQL唯一索引来实现分布式锁
 *
 * @author shihongwei
 * @email 1224028994@qq.com
 */
public class MysqlUniqueIndexLock {

    private DataSource dataSource;

    public MysqlUniqueIndexLock(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public boolean lock() {
        // 1. 插入数据
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement("insert into lock_table values ('myLock')");
            statement.execute();
            connection.commit();
            return true;
        } catch (SQLException e) {
            // 回滚
            try {
                connection.rollback();
            } catch (SQLException ex) {
                return false;
            }
            // 说明锁被占用,开启重试机制，如果时间太短，会导致连接不够用，所以连接池的最大线程要设置的大一点
            //避免出现递归导致连接被用完或者导致栈内存溢出
            try {
                Thread.sleep(100);
            } catch (InterruptedException ex) {
                throw new RuntimeException(ex);
            }
            return lock();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    public void unlock() {
        // 1. 删除数据
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement("delete from lock_table where lock_name = 'myLock'");
            statement.execute();
            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}