Oracle并发请求：超大规模表的Record ID分配方案问询

Hey there! Let's dive into your question about using stored procedures for record_id allocation with your massive 1-billion-row table, plus daily batch file processing and Web app ID reservations. I'll break down feasibility first, then share targeted optimization tips.

First off, yes, using stored procedures is a feasible approach—here's why it makes sense for your scenario:

• Centralized, consistent logic: Wrapping ID allocation in a stored procedure ensures both your batch file processing and Web app use the same rules for generating/reserving IDs. This eliminates the risk of duplicate IDs from disjointed code paths.
• Transaction-safe operations: You can embed transaction logic directly in the procedure to guarantee that ID allocation and any dependent actions (like marking a reservation) are atomic. No orphaned IDs or incomplete reservations.
• Works at scale (with tweaks): A 1-billion-row table is large, but as long as you avoid naive ID generation patterns, stored procedures can handle the load without becoming a bottleneck.

That said, there are potential pitfalls to watch for:

• Single-point contention: If every request hits the same stored procedure to get a single ID, you could run into lock waits or slowdowns under high concurrency.
• Inefficient locking: Using a MAX(record_id) + 1 approach will trigger heavy table/row locking, which is a non-starter for 1B-row tables with frequent writes.

To make this work smoothly at your scale, here are the key optimizations to implement:

1. Ditch "Max(ID) + 1"—Use Pre-Allocated ID Pools #

Instead of generating IDs one at a time by querying the main table's max ID, create a dedicated record_id_reservations table to manage batches of pre-allocated IDs. Your stored procedure can:

• Reserve a batch of IDs (e.g., 1000 at a time) from a sequence or calculated range.
• Mark the batch as "in use" for either batch processing or Web app requests.
• Let consumers (file processor/Web app) take IDs from the batch until it's exhausted.

Example schema for the reservation table:

CREATE TABLE record_id_reservations (
    batch_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    start_id BIGINT NOT NULL,
    end_id BIGINT NOT NULL,
    used_count INT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'AVAILABLE', -- AVAILABLE/IN_USE/EXHAUSTED
    reserved_for VARCHAR(50) -- e.g., 'BATCH_PROCESSING'/'WEB_APP'
);

This reduces direct hits to your 1B-row table and minimizes lock contention since you're only updating the small reservation table in batches.

2. Leverage Database-Native Sequence Objects #

Most modern databases (PostgreSQL, MySQL 8.0+, SQL Server) have built-in sequence objects that handle concurrent ID generation efficiently—way better than rolling your own logic. Your stored procedure can call these sequences directly to get single or batches of IDs.

For example, in MySQL:

-- Create a sequence starting just after your existing 1B records
CREATE SEQUENCE record_id_seq START WITH 1000000001;

-- Stored procedure to get a single ID
DELIMITER //
CREATE PROCEDURE get_single_record_id(OUT new_id BIGINT)
BEGIN
    SET new_id = NEXT VALUE FOR record_id_seq;
END //
DELIMITER ;

-- Stored procedure to get a batch of IDs
DELIMITER //
CREATE PROCEDURE get_batch_record_ids(IN batch_size INT, OUT start_id BIGINT, OUT end_id BIGINT)
BEGIN
    SET start_id = NEXT VALUE FOR record_id_seq;
    SET end_id = start_id + batch_size - 1;
    -- Advance the sequence to the end of the batch (avoids reusing IDs if the batch is abandoned)
    CALL seq_set_val('record_id_seq', end_id + 1);
END //
DELIMITER ;

Sequences are optimized for concurrency, so they'll handle thousands of requests per second without locking issues.

3. Separate ID Allocation from Data Insertion #

Don't tie ID allocation directly to inserting records into your big table. Instead:

• For batch files: First call the stored procedure to get a batch of IDs, then do a bulk insert using those IDs.
• For Web apps: Reserve a batch of IDs upfront, then let the app use them as needed for new records.

This keeps your stored procedure lightweight (no heavy insert logic) and reduces transaction duration, which lowers lock contention.

4. Optimize Locking and Isolation #

If you're using a custom reservation table:

• Keep transactions in the stored procedure as short as possible. Mark a batch as "IN_USE" immediately, then commit the transaction—don't wait for the consumer to use all IDs.
• Use the lowest isolation level that works for your use case (e.g., READ COMMITTED instead of REPEATABLE READ) to avoid unnecessary lock escalation.

5. Plan for Horizontal Scaling (If Needed) #

If your traffic grows beyond a single database instance, consider:

• Sharded ID ranges: Assign distinct ID ranges to different workloads (e.g., 1-2B for batch processing, 2B+ for Web apps). This lets you use separate sequences or stored procedures for each range, eliminating cross-workload contention.
• Distributed ID generators: If you need to scale across multiple databases, look into patterns like Snowflake IDs (which embed timestamp, worker ID, and sequence) but note that this moves ID generation outside the database—so only go this route if stored procedures alone can't keep up.

6. Monitor and Tune #

• Add indexes to your reservation table (e.g., a composite index on status and reserved_for to quickly find available batches).
• Track batch usage to adjust batch sizes: If Web apps typically reserve 20 IDs at a time, set batch sizes to 500 instead of 1000 to reduce unused IDs.
• Monitor lock waits and stored procedure execution times using your database's built-in tools (e.g., MySQL's Performance Schema, PostgreSQL's pg_stat_statements) to catch bottlenecks early.

Stored procedures are absolutely a viable solution here—you just need to avoid the common anti-patterns (like MAX(ID) + 1) and lean into database-native tools (sequences) and pre-allocation to keep things efficient at scale.

内容的提问来源于stack exchange，提问作者Lokesh UV