如何防止单次请求中重复执行CodeIgniter调用的存储过程？

Great question! When dealing with invoice number generation (or any state-changing stored procedure), accidental duplicate calls in a single request can lead to invalid or duplicate invoice numbers. Here are a few practical, request-level safeguards you can implement in CodeIgniter:

1. Cache the Result in CodeIgniter Session #

Sessions persist for the duration of the request, so you can store the generated invoice number once fetched and reuse it for the rest of the request flow.

Here’s how to implement this in your model or controller:

public function get_invoice_number($state_code) {
    // Check if we already have the invoice number stored in session
    $invoice_no = $this->session->userdata('current_invoice_no');
    
    if (!$invoice_no) {
        // Execute the stored procedure only once per request
        $query = $this->db->query("Declare @CurrentInvoiceNo int Execute spSetInvoiceNoStatewise '$state_code', @CurrentInvoiceNo output select @CurrentInvoiceNo");
        $result = $query->row();
        $invoice_no = $result->CurrentInvoiceNo;
        
        // Save to session for subsequent calls in the same request
        $this->session->set_userdata('current_invoice_no', $invoice_no);
    }
    
    return $invoice_no;
}

Note: If you only need this value for the current request, clear it after processing (e.g., after saving the invoice) with $this->session->unset_userdata('current_invoice_no');.

2. Use a Static Variable in Your Model/Controller #

Static variables retain their value for the lifetime of the request, making this a lightweight caching option without relying on sessions.

Example in a model:

class Invoice_model extends CI_Model {
    private static $cached_invoice_no = null;
    
    public function get_invoice_number($state_code) {
        if (self::$cached_invoice_no === null) {
            // Run the stored procedure once per request
            $query = $this->db->query("Declare @CurrentInvoiceNo int Execute spSetInvoiceNoStatewise '$state_code', @CurrentInvoiceNo output select @CurrentInvoiceNo");
            $result = $query->row();
            self::$cached_invoice_no = $result->CurrentInvoiceNo;
        }
        
        return self::$cached_invoice_no;
    }
}

No matter how many times you call get_invoice_number() in the same request, the stored procedure will only execute once.

3. Wrap Logic in a Singleton Service (For Larger Apps) #

If you’re building a more complex application, a singleton service ensures only one instance exists per request, guaranteeing the invoice number is generated once.

class InvoiceService {
    private static $instance = null;
    private $invoice_no;
    
    private function __construct() {}
    
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    public function getInvoiceNumber($state_code, $db) {
        if ($this->invoice_no === null) {
            $query = $db->query("Declare @CurrentInvoiceNo int Execute spSetInvoiceNoStatewise '$state_code', @CurrentInvoiceNo output select @CurrentInvoiceNo");
            $result = $query->row();
            $this->invoice_no = $result->CurrentInvoiceNo;
        }
        return $this->invoice_no;
    }
}

Use it in your controller like this:

$invoice_service = InvoiceService::getInstance();
$invoice_no = $invoice_service->getInvoiceNumber('TN', $this->db);

Bonus: Database-Level Safeguard #

Even with application-level checks, add a database lock to prevent race conditions from concurrent requests. Modify your stored procedure to use UPDLOCK when selecting the invoice number:

CREATE Procedure [dbo].[spSetInvoiceNoStatewise] 
    @StateCode nvarchar(20), 
    @InvoiceNo int = 0 output 
AS 
Begin 
    BEGIN TRANSACTION
    -- Lock the row during update to avoid concurrent duplicates
    select @InvoiceNo=invoice_no from statewise_invoice_no with (UPDLOCK) where invoice_state_code = @StateCode;
    
    -- Increment and update the invoice number (adjust this to match your logic)
    update statewise_invoice_no set invoice_no = @InvoiceNo + 1 where invoice_state_code = @StateCode;
    
    COMMIT TRANSACTION
End

内容的提问来源于stack exchange，提问作者Akash lal