Repeated error DeadlockException and very slow sync

Nextcloud version: 19.0.3 (snap)
Operating system and version: Ubuntu 18.04 (snap)
Apache or nginx version: Apache 2.4.46
PHP version: 7.3.21

The issue you are facing:
Repeated fatal errors in log when syncing large changes with many small files. Sync when moving a 1.3Gb folder with many small files takes more than a day. Nextcloud client on syncing desktop sometimes hangs. Restarting the server helps for a while.

I am in doubt whether to report this to nextcloud or the nextcloud ubuntu snap community. Decided to start at nextcloud.

This concerns a home server on commodity hardware with very little load.

Any help or pointers to relevant documentation are greatly appreciated.

Is this the first time you’ve seen this error? (Y/N): Y

Steps to replicate it:

  1. Setup Nextcloud on Ubuntu snap
  2. Sync with two desktop clients on Ubuntu 20.04 desktop
  3. On one desktop, move a folder with many small files and subfolders
  4. The sync will proceed exceedingly slow with many fatal errors in nextcloud log (see below)

The output of your Nextcloud log in Admin > Logging:
Example of first error that repeatedly happens:

[webdav] Fatal: Doctrine\DBAL\Exception\DeadlockException: An exception occurred while executing 'UPDATE `oc_filecache` SET `size` = ? WHERE (`fileid` = ?) AND ((`size` <> ?) OR (`size` IS NULL))' with params [56303297717, 170, 56303297717]:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction at <<closure>>

 0. /snap/nextcloud/23171/htdocs/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 169
    Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException("An exception oc ... n", Doctrine\DBAL\Dr ... ]})
 1. /snap/nextcloud/23171/htdocs/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 145
    Doctrine\DBAL\DBALException::wrapException(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "An exception oc ... n")
 2. /snap/nextcloud/23171/htdocs/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 1063
    Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "UPDATE `oc_file ... )", {1: 56303297717, ... 7})
 3. /snap/nextcloud/23171/htdocs/lib/private/DB/Connection.php line 214
    Doctrine\DBAL\Connection->executeUpdate("UPDATE `oc_file ... )", [56303297717,"** ... 7], [2,1,2])
 4. /snap/nextcloud/23171/htdocs/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php line 203
    OC\DB\Connection->executeUpdate("UPDATE `oc_file ... )", {dcValue1: "***  ... 7}, {dcValue1: 1,dcValue2: 2,dcValue3: 2})
 5. /snap/nextcloud/23171/htdocs/lib/private/DB/QueryBuilder/QueryBuilder.php line 216
    Doctrine\DBAL\Query\QueryBuilder->execute()
 6. /snap/nextcloud/23171/htdocs/lib/private/Files/Cache/Cache.php line 363
    OC\DB\QueryBuilder\QueryBuilder->execute()
 7. /snap/nextcloud/23171/htdocs/lib/private/Files/Cache/Cache.php line 898
    OC\Files\Cache\Cache->update("*** sensitive parameters replaced ***")
 8. /snap/nextcloud/23171/htdocs/lib/private/Files/Cache/HomeCache.php line 43
    OC\Files\Cache\Cache->calculateFolderSize("files_trashbin/files", OC\Files\Cache\CacheEntry {})
 9. /snap/nextcloud/23171/htdocs/lib/private/Files/Cache/Cache.php line 830
    OC\Files\Cache\HomeCache->calculateFolderSize("files_trashbin/files", null)
10. /snap/nextcloud/23171/htdocs/lib/private/Files/Cache/Cache.php line 842
    OC\Files\Cache\Cache->correctFolderSize("files_trashbin/files")
11. /snap/nextcloud/23171/htdocs/lib/private/Files/Cache/Updater.php line 220
    OC\Files\Cache\Cache->correctFolderSize("files_trashbin/ ... 0")
12. /snap/nextcloud/23171/htdocs/apps/files_trashbin/lib/Trashbin.php line 289
    OC\Files\Cache\Updater->renameFromStorage(OCA\Files_Trashb ... }}, "files/sec-sync/ ... E", "files_trashbin/ ... 0")
13. /snap/nextcloud/23171/htdocs/apps/files_trashbin/lib/Trash/LegacyTrashBackend.php line 108
    OCA\Files_Trashbin\Trashbin::move2trash("sec-sync/249/2A ... E")
14. /snap/nextcloud/23171/htdocs/apps/files_trashbin/lib/Trash/TrashManager.php line 103
    OCA\Files_Trashbin\Trash\LegacyTrashBackend->moveToTrash(OCA\Files_Trashb ... }}, "files/sec-sync/ ... E")
15. /snap/nextcloud/23171/htdocs/apps/files_trashbin/lib/Storage.php line 192
    OCA\Files_Trashbin\Trash\TrashManager->moveToTrash(OCA\Files_Trashb ... }}, "files/sec-sync/ ... E")
16. /snap/nextcloud/23171/htdocs/apps/files_trashbin/lib/Storage.php line 99
    OCA\Files_Trashbin\Storage->doDelete("files/sec-sync/ ... E", "unlink")
17. /snap/nextcloud/23171/htdocs/lib/private/Files/View.php line 1161
    OCA\Files_Trashbin\Storage->unlink("files/sec-sync/ ... E")
18. /snap/nextcloud/23171/htdocs/lib/private/Files/View.php line 718
    OC\Files\View->basicOperation("unlink", "/sec-sync/249/2 ... E", ["delete"])
19. /snap/nextcloud/23171/htdocs/apps/dav/lib/Connector/Sabre/File.php line 458
    OC\Files\View->unlink("/sec-sync/249/2 ... E")
20. /snap/nextcloud/23171/htdocs/3rdparty/sabre/dav/lib/DAV/Tree.php line 183
    OCA\DAV\Connector\Sabre\File->delete()
21. /snap/nextcloud/23171/htdocs/3rdparty/sabre/dav/lib/DAV/CorePlugin.php line 295
    Sabre\DAV\Tree->delete("files/jeroen/se ... E")
22. /snap/nextcloud/23171/htdocs/3rdparty/sabre/event/lib/WildcardEmitterTrait.php line 89
    Sabre\DAV\CorePlugin->httpDelete(Sabre\HTTP\Request {}, Sabre\HTTP\Response {})
23. /snap/nextcloud/23171/htdocs/3rdparty/sabre/dav/lib/DAV/Server.php line 474
    Sabre\DAV\Server->emit("method:DELETE", [Sabre\HTTP\Requ ... }])
24. /snap/nextcloud/23171/htdocs/3rdparty/sabre/dav/lib/DAV/Server.php line 251
    Sabre\DAV\Server->invokeMethod(Sabre\HTTP\Request {}, Sabre\HTTP\Response {})
25. /snap/nextcloud/23171/htdocs/3rdparty/sabre/dav/lib/DAV/Server.php line 319
    Sabre\DAV\Server->start()
26. /snap/nextcloud/23171/htdocs/apps/dav/lib/Server.php line 320
    Sabre\DAV\Server->exec()
27. /snap/nextcloud/23171/htdocs/apps/dav/appinfo/v2/remote.php line 35
    OCA\DAV\Server->exec()
28. /snap/nextcloud/23171/htdocs/remote.php line 167
    require_once("/snap/nextcloud ... p")

DELETE /remote.php/dav/files/FILENAME
from IPADDRESS by USER at 2020-10-01T09:25:41+00:00

Another error that intermittently pops up and may be related is:

[PHP] Error: Cannot modify header information - headers already sent by (output started at /snap/nextcloud/23171/htdocs/3rdparty/sabre/http/lib/Sapi.php:132) at /snap/nextcloud/23171/htdocs/apps/dav/lib/Connector/Sabre/File.php#683

PUT /remote.php/dav/files/FILEPATH/FILENAME
from IPADDRESS by USER at 2020-10-01T07:09:41+00:00

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

<?php
$CONFIG = array (
  'apps_paths' => 
  array (
    0 => 
    array (
      'path' => '/snap/nextcloud/current/htdocs/apps',
      'url' => '/apps',
      'writable' => false,
    ),
    1 => 
    array (
      'path' => '/var/snap/nextcloud/current/nextcloud/extra-apps',
      'url' => '/extra-apps',
      'writable' => true,
    ),
  ),
  'supportedDatabases' => 
  array (
    0 => 'mysql',
  ),
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'memcache.local' => '\\OC\\Memcache\\Redis',
  'redis' => 
  array (
    'host' => '/tmp/sockets/redis.sock',
    'port' => 0,
  ),
  'passwordsalt' => '<SALT>',
  'secret' => '<SECRET>',
  'trusted_domains' => 
  array (
    0 => 'localhost',
    1 => '<FQDN>',
  ),
  'datadirectory' => '/var/snap/nextcloud/common/nextcloud/data',
  'dbtype' => 'mysql',
  'version' => '19.0.3.1',
  'overwrite.cli.url' => 'http://localhost',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost:/tmp/sockets/mysql.sock',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => '<DBUSER>',
  'dbpassword' => '<DBPASS>',
  'installed' => true,
  'instanceid' => '<INSTANCEID>',
  'maintenance' => false,
  'loglevel' => 2,
);

The output of your Apache/nginx/system log in /var/log/____:

[Thu Oct 01 09:09:45.516812 2020] [mpm_event:notice] [pid 3098:tid 140510931374080] AH00491: caught SIGTERM, shutting down
[Thu Oct 01 09:10:41.895983 2020] [mpm_event:notice] [pid 2614:tid 140095800209408] AH00489: Apache/2.4.46 (Unix) OpenSSL/1.1.1 configured -- resuming normal operations
[Thu Oct 01 09:10:41.896043 2020] [core:notice] [pid 2614:tid 140095800209408] AH00094: Command line: 'httpd -d /snap/nextcloud/23171 -D EnableHTTPS -D EnableHSTS -D FOREGROUND'
[Thu Oct 01 09:10:41.898921 2020] [unixd:alert] [pid 2617:tid 140095800209408] AH02155: getpwuid: couldn't determine user name from uid 4294967295, you probably need to modify the User directive
[Thu Oct 01 09:10:41.898931 2020] [unixd:alert] [pid 2618:tid 140095800209408] AH02155: getpwuid: couldn't determine user name from uid 4294967295, you probably need to modify the User directive
[Thu Oct 01 09:10:41.898938 2020] [unixd:alert] [pid 2616:tid 140095800209408] AH02155: getpwuid: couldn't determine user name from uid 4294967295, you probably need to modify the User directive
[Thu Oct 01 09:10:58.913239 2020] [unixd:alert] [pid 3610:tid 140095800209408] AH02155: getpwuid: couldn't determine user name from uid 4294967295, you probably need to modify the User directive

I’ve noticed the issues seem to correlate with adding files to a CryFS encrypted folder. This causes many (tens of thousands, sometimes hundreds of thousands) of small files to require synchronization.

Could be a performance issue after all?