Implementing a Distributed Lock with MySQL

  • 2021-12-21 05:27:53
  • OfStack


In distributed system, distributed lock is one of the most basic tool classes. For example, in two micro-services with payment function deployed, users may initiate two payment operations for one order, and these two requests may be sent to two services, so distributed locks must be used to prevent repeated submission, services that acquire locks normally perform payment operations, and services that fail to acquire locks prompt repeated operations.

Our company encapsulates a large number of basic tool classes. When we want to use distributed locks, we only need to do three things

1. Build globallocktable table in database
2. Introduce the corresponding jar package
3. You can use this component by writing @ Autowired GlobalLockComponent globalLockComponent in your code

After reading this article, you can also use springboot-starter to achieve the same function. But we didn't achieve it in this way. Another article analyzes how we achieved it.

This article first analyzes the implementation of MySQL distributed under 1

Table building

CREATE TABLE `globallocktable` (
 `lockKey` varchar(60) NOT NULL COMMENT ' Lock name ',
 `createTime` datetime NOT NULL COMMENT ' Creation time ',
 PRIMARY KEY (`id`),
 UNIQUE KEY `lockKey` (`lockKey`)

Components for others to use

public class GlobalLockComponent {
 GlobalLockTableDAO globalLockDAO;
  *  Attempt to acquire a lock, success is true The failure is false
 public boolean tryLock(String key) {
  return GlobalLockUtil.tryLock(this.globalLockDAO, key);
  *  If another program already occupies the lock and exceeds the timeoutMs (milliseconds), the lock occupation is forcibly cleared 
  *  That is, according to key Delete records before adding records 
 public boolean tryLockWithClear(String key, Long timeoutMs) {
  return GlobalLockUtil.tryLockWithClear(this.globalLockDAO, key, timeoutMs);
  *  Release the lock, according to key Delete a record 
 public void releasLock(String key) {
  GlobalLockUtil.releasLock(this.globalLockDAO, key);

The lock object is defined as follows

public class GlobalLockTable {

 private Integer id;
 private String lockKey;
 private Date createTime;
 //  Omission get And set Method 

GlobalLockTableDAO The definition is as follows 

public interface GlobalLockTableDAO {
 int deleteByPrimaryKey(Integer id);
 int deleteByLockKey(String lockKey);
 GlobalLockTable selectByLockKey(String key);
 int insertSelectiveWithTest(GlobalLockTable record);

Specific locking and unlocking logic

public class GlobalLockUtil {
 private static Logger logger = LoggerFactory.getLogger(GlobalLockUtil.class);
 private static GlobalLockTable tryLockInternal(GlobalLockTableDAO lockDAO, String key) {
  GlobalLockTable insert = new GlobalLockTable();
  insert.setCreateTime(new Date());
  //  A place to pay attention to 1
  int count = lockDAO.insertSelectiveWithTest(insert);
  if (count == 0) {
   GlobalLockTable ready = lockDAO.selectByLockKey(key);
   logger.warn("can not lock the key: {}, {}, {}", insert.getLockKey(), ready.getCreateTime(),
   return ready;
  }"yes got the lock by key: {}", insert.getId(), insert.getLockKey());
  return null;
 /**  Timeout to clear lock occupation and re-lock  **/
 public static boolean tryLockWithClear(GlobalLockTableDAO lockDAO, String key, Long timeoutMs) {
  GlobalLockTable lock = tryLockInternal(lockDAO, key);
  if (lock == null) return true;
  if (System.currentTimeMillis() - lock.getCreateTime().getTime() <= timeoutMs) {
   logger.warn("sorry, can not get the key. : {}, {}, {}", key, lock.getId(), lock.getCreateTime());
   return false;
  logger.warn("the key already timeout wthin : {}, {}, will clear", key, timeoutMs);
  //  A place to pay attention to 2
  int count = lockDAO.deleteByPrimaryKey(lock.getId());
  if (count == 0) {
   logger.warn("sorry, the key already preemptived by others: {}, {}", lock.getId(), lock.getLockKey());
   return false;
  lock = tryLockInternal(lockDAO, key);
  return lock != null ? false : true;
 /**  Lock  **/
 public static boolean tryLock(GlobalLockTableDAO lockDAO, String key) {
  return tryLockInternal(lockDAO, key) == null ? true : false;
 /**  Unlock  **/
 public static void releasLock(GlobalLockTableDAO lockDAO, String key) {

There are two interesting things about this tool class. Look at Note 2 first (identified in the above code)

1. In order to avoid the lock not released for a long time, if it is implemented with Redis, the lock timeout time can be set, and the timeout will be automatically released (it will be written later to implement distributed locks with Redis) if it is implemented with MySQL, it can be deleted first and then added. You can see that when you delete it, you delete it with id, not name. Why? Think about it for yourself first

Because if it is deleted through name, it is possible that someone else deleted the lock and added the lock through name. Before the timeout, you deleted it according to name. If you delete through id, when id=0 is returned, it means that others have re-locked and you need to re-obtain it.

2. Other methods of dao layer of GlobalLockTable object are known by name. Let's look at one of these methods. That is, note 1 in the code
You can see that every time you try to lock, select is not first, but insertSelectiveWithTest directly, which reduces one query time and improves efficiency

The function of insertSelectiveWithTest is to return 0 without inserting lockKey when it exists. Insert when lockKey does not exist and return 1

<insert id="insertSelectiveWithTest" useGeneratedKeys="true" keyProperty="id" parameterType="com.javashitang.middleware.lock.mysql.pojo.GlobalLockTable">
 insert into `globallocktable` (`id`,
 `lockKey`, `createTime` )
  select #{id,jdbcType=INTEGER}, #{lockKey,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}
  from dual where not exists
  (select 1 from globallocktable where lockKey = #{lockKey,jdbcType=VARCHAR})


When we want to use it, we just write business logic, which is very convenient

if (!globalLockComponent.tryLock(name)) {
 //  No lock was acquired and returned 
try {
 //  Write business logic here 
} catch (Exception e) {
} finally {


The above is this site to introduce the use of MySQL to achieve a distributed lock, I hope to help you!

Related articles: