[DATABASE] - Query Builder for LOCKING Query Select

I have many request at same time, with TRANSACTION make unique string increment like INVCLIENT2024070001 - 9999. Without LOCKING query select num row last format can be duplicate.

SUCCESS

PHP Code:

$resultKrediturCountData = $this->db->query("SELECT COUNT(ad.debitor_no) AS debitor_count FROM askred_debitor ad WHERE ad.creditor_no = ? FOR SHARE", [$creditor_no]); 


FAILED (Duplicate)

PHP Code:

$this->db->select("COUNT(ad.debitor_no) AS debitor_count");
$this->db->from('askred_debitor ad');
$this->db->where("ad.creditor_no", $creditor_no);

$resultKrediturCountData = $this->db->get(); 


Maybe this can build with query builder like :

PHP Code:

$this->db->select("COUNT(ad.debitor_no) AS debitor_count");
$this->db->from('askred_debitor ad');
$this->db->where("ad.creditor_no", $creditor_no);
$this->db->for("SHARE"); // or $this->db->for("UPDATE");

$resultKrediturCountData = $this->db->get(); 


Reference LOCKING :

1. https://dev.mysql.com/doc/refman/8.4/en/...reads.html
   
2. https://www.postgresql.org/docs/current/...CKING-ROWS
   
3. https://www.sqlite.org/lockingv3.html