Resolving Duplicate OpenID Insertions in Fast App Center: Analysis and Distributed Lock Solutions

Many user‑facing internet services keep a copy of user data on the backend; the Fast App Center does the same. It records users' favorite fast apps and stores the association between a user's OpenID and the client‑side identifier local_identifier so that the Menubar state can be synchronized.

When the Fast App Center starts, the client sends OpenID and local_identifier to the server. The server checks whether the OpenID already exists; if not, it inserts a new row, otherwise it updates the local_identifier column (to handle the same Vivo account on multiple phones). Subsequent business logic can query either side of the mapping.

After deployment, the t_account table began to contain many duplicate OpenID rows. The duplication does not break the current queries because the SQL adds LIMIT 1 , so only the row with the smallest ID is used.

Data inspection showed that about 3% of OpenIDs appear more than once, with identical creation timestamps and consecutive auto‑increment IDs. Simulating concurrent client calls reproduced the duplicate inserts, indicating a classic concurrency conflict caused by retries, network latency, or server overload that leads to multiple requests for the same OpenID.

The typical pattern is a non‑atomic "check‑then‑insert" sequence: multiple concurrent requests both see that the OpenID is absent and then both insert, resulting in duplicate rows.

Two broad remediation directions were considered:

Database‑level protection – add a UNIQUE index on the open_id column.

Application‑level protection – use a distributed lock to serialize the critical section.

Database solution : Adding a UNIQUE index prevents duplicate inserts; the first request succeeds, the second receives a duplicate‑key error.

ALTER TABLE t_account ADD UNIQUE uk_open_id( open_id );

When the index is in place, a duplicate insert yields an error such as:

Error Code: 1062. Duplicate entry 'xxx' for key 'uk_open_id'

Application solution : Because the service runs on multiple nodes, a simple Java synchronized block is insufficient. A distributed lock is required to make the "check‑then‑insert" operation atomic across processes.

Distributed locks must satisfy several properties (single holder, high availability, performance, re‑entrancy, expiration, blocking/non‑blocking). Common implementations include:

Database‑based lock tables.

Zookeeper sequential nodes.

Redis SET NX EX pattern.

1. Database lock – a lock table with a UNIQUE index on method_name :

CREATE TABLE `myLock` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `method_name` varchar(100) NOT NULL DEFAULT '' COMMENT '锁定的方法名',
  `value` varchar(1024) NOT NULL DEFAULT '锁信息',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_method_name` (`method_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='锁定中的方法';

-- lock
INSERT INTO myLock(method_name, value) VALUES('m1', '1');

-- unlock
DELETE FROM myLock WHERE method_name='m1';

2. Zookeeper lock – create a temporary sequential Znode under a lock directory, watch the predecessor node, and acquire the lock when it becomes the smallest node.

3. Redis lock – use the atomic SET key value NX EX seconds command to acquire the lock and DEL key to release it. A Lua script can combine the two operations to avoid the race between SETNX and EXPIRE .

SET key value [EX seconds] [PX milliseconds] NX

DEL key

In this case study, a Redis‑based lock was chosen. The following Java class implements a simple lock with a 3‑second TTL using JedisCluster:

public class RedisLock {
    private static final String LOCK_SUCCESS = "OK";
    private static final String LOCK_VALUE = "lock";
    private static final int EXPIRE_SECONDS = 3;

    @Autowired
    protected JedisCluster jedisCluster;

    public boolean lock(String openId) {
        String redisKey = this.formatRedisKey(openId);
        String ok = jedisCluster.set(redisKey, LOCK_VALUE, "NX", "EX", EXPIRE_SECONDS);
        return LOCK_SUCCESS.equals(ok);
    }

    public void unlock(String openId) {
        String redisKey = this.formatRedisKey(openId);
        jedisCluster.del(redisKey);
    }

    private String formatRedisKey(String openId){
        return "keyPrefix:" + openId;
    }
}

The service method was refactored to acquire the Redis lock before performing the synchronization logic and to release it in a finally block:

public class AccountService {
    @Autowired
    private RedisLock redisLock;

    public void submit(String openId, String localIdentifier) {
        if (!redisLock.lock(openId)) {
            // concurrent request lost the lock – discard
            return;
        }
        try {
            Account account = accountDao.find(openId);
            if (account == null) {
                // insert
            } else {
                // update
            }
        } finally {
            redisLock.unlock(openId);
        }
    }
}

Because the duplicate data volume is large, a scheduled task was added to clean up old duplicate OpenIDs in batches of 1,000 rows per minute, stopping the task once cleanup is complete.

Conclusion : Systematic analysis identified a concurrency bug caused by non‑atomic check‑then‑insert logic. After weighing database‑level uniqueness against application‑level distributed locking, the team adopted a Redis‑based distributed lock for immediate mitigation and scheduled data cleanup, while keeping the option to add a unique index later.