How do I lock mysql tables in php? I currently have this code:
$db->query("LOCK TABLES tbl_othercharge WRITE");
for($x=0;$x<=500; $x++){
$id = get_max();
$db->query("INSERT INTO tbl_othercharge SET tblocID = '$id', assessmentID='lock1'");
}
$db->query("UNLOCK TABLES");
Here's the get_max() function, which obviously will fail if the script above executes simultaneously.
<?php
function get_max(){
global $db;
$max = $db->get_var("SELECT MAX(tblocNumber) FROM tbl_othercharge");
if($max == null){
$max = 1;
}else if($max >= 1){
$max = $max + 1;
}
return 'OC'.$max;
}
?>
I'm trying to test if there are still concurrency problems by executing the same script on 2 browsers. The script above inserts 400+ records instead of 999 records. How do I properly lock the table while I'm inserting something into it.
I want to lock the table to prevent something like this to happen:
As you can see the field with the prefix 'OC' on it should have a number which is equal to the auto-increment primary key.
The only reliable solution is to do an insert with a dummy value, getting the last insert id, and updating the row to the correct value.
mysql_query("INSERT INTO table (field) VALUES (dummy);");
$id = mysql_last_insert_id();
mysql_query("UPDATE table SET field='OC{$id}' WHERE id={$id} LIMIT 1;");
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With