MySQL Locking issue

Mysql locks when a query executed

[jcodemasters]

Hi,
Recently I am facing issue with mysql locking.. I don't its due to mysql load, incorrect mysql configuration or query.. so I thought I should get advice here..

i am using three tables to get the data from database.. it will return 5 rows.

users
profile (update date is should not be null)
online (expected return value Null)

SELECT U.user_name,U.age, U.gender, U.country, U.picture, O.user AS `user_online`, U.picture_thumb FROM users U INNER JOIN user_profile AS P ON U.user_id = P.user_id LEFT JOIN online AS O ON U.user_name=O.user WHERE U.ban=0 AND U.active=1 AND P.update_date IS NOT NULL ORDER BY P.update_date DESC LIMIT 0,5

secondly I have to use lock tables during update.. if i don't use i see tons lock threads in mysql administration tool..

any suggestion? why its locking

[phparion]

I am a little confused with your explanation to the problem however I can add my two cents as follow,

1 - it is not necessary that mysql is locking your all working queries, the mysql administrator tool also shows those queries as locked which have been executed but not completed for a long time and the mysql server is still trying to get you the output of those queries. This affects your mysql server load too.

Sol: kill all mysql processes and keep the administrator tool opened. Check your server load. then execute your this very query from command line or webpage, ONLY ONCE, check again the administrator tool and server load, if load is increasing and the query is in processes it means your query have some problem. It's output is very long or ambigous and mysql is lost to execute it properly for you.

2 - are you sure your this query works fine? did you display it to check its structure for any mistakes?

3 - you can try asking this question in mysql forum too am not sure if DBAs visit php forum often. they are expert to handle such situations.

[jcodemasters]

I found the issue.. actually problem is in online table.. its being used through out the website i.e users profile, random user profile list on each page etc. so when ever its updated it cause the locking.. I am wondering i am using lock tables but it still cause the lock..

here is the function where it stucks

<?php
$time_to_check_online = time() + (5 * 60); //5 min check to avoid the one query execution each page

function fill_online()
{
global $user_online, $max_time_online, $mydb,$time_to_check_online;

$query = "SELECT * FROM online WHERE `session_id` = '" . session_id() . "'";
$result = $mydb->query( $query );

$ip = $_SERVER['REMOTE_ADDR'];
$page = $_SERVER['REQUEST_URI'];
$browser = $_SERVER['HTTP_USER_AGENT'];

if( $result && mysql_num_rows($result) ) {
$user_field = '';
if($user_online!= '') {
$user_field = "`user`='$user_online',";
}
else {
$user_field = "`user`='',";
}
$query = "UPDATE `online` SET $user_field `browser` = '$browser',`ip`='$ip', `time` = '".date("YmdHi")."'
WHERE `session_id` = '" . session_id() . "'";
}
else {
if ($user_online!= '') {
$query = "INSERT INTO `online` (`session_id`, `user`, `time`, `ip`, `browser`, `page`)
VALUES('".session_id()."','$user_online','". date( "YmdHi" )."','$ip','$browser','$page')";
}
else {
$query = "INSERT INTO `online` (`session_id`, `time`, `ip`, `browser`, `page`)
VALUES('".session_id()."', '". date( "YmdHi" )."','$ip','$browser','$page')";
}

}
@mysql_free_result($result);
$mydb->query("LOCK TABLES `online` WRITE");
$mydb->query($query);
if( ($_SESSION['online']-time()) <= 0) {
$_SESSION['online'] = $time_to_check_online;
//DELETE
$now = date("YmdHi"); // yyyymmddhhmm, ex. 200009021431
$timer_ago = date("YmdHi", mktime(date("H"), date("i") - $max_time_online, 0,
date("m"), date("d"), date("Y")));
$query = "DELETE FROM `online` WHERE `time` < '" . $timer_ago ."'";
$mydb->query($query);
}
$mydb->query("UNLOCK TABLES");
}

if(!isset($_SESSION['online'])) {
$_SESSION['online'] = $time_to_check_online; //add 5 mins check
}
?>