-
Bug
-
Resolution: Fixed
-
Major
-
3.1.4, 3.2.4, 3.3
-
Database servers:
* Microsoft SQL Server 2008 R2
* Microsoft SQL Server 2016
Database driver:
* sqlsrv
No other details applicable.
-
Microsoft SQL
-
MOODLE_31_STABLE, MOODLE_32_STABLE, MOODLE_33_STABLE
-
MOODLE_32_STABLE, MOODLE_33_STABLE
-
MDL-57698-master -
-
-
3.4 Sprint 2, 3.4 Sprint 3
During backup and restore operations, the connection to SQL Server can end up in a deadlocked state. This issue appears to affect only the sqlsrv driver, regardless of host operating system. I assume that a workaround must be applied in FreeTDS for mssql.
This deadlock appears to occur because of SELECT...UPDATE loops such as the following in \backup_structure_dbops::move_annotations_to_final():
$rs = $DB->get_recordset('backup_ids_temp', array('backupid' => $backupid, 'itemname' => $itemname));
|
foreach ($rs as $annotation) {
|
$DB->set_field('backup_ids_temp', 'itemname', $itemname . 'final', array('id' => $annotation->id));
|
}
|
Executing the above will, in some cases, cause two competing locks to be obtained on the #backup_ids_temp table:
- A SELECT, waiting for ASYNC_NETWORK_IO.
- An UPDATE, waiting for an intent lock (LCK_M_IX).
Reviewing the process list in the SSMS Activity Monitor will show two SUSPENDED processes, and the profiler will show the SELECT and UPDATE queries as the last two to have started (BatchStarted event), with no corresponding complete events.
In the end, I settled on adding an optional $hints parameter to the get_recordset() and get_recordset_select() methods on \moodle_database which accepts a bitmask of HINT_* values (just HINT_NO_LOCK for now). I then added an empty get_hints() implementation to the class and added an implementation in \sqlsrv_native_moodle_database.
For reference, I've explained how I came to this approach.
I first attempted to solve this problem by switching from the READ_COMMITTED isolation level to SNAPSHOT. This appeared to resolve the problem affecting the backup process, but I was unable to get this to behave correctly in the PHPUnit environment without calling \advanced_testcase::preventResetByRollback(). Upon investigation, I realised that SQL Server's SNAPSHOT isolation mode prevents certain DDL changes within a transaction, yielding error 3964 when applying CREATE INDEX statements on temporary tables.
I then modified the specific case preventing our backup from completing (user creation) to include the WITH (NOLOCK) clause and saw that the backup completed successfully. Tests invoking the backup and restore process also completed successfully.