Scalable Railway Ticket System Design: Architecture, Database & Performance

Introduction

Inspired by recent news about the Ministry of Railways' new ticketing system, the author shares a detailed design proposal for a railway ticketing platform, inviting discussion and critique.

Problem Statement

The current 12306 system suffers from login difficulty during holidays, limited concurrency in the ticket‑grabbing phase, and slow seat‑availability queries. Users also complain about mandatory account login for seat queries.

Optimization Proposals

Seat‑Availability Optimization

Separate the seat‑availability service from the ticket‑grabbing service and deploy the former on CDN‑mirrored sites, while the latter runs on dedicated servers. Use read‑write splitting: the primary database handles writes (ticket purchase, real‑time seat updates) and an asynchronous replica serves seat queries. Consider a NoSQL store for fast reads, as millisecond‑level freshness is unnecessary for seat checks.

Option 1 – Memory‑Centric with Database Backup

Implement a distributed data‑distribution system (SApp as master, CApp as mirrors) that keeps seat data in memory and synchronizes updates every 15 seconds (or longer if no changes). Failover reads from the database or a NoSQL cluster.

Option 2 – Pure Database

Shard seat data by city or region, placing hot data on CDN mirrors and using read‑only replicas for queries.

Ticket‑Grabbing Optimization

Separate authentication from the ticket‑grabbing service, place them behind load balancers, and route each ticket request to a dedicated message queue. Return a queue position ID to the client and process tickets asynchronously. Deploy a separate unpaid‑order monitor that clears timed‑out orders every minute.

Database Design and Non‑Functional Requirements

The database layer is critical for performance, availability, and scalability. The system is an OLTP workload with occasional OLAP analysis, so Oracle is preferred for core transactions, while MySQL can serve less critical data. NoSQL is considered for caching but not for primary consistency.

Data Center Redundancy

Deploy multiple data centers to avoid single‑point failures, with synchronous log replication for same‑city backup and asynchronous replication for remote backup. Diagrams illustrate both same‑city and remote backup architectures.

Sharding Strategy

Five logical databases are proposed: passenger, ticket, SMS, train‑schedule (including seat availability), and payment. Passenger data fits in a single instance; train‑schedule data is modest and also stays in one instance. Ticket, payment, and SMS data require further sharding due to massive volume.

Ticket sharding uses a simple modulo on a globally unique ticket ID (e.g., last digit) to distribute data across ten online shards plus one historical shard, supporting up to 100 billion annual transactions.

Seat‑Availability Database Details

Seat‑availability must provide real‑time data; thus read‑write separation is avoided. The design emphasizes minimal lock scope, short transaction duration, and heavy use of asynchronous processing.

Locking Considerations

Pessimistic locking is required to prevent over‑booking. The lock duration is assumed to be ~0.1 s per request, which is acceptable if the workload is spread across multiple shards.

Cache Risks and Strategies

Cache failures, system releases, and extreme events (hardware crashes, power loss) can cause data loss. Therefore, a robust cache cluster with failover and periodic synchronization to the database is essential.

Overall Architecture Model

The final architecture combines CDN‑mirrored seat‑availability services, sharded transactional databases, message‑queue‑driven ticket processing, and separate services for authentication, payment, and SMS notifications.

Future Work

Further articles will cover table structures, indexing, distributed transaction handling, and detailed seat‑availability algorithms (e.g., segment trees for multi‑segment seat accounting).

select * from seat_availability;

insert into ticket (...);

update seat_availability set seats = seats - 1 where ...;

update ticket set status='WAIT_PAY' where id = ...;

update ticket set status='PAY' where id = ...;