pf_ps_mysql_lock_test

// mysql_lock_test
// creates locks in a table to prevent concurrency
// locks are based on item_id

/*
 -- assume mysql table with itemId (bigint), lockTime (dateTime), lockId (varchar 32)
 -- [with indexes on all columns]:
CREATE TABLE `locks` (
  `itemId` bigint(20) unsigned NOT NULL,
  `lockId` varchar(32) NOT NULL,
  `lockTime` datetime NOT NULL,
  KEY `itemId` (`itemId`),
  KEY `lockId` (`lockId`),
  KEY `locakTime` (`lockTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
*/

global $lockTable
$lockTable = "locks"
global $sqlConn
$sqlConn = "do-pf-test-db"
global $lockId
$lockId = uniqid()

// start of real code

// get current item id from POST etc
$current_item = 1234

get_lock($current_item)
// do real work here
// sleep(10)
// ...
release_lock($current_item)
yield "OK"

function get_lock($current_item) {
    $old = date("Y-m-d H:i:s", strtotime("-60 seconds"))
    mysql_command("DELETE FROM "+$lockTable+" WHERE lockTime<?", [$old], $sqlConn)
    $sql = "INSERT INTO "+$lockTable+" (itemId, lockTime, lockId) VALUES (?, ?, ?) "
    $sql = $sql + " ON DUPLICATE KEY UPDATE lockId=lockId"
    $now = date("Y-m-d H:i:s")
    mysql_command($sql, [$current_item, $now, $lockId], $sqlConn)
    $locks = mysql_array("SELECT * FROM "+$lockTable+" WHERE itemId=? AND lockId=?", [$current_item, $lockId], $sqlConn)
    $safety = 0;
    while ( sizeof($locks) == 0 ) {
        $safety = $safety + 1
        if ( $safety > 1000 ) {
            throw("maximum safety threshold exceeded")
        }
        sleep(1)
        ini_set("max_execution_time", 30)
        mysql_command($sql, [$current_item, $now, $lockId], $sqlConn)
        $locks = mysql_array("SELECT * FROM "+$lockTable+" WHERE itemId=? AND lockId=?", [$current_item, $lockId], $sqlConn)
    }
}

function release_lock($current_item) {
    mysql_command("DELETE FROM "+$lockTable+" WHERE itemId=?", [$current_item], $sqlConn)
}