Resolving Duplicate OpenID Records with Distributed Locks in a Fast App Backend

**Business Background**

The fast‑app center stores user‑collected app identifiers linked to an OpenID; to synchronize the collection state between the server and the client Menubar, a mapping between OpenID and a local identifier is maintained. Synchronization occurs when the user launches the app center, inserting or updating the mapping in the t_account table.

**Problem Analysis**

After deployment, many duplicate OpenID rows were observed in t_account. Although queries used LIMIT 1 and thus were not affected, the duplicates indicated a concurrency bug. Investigation showed that duplicate rows shared the same creation timestamp and consecutive auto‑increment IDs, pointing to concurrent insert requests caused by client retries after timeouts or server overload.

**Possible Solutions**

Two main approaches were considered:

**Database‑level solution – Unique Index**: Adding a unique constraint on open_id would let MySQL reject the second concurrent insert. ALTER TABLE t_account ADD UNIQUE uk_open_id(open_id); The error returned would be: Error Code: 1062. Duplicate entry 'xxx' for key 'uk_open_id' **Application‑level solution – Distributed Lock**: Serialize the check‑then‑insert logic using a lock, ensuring only one request can perform the operation at a time.

**Why Distributed Lock Was Chosen**

Existing duplicate rows prevented adding a unique index directly, and cleaning them required coordinated downtime. A distributed lock could be introduced without immediate data cleanup, preventing new duplicates while a later cleanup runs.

**Distributed Lock Overview**

A distributed lock must guarantee exclusive acquisition, high availability, performance, re‑entrancy, and automatic expiration. Common implementations include:

Database‑based lock tables

Zookeeper sequential nodes

Redis SET NX with expiration

**Implementation Details**

*Database lock* uses a lock table with a unique index on method_name and inserts/deletes rows to acquire/release the lock.

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='锁定中的方法';

Insert to lock, delete to unlock.

*Zookeeper* creates a temporary sequential node under a lock directory; the smallest node holder owns the lock.

*Redis* uses the atomic SET key value NX EX seconds command to acquire the lock and DEL key to release it.

**Redis Lock Java Implementation**

public class RedisLock {<br/>    private static final String LOCK_SUCCESS = "OK";<br/>    private static final String LOCK_VALUE = "lock";<br/>    private static final int EXPIRE_SECONDS = 3;<br/>    @Autowired<br/>    protected JedisCluster jedisCluster;<br/><br/>    public boolean lock(String openId) {<br/>        String redisKey = this.formatRedisKey(openId);<br/>        String ok = jedisCluster.set(redisKey, LOCK_VALUE, "NX", "EX", EXPIRE_SECONDS);<br/>        return LOCK_SUCCESS.equals(ok);<br/>    }<br/><br/>    public void unlock(String openId) {<br/>        String redisKey = this.formatRedisKey(openId);<br/>        jedisCluster.del(redisKey);<br/>    }<br/><br/>    private String formatRedisKey(String openId){<br/>        return "keyPrefix:" + openId;<br/>    }<br/>}

The lock expires after 3 seconds, sufficient for the quick insert/update operation.

**Improved Service Logic Using the Lock**

public class AccountService {<br/><br/>    @Autowired<br/>    private RedisLock redisLock;<br/><br/>    public void submit(String openId, String localIdentifier) {<br/>        if (!redisLock.lock(openId)) {<br/>            // concurrent request lost the lock, discard<br/>            return;<br/>        }<br/>        try {<br/>            Account account = accountDao.find(openId);<br/>            if (account == null) {<br/>                // insert<br/>            } else {<br/>                // update<br/>            }<br/>        } finally {<br/>            redisLock.unlock(openId);<br/>        }<br/>    }<br/>}

**Data Cleanup**

A scheduled task runs every minute, deleting up to 1,000 duplicate OpenID rows each run to avoid overwhelming the database. Once cleanup completes, the task is disabled and the lock‑only solution remains in production.

**Conclusion**

The case demonstrates a systematic approach: identify the root cause of duplicate records, evaluate database‑level versus application‑level fixes, choose a pragmatic distributed‑lock implementation with Redis, and complement it with a cleanup routine, illustrating best practices for handling concurrency issues in backend services.