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 :


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.


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.


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

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

$dbname = 'carpooling';


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

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

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

function showValue($res){
  # mysql_close();
 if (!$res) {
    die('Could not query:' . mysql_error());
  while ($i < $num) {
    echo "RESULT IS : $first \n";
    echo "----------------------\n";

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

dino@linux-vxrd:~/Documents/php> php locks.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.

Simple Idea For Passing Data To Huge Number of Users

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

Three years ago, I had a situation when I was building a web application for a stocks consultant. It should provide users with updates , in a page that gets automatically refreshed every 1 minute.

My problem was that when the number of logged in users exceed 100 users, the page turns to be extreemly slowly rendered.

The problem was that I made that page retrieve the data from database every time the page refresh. Ofcourse it should.

I thought of a simple solution, as there was one stocks consultant ( for me data editor ), and it was one page that is needed to get rendered. I decided not to make it dynamically retrieved, but dynamically created.

Which means, I create the HTML page directly after the stocks consultant finishs editing his data that will be shown to his clients ( At that time I wasn’t using AJAX , and the output was fairly simple ). And, With each refresh I retrieve the HTML static page, that was previously created.

After a while, I decided to make it a little more efficient, by checking the file’s SHA1 and compare it with the latest SHA1 of the same file, if they are the samee ( File didn’t change from last retrieval ), I return back a copy that was previously stored in Memory, else I retrieve the generated file, update the memory cache, then send the memory cache back to users.

Ofcourse, If I am creating this application right now, I would be using the same idea, except for saving data in JSON not HTML and send it back to users ( ofoucrse using AJAX ) and render it ( at the client side ).

I dunno actually if compressing JSON data using GZIP is applicable or what, I hope someone tell me that !! ,, ( ofcourse if I’m creating a similar application right now , I will be asking Uncle Google’s Help for GZIP with JSON )

Hope I could be of some help.