globiws

pf_ps_mysql_lock_test

Dec 5th, 2020 (edited)
890
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. // mysql_lock_test
  2. // creates locks in a table to prevent concurrency
  3. // locks are based on item_id
  4.  
  5. /*
  6.  -- assume mysql table with itemId (bigint), lockTime (dateTime), lockId (varchar 32)
  7.  -- [with indexes on all columns]:
  8. CREATE TABLE `locks` (
  9.   `itemId` bigint(20) unsigned NOT NULL,
  10.   `lockId` varchar(32) NOT NULL,
  11.   `lockTime` datetime NOT NULL,
  12.   KEY `itemId` (`itemId`),
  13.   KEY `lockId` (`lockId`),
  14.   KEY `locakTime` (`lockTime`)
  15. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  16. */
  17.  
  18. global $lockTable
  19. $lockTable = "locks"
  20. global $sqlConn
  21. $sqlConn = "do-pf-test-db"
  22. global $lockId
  23. $lockId = uniqid()
  24.  
  25. // start of real code
  26.  
  27. // get current item id from POST etc
  28. $current_item = 1234
  29.  
  30. get_lock($current_item)
  31. // do real work here
  32. // sleep(10)
  33. // ...
  34. release_lock($current_item)
  35. yield "OK"
  36.  
  37. function get_lock($current_item) {
  38.     $old = date("Y-m-d H:i:s", strtotime("-60 seconds"))
  39.     mysql_command("DELETE FROM "+$lockTable+" WHERE lockTime<?", [$old], $sqlConn)
  40.     $sql = "INSERT INTO "+$lockTable+" (itemId, lockTime, lockId) VALUES (?, ?, ?) "
  41.     $sql = $sql + " ON DUPLICATE KEY UPDATE lockId=lockId"
  42.     $now = date("Y-m-d H:i:s")
  43.     mysql_command($sql, [$current_item, $now, $lockId], $sqlConn)
  44.     $locks = mysql_array("SELECT * FROM "+$lockTable+" WHERE itemId=? AND lockId=?", [$current_item, $lockId], $sqlConn)
  45.     $safety = 0;
  46.     while ( sizeof($locks) == 0 ) {
  47.         $safety = $safety + 1
  48.         if ( $safety > 1000 ) {
  49.             throw("maximum safety threshold exceeded")
  50.         }
  51.         sleep(1)
  52.         ini_set("max_execution_time", 30)
  53.         mysql_command($sql, [$current_item, $now, $lockId], $sqlConn)
  54.         $locks = mysql_array("SELECT * FROM "+$lockTable+" WHERE itemId=? AND lockId=?", [$current_item, $lockId], $sqlConn)
  55.     }
  56. }
  57.  
  58. function release_lock($current_item) {
  59.     mysql_command("DELETE FROM "+$lockTable+" WHERE itemId=?", [$current_item], $sqlConn)
  60. }
  61.  
RAW Paste Data