Recently we attempted to upgrade our custom SQL*Server docker image to the recently available 2019 edition.

Once checked that it was working ok and passing own tests we proposed the upgrade via PR: https://github.com/moodlehq/moodle-db-mssql/pull/11

Then, discovered that it was leading to a blocking in some unit tests. More exactly with [ test_restore_of_deadlock_causing_backup|https://github.com/moodle/moodle/blob/master/backup/controller/tests/controller_test.php#L156].

To reproduce the problem, just run against SQL*Server 2019 this unit test:

--testsuite core_backup_testsuite --filter test_restore_of_deadlock_causing_backup

That's related with temporal tables and how SQL*Server manages them, in core we did workaround that problem long ago with MDL-57698, adding the WITH (NOLOCK) hint exactly to prevent those problems happening on dbtemp (the storage where temp tables are created, with different transaction rules than normal tables). In fact, the test above was created (yay!) exactly to check that the locking was not a problem anymore.

But it seems that the problem is back.

This issue is about to see what has changed (we are aware of some changes to temporal tables coming in 2019) and how we can solve it. First in our docker images and then, in general, or people may face problems running Moodle with SQL*Server 2019.

Let's see what's discovered and which are the implicacions...

Ciao