MySQL concurrency with GET_LOCK

[betazold]

I trying to use GET_LOCK for application-level blocking in my application. So I research how to do it in propert way. As I can see, IS_FREE_LOCK function is non-atomic, but GET_LOCK with second parameter (timeout) equal to 0 is works very good. So we should to use two checking steps: IS_FREE_LOCK as first and then GET_LOCK($lockId, 0) === 1.

First, we need to create code like this (its not ideal, and hasnt error handling: its a just proof-o-concept):

<?php
$db = new mysqli('localhost', 'root', '');
 
if ($db->connect_errno) {
    printf("Не удалось подключиться: %s\n", $db->connect_error);
    exit();
}
 
function get_named_lock($lockname, $i) {
  global $db;
  $rs = $db->query("SELECT IS_FREE_LOCK('$lockname' AS isfree");
  $result = $rs->fetch_array();
 
  if ($result['isfree']) {
    printf("Is free! PID = %s I = %d\n", getmypid(), $i);
    $rs = $db->query("SELECT GET_LOCK('$lockname', 0) AS locked");
    $result = $rs->fetch_array();
    $locked = $result['locked'];
    printf("%s Locked! PID = %s I = %d\n", $locked ? 'IS' : 'NOT', getmypid(), $i);
    return $locked;
  } else {
    return false;
  }
}
 
function release_named_lock($lockname) {
  $db->query("DO RELEASE_LOCK('$lockname'");
}
 
printf("Started %s\n", getmypid());
for ($i = 0; $i < 10000; $i++) {
    get_named_lock('mylock', $i);
    usleep(10);
}
printf("Release %s\n", getmypid());

Lets save it in file lock_test.php and then write small Bash sсript (run.sh):

#!/bin/bash
for i in {1..10}
do
 `php lock_test.php >> /tmp/out.log` &
done

Then you can to run it and see into logs:

$ ./run.sh
$ tail -f /tmp/out.log
Started 29376
Is free! PID = 29376 I = 0
IS Locked! PID = 29376 I = 0
Started 29369
Started 29377
Started 29368
Started 29370
Started 29373
Started 29374
Started 29372
Started 29378
Started 29379
Release 29373
Release 29370
Release 29376
Is free! PID = 29378 I = 9844
Is free! PID = 29369 I = 9964
Is free! PID = 29374 I = 9989
IS Locked! PID = 29369 I = 9964
Is free! PID = 29377 I = 9852
NOT Locked! PID = 29374 I = 9989
NOT Locked! PID = 29378 I = 9844
NOT Locked! PID = 29377 I = 9852
Release 29374
Release 29369
Release 29372
Is free! PID = 29377 I = 9876
Is free! PID = 29368 I = 9881
Is free! PID = 29379 I = 9906
Is free! PID = 29378 I = 9876
IS Locked! PID = 29379 I = 9906
NOT Locked! PID = 29378 I = 9876
NOT Locked! PID = 29368 I = 9881
NOT Locked! PID = 29377 I = 9876
Release 29379
Is free! PID = 29378 I = 9973
IS Locked! PID = 29378 I = 9973
Release 29368
Release 29378
Release 29377



As you can see, lot of sсripts can to catch lock as "free" at same time, but only one can actually catch it with GET_LOCK. Anyway, a lot of iterations are skipped by IS_FREE_LOCK check step - its fast and enough to skip non-concurrency scripts.

@темы: Bash, MySQL, PHP, concurrency, GET_LOCK, mutex