#1
  1. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,350
    Rep Power
    61

    Issues trying to create row level lock


    Ok, so I have a table that because or race conditions I'm trying to do a row-level lock but clearly I have no idea what I'm doing. At first I thought I did! Ok, so here's the scenario.

    So I opened up mysqlworkbench and ran the following commands:

    Code:
    set autocommit=0;
    select id from table1 where id=1 for update;
    Then, I created a php page where I attempted to select that row for update and the page hung. So far, so good. Then, I went back to mysqlworkbench and ran a commit command and the webpage immediately displayed the results of my select statement. Awesome! I know what I'm doing!

    So now, I created a php page with the following:

    PHP Code:
    $sql "/* " __FILE__ "*/ 
              select
                id
              from
                table1
              where
                id=1 for update"
    ;
                    
    $result     mysqli_query($cnx$sql) or die("Could not select with $sql because: " mysqli_error($cnx));
    $row1     mysqli_fetch_array($result);

    print 
    "The value is {$row['game_id']}";
    usleep(100000000); 
    Ok, so I now have a row level lock on id=1. I go into my mysqlworkbench and sure enough, when I try and select that row for updating, it just hangs. Now for the issues. I tried going back to my php page and unlocking the row with

    PHP Code:
    $cnx->commit(); 
    Problem is, the row won't unlock. I go back to mysqlworkbench and try to select the row for updating and the command just hangs. I also try and select the row from php and it also hangs. So how do you unlock rows that you previously locked with php? And how can I view row level locks? Thanks in advance.
    Correspondence chess
    nothingbutchess.com
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,965
    Rep Power
    9397

    Moved


    I think you'll get better answers in here than in PHP.
  4. #3
  5. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,350
    Rep Power
    61
    Ok, I've read of cases where people had deadlocks like this and they simply had to wait for the lock to release. Sure enough, my lock released.

    The problem was that my php script, which obtained the lock, didn't initially do the commit. I tested it by doing something like

    1. Turn autocommit off
    2. Run select statement for update
    3. stop execution of script
    4. confirm that I can't get access to that row from mysqlworkbench

    What I should have done instead was

    1. Turn autocommit off
    2. Run select statement for update
    3. make script sleep for 15 seconds
    4. confirm that I can't get access to that row from mysqlworkbench
    5. script stops sleeping and runs the commit command I placed after sleep command

    Point is, the script that created the lock must also do the commit. Now I see another problem.

    What if in the middle of running a script, someone loses their internet connection. Or the page doesn't load fully. So 1 and 2 run, then the script hangs and the person refreshes the page. Now when they go to refresh the page, they have a row that's locked, even to them. I'm not sure if I see a way around this.
    Correspondence chess
    nothingbutchess.com
  6. #4
  7. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    The problem was that my php script, which obtained the lock, didn't initially do the commit.
    Transactions and locks are specific to a particular MySQL connection or session. Each running instance of a PHP script has its own connection to the MySQL server, so it can't share transactions or locks with other instances of PHP scripts (even of the same script).

    When the MySQL connection is closed, MySQL will release any resources locked by that connection. As long as you are not using persistent connections and as long as PHP terminates normally, it will automatically close any open MySQL connections when it finishes running the script. An abnormal termination would be something like a segfault, sigkill or a hard power reset, not an interrupted network connection.

    However, in the case of an abnormal termination, the row would remain locked until the lock or connection expires.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo