Uploaded image for project: 'translate5'
  1. translate5
  2. TRANSLATE-351

optimize table locking of segment meta table in termtagger import

XMLWordPrintable

    • Icon: Task Task
    • Resolution: Unresolved
    • None
    • None
    • None

      Currently the TermTaggerImport locks a bunch of tables to get the next segments which are to be tagged.
      This could be improved by doing the following steps:

      1. Instead of creating the segment meta entries only for the selected X segments we could create all segment meta entries for all segments of the Task in a first step (insert into LEK_segment_meta values select segmentId, taskGuid from LEK_segments where taskGuid = ?; Add a duplicate key update (or better ignore if possible) statement to the insert).
      2. refactor the getNextSegmentSelect so that it does a "select for update" call which hopefully locks the affected rows then.
      3. refactor the later foreach meta->save() to one update statement which sets the state for all segmentIds in (the above fetched ones)

          [TRANSLATE-351] optimize table locking of segment meta table in termtagger import

          Marc Mittag [Administrator] added a comment -

          one time we had an error so far, which is connected to this issue. Happend at big data import at beo.

          Portal_Log on portal: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: UPDATE `Zf_worker` SET `hash` = ? WHERE (id = "8568") AND (hash = "549174f2772ea1.25710073") AND (state = "waiting")



#0 /usr/share/php/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/php/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#2 /usr/share/php/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `Zf_work...', Array)
#3 /usr/share/php/Zend/Db/Adapter/Abstract.php(635): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `Zf_work...', Array)
#4 /usr/share/php/Zend/Db/Table/Abstract.php(1133): Zend_Db_Adapter_Abstract->update('Zf_worker', Array, Array)
#5 /var/www/html/debug-portal-3.0.34/library/ZfExtended/Models/Worker.php(162): Zend_Db_Table_Abstract->update(Array, Array)
#6 /var/www/html/debug-portal-3.0.34/library/ZfExtended/Worker/Abstract.php(245): ZfExtended_Models_Worker->setRunningMutex()
#7 /var/www/html/debug-portal-3.0.34/library/ZfExtended/Controllers/WorkerController.php(101): ZfExtended_Worker_Abstract->runQueued()
#8 /usr/share/php/Zend/Controller/Action.php(516): ZfExtended_WorkerController->putAction()
#9 /var/www/html/debug-portal-3.0.34/library/ZfExtended/Controllers/RestController.php(218): Zend_Controller_Action->dispatch('putAction')
#10 /usr/share/php/Zend/Controller/Dispatcher/Standard.php(308): ZfExtended_RestController->dispatch('putAction')
#11 /usr/share/php/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(REST_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#12 /usr/share/php/Zend/Application/Bootstrap/Bootstrap.php(101): Zend_Controller_Front->dispatch()
#13 /usr/share/php/Zend/Application.php(366): Zend_Application_Bootstrap_Bootstrap->run()
#14 /var/www/html/debug-portal-3.0.34/library/ZfExtended/BaseIndex.php(167): Zend_Application->run()
#15 /var/www/html/debug-portal-3.0.34/public/index.php(11): ZfExtended_BaseIndex->startApplication()
#16 {main}



Eingeloggter User:

object(ArrayObject)#449 (1) {
  ["storage":"ArrayObject":private]=>
  array(2) {
    ["data"]=>
    object(stdClass)#390 (1) {
      ["roles"]=>
      array(1) {
        [0]=>
        string(8) "noRights"
      }
    }
    ["roles"]=>
    array(1) {
      [0]=>
      string(8) "noRights"
    }
  }
}
Aufgerufene URL inkl. ggf. vorhandener POST-Parameter als get-Parameter: 

/debug-portal/public/editor/worker/put/cleanupSessionAfterRun/1/id/8568/format/json/data/{"state":"running","hash":"549174f2772ea1.25710073"}

          And this is the following error of the fsockopen:

          Portal_Log on portal: ZfExtended_Worker_TriggerByHttp->triggerUrl; Can not trigger url:  http://portal/debug-portal/public/editor/worker/8568



Method: PUT; Post-Parameter: Array
(
    [state] => running
    [hash] => 549174f2772ea1.25710073
)
Aufgerufene URL inkl. ggf. vorhandener POST-Parameter als get-Parameter: 

/debug-portal/public/editor/worker/queue/format/json/cleanupSessionAfterRun/1

          Marc Mittag [Administrator] added a comment - one time we had an error so far, which is connected to this issue. Happend at big data import at beo. Portal_Log on portal: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: UPDATE `Zf_worker` SET `hash` = ? WHERE (id = "8568" ) AND (hash = "549174f2772ea1.25710073" ) AND (state = "waiting" ) #0 /usr/share/php/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array) #1 /usr/share/php/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array) #2 /usr/share/php/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query( 'UPDATE `Zf_work...' , Array) #3 /usr/share/php/Zend/Db/Adapter/Abstract.php(635): Zend_Db_Adapter_Pdo_Abstract->query( 'UPDATE `Zf_work...' , Array) #4 /usr/share/php/Zend/Db/Table/Abstract.php(1133): Zend_Db_Adapter_Abstract->update( 'Zf_worker' , Array, Array) #5 / var /www/html/debug-portal-3.0.34/library/ZfExtended/Models/Worker.php(162): Zend_Db_Table_Abstract->update(Array, Array) #6 / var /www/html/debug-portal-3.0.34/library/ZfExtended/Worker/Abstract.php(245): ZfExtended_Models_Worker->setRunningMutex() #7 / var /www/html/debug-portal-3.0.34/library/ZfExtended/Controllers/WorkerController.php(101): ZfExtended_Worker_Abstract->runQueued() #8 /usr/share/php/Zend/Controller/Action.php(516): ZfExtended_WorkerController->putAction() #9 / var /www/html/debug-portal-3.0.34/library/ZfExtended/Controllers/RestController.php(218): Zend_Controller_Action->dispatch( 'putAction' ) #10 /usr/share/php/Zend/Controller/Dispatcher/Standard.php(308): ZfExtended_RestController->dispatch( 'putAction' ) #11 /usr/share/php/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch( Object (REST_Controller_Request_Http), Object (Zend_Controller_Response_Http)) #12 /usr/share/php/Zend/Application/Bootstrap/Bootstrap.php(101): Zend_Controller_Front->dispatch() #13 /usr/share/php/Zend/Application.php(366): Zend_Application_Bootstrap_Bootstrap->run() #14 / var /www/html/debug-portal-3.0.34/library/ZfExtended/BaseIndex.php(167): Zend_Application->run() #15 / var /www/html/debug-portal-3.0.34/ public /index.php(11): ZfExtended_BaseIndex->startApplication() #16 {main} Eingeloggter User: object(ArrayObject)#449 (1) { [ "storage" : "ArrayObject" : private ]=> array(2) { [ "data" ]=> object(stdClass)#390 (1) { [ "roles" ]=> array(1) { [0]=> string(8) "noRights" } } [ "roles" ]=> array(1) { [0]=> string(8) "noRights" } } } Aufgerufene URL inkl. ggf. vorhandener POST-Parameter als get-Parameter: /debug-portal/ public /editor/worker/put/cleanupSessionAfterRun/1/id/8568/format/json/data/{ "state" : "running" , "hash" : "549174f2772ea1.25710073" } And this is the following error of the fsockopen: Portal_Log on portal: ZfExtended_Worker_TriggerByHttp->triggerUrl; Can not trigger url: http: //portal/debug-portal/ public /editor/worker/8568 Method: PUT; Post-Parameter: Array ( [state] => running [hash] => 549174f2772ea1.25710073 ) Aufgerufene URL inkl. ggf. vorhandener POST-Parameter als get-Parameter: /debug-portal/ public /editor/worker/queue/format/json/cleanupSessionAfterRun/1

          Marc Mittag [Administrator] added a comment -

          don't know, if the suggested solution will work / is sufficient, because every worker tries to get the next 50 rows which are not tagged. If a worker runs exactly at the moment, when these 50 rows are locked for the update, it should select the locked rows - but can't. What will it do? Wait until the rows are not locked any more? Or select the next 50 rows? It may work, but it has to be tested very well.

          Marc Mittag [Administrator] added a comment - don't know, if the suggested solution will work / is sufficient, because every worker tries to get the next 50 rows which are not tagged. If a worker runs exactly at the moment, when these 50 rows are locked for the update, it should select the locked rows - but can't. What will it do? Wait until the rows are not locked any more? Or select the next 50 rows? It may work, but it has to be tested very well.

            Unassigned Unassigned
            tlauria Thomas Lauria
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated: