How to Efficiently Manage User Lockout: Redis Cache vs Database Storage

Project Background

The member center has 10 million registered users and is being refactored using Java, Oracle, and Redis for caching.

Use Case

When a member fails to log in more than three times, the account must be locked; deciding where to store and how to clean up this lock state is a key problem.

Login Flow (Simplified)

Precondition: user has an account and opens the login page.

Front‑end: member enters username and password.

Back‑end: verify credentials; on failure increment error count.

After three failed attempts, back‑end updates the member status to locked.

Post‑condition: subsequent logins or business requests show the locked status.

Domain Analysis

The lock state is temporary and cleared after 24 hours.

It affects business scenarios that need to check member status.

Solution Analysis

1) Store in Redis cache

Pros: avoids database access, improves performance, leverages Redis eviction.

Cons: strong dependency on cache (lock lost if cache fails), increased system and code complexity, multiple subsystems must read the cache.

2) Store in a database field

Pros: high data consistency, simpler code, no need for each interface to query the cache.

Cons: slight performance impact, requires a scheduled task to clear expired locks.

Refactoring Plan

Conclusion: Although cache‑based storage suits architectures with full member caching, our project does not use such an architecture, leading to coupling and double‑checking. Therefore we adopt the database approach.

Changes:

Add a status column for login lock in the member table.

Update the DB record to locked after exceeding login attempts.

Introduce a scheduled job to clear expired lock states.

Future Optimizations

Create a lock‑log table to record locked member IDs, reducing full scans.

Cache all member data and store lock state temporarily in Redis.

Design Insights

Architecture decisions must align with business volume, use cases, non‑functional requirements, and team capacity. The optimal solution is the one that fits the current context, offering foresight without over‑engineering, suitable for fast‑iteration, MVP‑style development.

We welcome discussion and suggestions.