Using Moodle 2.4.2+ (Build: 20130315) I first attempted to run a full import of a course containing 15 topics with a mixture of 36 single PDF files, single HTML files, URL links, SCORM packages and file/folder collections which comprise single FLASH based resources. This failed with the following error;

Error writing to database

More information about this error

Debug info: SQLState: 40001<br>
Error Code: 1205<br>
Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Transaction (Process ID 54) was deadlocked on lock | generic waitable object resources with another process and has been chosen as the deadlock victim. Rerun the transaction.<br>

UPDATE #mdl_backup_ids_temp SET itemname = N'filefinal' WHERE id = '355'
[array (
0 => 'filefinal',
1 => '355',
)]
Error code: dmlwriteexception

Stack trace: •line 429 of \lib\dml\moodle_database.php: dml_write_exception thrown
•line 260 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
•line 367 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
•line 1131 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
•line 1565 of \lib\dml\moodle_database.php: call to sqlsrv_native_moodle_database->set_field_select()
•line 142 of \backup\util\dbops\backup_structure_dbops.class.php: call to moodle_database->set_field()
•line 1437 of \backup\moodle2\backup_stepslib.php: call to backup_structure_dbops::move_annotations_to_final()
•line 34 of \backup\util\plan\backup_execution_step.class.php: call to move_inforef_annotations_to_final->define_execution()
•line 153 of \backup\util\plan\base_task.class.php: call to backup_execution_step->execute()
•line 211 of \backup\moodle2\backup_activity_task.class.php: call to base_task->execute()
•line 163 of \backup\util\plan\base_plan.class.php: call to backup_activity_task->execute()
•line 110 of \backup\util\plan\backup_plan.class.php: call to base_plan->execute()
•line 309 of \backup\controller\backup_controller.class.php: call to backup_plan->execute()
•line 111 of \backup\util\ui\backup_ui.class.php: call to backup_controller->execute_plan()
•line 94 of \backup\import.php: call to backup_ui->execute()

Instead of performing any additional troubleshooting on this version of Moodle I upgraded to the latest version of Moodle: 2.5+ (Build 20130606)

I attempted the same import above which now fails after some time with a general fastCGI timeout error. I doubled the fastCGI activityTimeout and requestTimeout from the default 10 minutes to 20 minutes which did not solve the issue.

After many hours stepping through the app, it is the following SQL command which fails (The backup phase completes fine. Is is the restore phase which fails);

\backup\util\dbops\restore_dbops.class.php

LINE 944

//Excecutes SQL UPDATE command below which hangs the system resulting with fastCGI timeout

"UPDATE #mdl_backup_files_temp SET newcontextid = '1432',newitemid = '0' WHERE id = '1'"

Note: The backup log stops with the entry: [debug] setting controller status to 800. Later after successfully completing an import I checked those logs to find the next entry should have been: [info] processing file aliases queue

I then attempted the import again, but excluded the SCORM packages and sets of file/folder collections which comprised single FLASH based resources. The import succeeded! So it appears there may be a connection with resources which are made up of several files and therefore representing a one to many relationship within the database? This may be handled by the app updating the parent record with its children in the #mdl_backup_files_temp temporary table?

However it is not clear why the #mdl_backup_files_temp is causing an issue. Could it be its scope? The necessary # prefix for MSSQL to recognize a Local temp table with user connection scope is added in file \lib\dml\mssql_native_moodle_temptables.php LINE 49. I tried adding a second # prefix to define a Global Temp Table ## which is then available to any user by any connection. However this fails later in the app as after the table is created it fails to be populated. Perhaps another option is to try a table variable instead although this would take more work to implement. Also I can rule out the MS SQL Server parallelism bug as this edition is limited to only 1 CPU core.

After reading MDL-34744 I changed the following line so the app no longer creates a temp table. It creates a permanent table and then later deletes it.

\backup\util\dbops\backup_controller_dbops.class.php

LINE 159

//LFW Hack to troubleshoot deadlock import course issue
$dbman->create_table($xmldb_table); // And create it

The Import completes successfully! The hack may cause other issues later. Can anyone help? Why is #mdl_backup_files_temp causing an issue?

Other Links with a similar issue:

https://tracker.moodle.org/browse/MDL-34744
https://tracker.moodle.org/browse/MDL-34854
https://moodle.org/mod/forum/discuss.php?d=177822
https://moodle.org/mod/forum/discuss.php?d=208642