Testing MySQL GET_LOCK() using PHP

[ This Blog Was Moved To : http://www.diknows.com ]


Early this morning, a friend of mine was stuck in GET_LOCK() MySQL function. She was using it in her application. The function was working very well from the MySQL Console , but it was not working from PHP ( Neither from her application, nor from PHPMyAdmin web interface).

She asked my help, so I searched Google, and had a look at the MySQL Documentation about this function.

This function as it appears in the MySQL Documentation, does the following :

GET_LOCK(str,timeout)

Tries to obtain a lock with a name given by the string str, using a timeout of timeout seconds. Returns 1 if the lock was obtained successfully, 0 if the attempt timed out (for example, because another client has previously locked the name), or NULL if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill). If you have a lock obtained with GET_LOCK(), it is released when you execute RELEASE_LOCK(), execute a new GET_LOCK(), or your connection terminates (either normally or abnormally). Locks obtained with GET_LOCK() do not interact with transactions. That is, committing a transaction does not release any such locks obtained during the transaction.

This function can be used to implement application locks or to simulate record locks. Names are locked on a server-wide basis. If a name has been locked by one client, GET_LOCK() blocks any request by another client for a lock with the same name. This allows clients that agree on a given lock name to use the name to perform cooperative advisory locking. But be aware that it also allows a client that is not among the set of cooperating clients to lock a name, either inadvertently or deliberately, and thus prevent any of the cooperating clients from locking that name. One way to reduce the likelihood of this is to use lock names that are database-specific or application-specific. For example, use lock names of the form db_name.str or app_name.str.

mysql> SELECT GET_LOCK('lock1',10);
        -> 1
mysql> SELECT IS_FREE_LOCK('lock2');
        -> 1
mysql> SELECT GET_LOCK('lock2',10);
        -> 1
mysql> SELECT RELEASE_LOCK('lock2');
        -> 1
mysql> SELECT RELEASE_LOCK('lock1');
        -> NULL

The second RELEASE_LOCK() call returns NULL because the lock 'lock1' was automatically released by the second GET_LOCK() call.

If multiple clients are waiting for a lock, the order in which they will acquire it is undefined and depends on factors such as the thread library in use. In particular, applications should not assume that clients will acquire the lock in the same order that they issued the lock requests.

Note

If a client attempts to acquire a lock that is already held by another client, it blocks according to the timeout argument. If the blocked client terminates, its thread does not die until the lock request times out. This is a known bug (fixed in MySQL 5.6).

So, after reading the above wonderful documentation, I thought of several reasons “Why would she not be able to obtain a lock using that function from PHP ? ” and I guessed the following :

  1. She may have called GET_LOCK and never called RELEASE_LOCK and tried to call GET_LOCK again with the same lock name.
  2. She may be calling GET_LOCK from a script where the connection is closed by the end of that script and then tries to check IS_FREE_LOCK from another script.
  3. Something is error in her SETUP ( Which I have no clue how to solve ).

After arguing with her over these points, she proved to me that she is doing every thing the right way. So, I created the following PHP Script to check if there is really and issue in PHP calling GET_LOCK MySQL function.


<?php

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');

$dbname = 'carpooling';
mysql_select_db($dbname);

$idProjetRevision=50;

function LockProjet( $idProjetRevision)
 {
  $sql = "SELECT GET_LOCK('LockedProject_$idProjetRevision', 28800) AS 'GetLock'"; //8 hours lock
  $res = mysql_query($sql);
  showValue($res);
}

function IsLockedProjet( $idProjetRevision)
 {
  $sql = "SELECT IS_FREE_LOCK('LockedProject_$idProjetRevision');"; //8 hours lock
  $res = mysql_query($sql);
  showValue($res);
}

function ReleaseProjet($idProjetRevision)
 {
  $sql = "SELECT RELEASE_LOCK('LockedProject_$idProjetRevision'); "; //8 hours lock
  $res = mysql_query($sql);
  showValue($res);
}

function showValue($res){
  # mysql_close();
 if (!$res) {
    die('Could not query:' . mysql_error());
  }
  $num=mysql_numrows($res);
  $i=0;
  while ($i < $num) {
    $first=mysql_result($res,$i);
    echo "RESULT IS : $first \n";
    echo "----------------------\n";
    $i++;
  }
}
LockProjet(50);
IsLockedProjet(50);
ReleaseProjet(50);
?>

When I tested this function on my Laptop, this was the result:

dino@linux-vxrd:~/Documents/php> php locks.php

RESULT IS : 1
----------------------
RESULT IS : 0
----------------------
RESULT IS : 1
----------------------
dino@linux-vxrd:~/Documents/php>

Which means it should be working fine. So, I asked her to use my code instead, it didn’t work either.
So, the last thing I though of is the privileges of the user she is using to connect to the database, I asked her to open MySQL Administration tool and check user privileges.


So, She checked it, but no use. So, both of us get fed up and she said she gonna do it her own way. I suggested using any ORM Framework, but she got no knowledge about it.
So, This is it.

Wish her best of luck.

All Of you too.

About these ads

One Response to “Testing MySQL GET_LOCK() using PHP”

  1. Testing MySQL GET_LOCK() using PHP « DiKnows Tech | Coder Online Says:

    [...] Read this article: Testing MySQL GET_LOCK() using PHP « DiKnows Tech [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: