0

I have tables (tahminler, users) with InnoDB engine, I read that InnoDB uses row-lock and have MVCC feature and those two things make it possible to execute read queries (SELECT queries) even when there are simultaneous write queries (UPDATE or INSERT).

I have the following scenario In my website: 1. user1 is using the following code to insert information in two tables:

        $id=$_POST['id'];
        $user_id=$_POST['user_id'];
        $tahmin=$_POST['tahmin'];
        $tahmin_text=$_POST['tahmin_text'];
        include_once("mysql.class.php");
        include_once("config.php");
        $db1;
        $db1 = new db_mysql($conf['db_hostname'], $conf['db_username'], $conf['db_password'], $conf['db_name']);

        $current_server_date =  date('Y-m-d H:i:s');// Your local server time
        date_default_timezone_set('Asia/Istanbul');
        $current_pc_date = date('Y-m-d H:i:s');

        $sql1 = $db1->query("INSERT INTO tahminler (tahmin, tahmin_text, match_id, user_id, timestamp) 
VALUES ('$tahmin','$tahmin_text', $id, $user_id, '$current_pc_date')");

        $sql1 = $db1->query("UPDATE `users` 
SET daily_tahmin =(daily_tahmin+1), monthly_tahmin =(monthly_tahmin+1)  
WHERE id=$user_id");

2.At the same time another user,user2, is trying to ask for the information of the previous tables:

$sql1 = $db1->query("SELECT * FROM tahminler");
$sql1 = $db1->query("SELECT * FROM users");

When the user2 ask to read some information from the database even the engines of tables are InnoDB but the database block the read request until the user1 finishes his write query.

I changed the above code and add some lines to it to solve the problem using Transactions feauter like following:

        $id=$_POST['id'];
        $user_id=$_POST['user_id'];
        $tahmin=$_POST['tahmin'];
        $tahmin_text=$_POST['tahmin_text'];
        include_once("mysql.class.php");
        include_once("config.php");
        $db1;
        $db1 = new db_mysql($conf['db_hostname'], $conf['db_username'], $conf['db_password'], $conf['db_name']);

        $db1->query("SET AUTOCOMMIT=0");//new
        $db1->query("START TRANSACTION");//new

        $current_server_date =  date('Y-m-d H:i:s');// Your local server time
        date_default_timezone_set('Asia/Istanbul');
        $current_pc_date = date('Y-m-d H:i:s');

        $sql1 = $db1->query("INSERT INTO tahminler (tahmin, tahmin_text, match_id, user_id, timestamp) 
VALUES ('$tahmin','$tahmin_text', $id, $user_id, '$current_pc_date')");

        $sql1 = $db1->query("UPDATE `users` 
SET daily_tahmin =(daily_tahmin+1), monthly_tahmin =(monthly_tahmin+1)  
WHERE id=$user_id");

        $db1->query("COMMIT");//new

I add three new lines to make the queries of the update and insert executed inside the transaction to let THE READ OF OTHER TABLES AND RECORDS POSSIBLE, but unfortunately the read-block still as it is even after adding those lines , so any body can tell me if there is any missing query or if I make any mistake here ??

Basel
  • 359
  • 3
  • 16
  • Check whether tables are really InnoDb, and not MyISAM. If they are InnoDb, then check the isolation level - if the isolation level is `serializable`, then updates will block reads. – krokodilko Nov 24 '13 at 21:53
  • I check it is repeatable-read, how can I change it? – Basel Nov 24 '13 at 22:03

0 Answers0