Bulk Upload - Deadlock found when trying to get lock #29985

apg1980 · 2021-11-30T14:39:12Z

Mariadb 10.5.13
Php 8.0.13
Apache
ubuntu 20.04 lts

{
  "reqId": "57SS2UgAK0IhKBiUN97i",
  "level": 3,
  "time": "2021-11-30T14:44:25+01:00",
  "remoteAddr": "------",
  "user": "---",
  "app": "no app in context",
  "method": "POST",
  "url": "/remote.php/dav/bulk",
  "message": "An exception occurred while executing a query: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction",
  "userAgent": "Mozilla/5.0 (Windows) mirall/3.4.0stable-Win64 (build 20211129) (Nextcloud, windows-10.0.22000 ClientArchitecture: x86_64 OsArchitecture: x86_64)",
  "version": "23.0.0.10",
  "exception": {
    "Exception": "Doctrine\\DBAL\\Exception\\DeadlockException",
    "Message": "An exception occurred while executing a query: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction",
    "Code": 1213,
    "Trace": [
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
        "line": 1780,
        "function": "convert",
        "class": "Doctrine\\DBAL\\Driver\\API\\MySQL\\ExceptionConverter",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
        "line": 1719,
        "function": "handleDriverException",
        "class": "Doctrine\\DBAL\\Connection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
        "line": 1173,
        "function": "convertExceptionDuringQuery",
        "class": "Doctrine\\DBAL\\Connection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/DB/Connection.php",
        "line": 267,
        "function": "executeStatement",
        "class": "Doctrine\\DBAL\\Connection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
        "line": 354,
        "function": "executeStatement",
        "class": "OC\\DB\\Connection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
        "line": 287,
        "function": "execute",
        "class": "Doctrine\\DBAL\\Query\\QueryBuilder",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/Cache/Cache.php",
        "line": 379,
        "function": "execute",
        "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/Cache/Wrapper/CacheWrapper.php",
        "line": 145,
        "function": "update",
        "class": "OC\\Files\\Cache\\Cache",
        "type": "->",
        "args": [
          "*** sensitive parameters replaced ***"
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/Cache/Wrapper/CacheWrapper.php",
        "line": 145,
        "function": "update",
        "class": "OC\\Files\\Cache\\Wrapper\\CacheWrapper",
        "type": "->",
        "args": [
          "*** sensitive parameters replaced ***"
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/Cache/Updater.php",
        "line": 256,
        "function": "update",
        "class": "OC\\Files\\Cache\\Wrapper\\CacheWrapper",
        "type": "->",
        "args": [
          "*** sensitive parameters replaced ***"
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/Cache/Updater.php",
        "line": 138,
        "function": "correctParentStorageMtime",
        "class": "OC\\Files\\Cache\\Updater",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/View.php",
        "line": 323,
        "function": "update",
        "class": "OC\\Files\\Cache\\Updater",
        "type": "->",
        "args": [
          "*** sensitive parameters replaced ***"
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/View.php",
        "line": 943,
        "function": "writeUpdate",
        "class": "OC\\Files\\View",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/files_versions/lib/Versions/LegacyVersionsBackend.php",
        "line": 93,
        "function": "copy",
        "class": "OC\\Files\\View",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/files_versions/lib/Versions/VersionManager.php",
        "line": 92,
        "function": "createVersion",
        "class": "OCA\\Files_Versions\\Versions\\LegacyVersionsBackend",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/files_versions/lib/Storage.php",
        "line": 217,
        "function": "createVersion",
        "class": "OCA\\Files_Versions\\Versions\\VersionManager",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/files_versions/lib/Hooks.php",
        "line": 56,
        "function": "store",
        "class": "OCA\\Files_Versions\\Storage",
        "type": "::"
      },
      {
        "file": "/var/www/nextcloud/lib/private/legacy/OC_Hook.php",
        "line": 106,
        "function": "write_hook",
        "class": "OCA\\Files_Versions\\Hooks",
        "type": "::"
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/View.php",
        "line": 1284,
        "function": "emit",
        "class": "OC_Hook",
        "type": "::"
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/View.php",
        "line": 1154,
        "function": "runHooks",
        "class": "OC\\Files\\View",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/View.php",
        "line": 706,
        "function": "basicOperation",
        "class": "OC\\Files\\View",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/Files/Node/Folder.php",
        "line": 198,
        "function": "file_put_contents",
        "class": "OC\\Files\\View",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/lib/BulkUpload/BulkUploadPlugin.php",
        "line": 81,
        "function": "newFile",
        "class": "OC\\Files\\Node\\Folder",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
        "line": 89,
        "function": "httpPost",
        "class": "OCA\\DAV\\BulkUpload\\BulkUploadPlugin",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 472,
        "function": "emit",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 253,
        "function": "invokeMethod",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 321,
        "function": "start",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/lib/Server.php",
        "line": 339,
        "function": "exec",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php",
        "line": 35,
        "function": "exec",
        "class": "OCA\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/remote.php",
        "line": 166,
        "args": [
          "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php"
        ],
        "function": "require_once"
      }
    ],
    "File": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php",
    "Line": 37,
    "Previous": {
      "Exception": "Doctrine\\DBAL\\Driver\\PDO\\Exception",
      "Message": "SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction",
      "Code": 1213,
      "Trace": [
        {
          "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
          "line": 94,
          "function": "new",
          "class": "Doctrine\\DBAL\\Driver\\PDO\\Exception",
          "type": "::"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
          "line": 1163,
          "function": "execute",
          "class": "Doctrine\\DBAL\\Driver\\PDO\\Statement",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/DB/Connection.php",
          "line": 267,
          "function": "executeStatement",
          "class": "Doctrine\\DBAL\\Connection",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
          "line": 354,
          "function": "executeStatement",
          "class": "OC\\DB\\Connection",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
          "line": 287,
          "function": "execute",
          "class": "Doctrine\\DBAL\\Query\\QueryBuilder",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/Files/Cache/Cache.php",
          "line": 379,
          "function": "execute",
          "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/Files/Cache/Wrapper/CacheWrapper.php",
          "line": 145,
          "function": "update",
          "class": "OC\\Files\\Cache\\Cache",
          "type": "->",
          "args": [
            "*** sensitive parameters replaced ***"
          ]
        },
        {
          "file": "/var/www/nextcloud/lib/private/Files/Cache/Wrapper/CacheWrapper.php",
          "line": 145,
          "function": "update",
          "class": "OC\\Files\\Cache\\Wrapper\\CacheWrapper",
          "type": "->",
          "args": [
            "*** sensitive parameters replaced ***"
          ]
        },
        {
          "file": "/var/www/nextcloud/lib/private/Files/Cache/Updater.php",
          "line": 256,
          "function": "update",
          "class": "OC\\Files\\Cache\\Wrapper\\CacheWrapper",
          "type": "->",
          "args": [
            "*** sensitive parameters replaced ***"
          ]
        },
        {
          "file": "/var/www/nextcloud/lib/private/Files/Cache/Updater.php",
          "line": 138,
          "function": "correctParentStorageMtime",
          "class": "OC\\Files\\Cache\\Updater",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/Files/View.php",
          "line": 323,
          "function": "update",
          "class": "OC\\Files\\Cache\\Updater",
          "type": "->",
          "args": [
            "*** sensitive parameters replaced ***"
          ]
        },
        {
          "file": "/var/www/nextcloud/lib/private/Files/View.php",
          "line": 943,
          "function": "writeUpdate",
          "class": "OC\\Files\\View",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/files_versions/lib/Versions/LegacyVersionsBackend.php",
          "line": 93,
          "function": "copy",
          "class": "OC\\Files\\View",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/files_versions/lib/Versions/VersionManager.php",
          "line": 92,
          "function": "createVersion",
          "class": "OCA\\Files_Versions\\Versions\\LegacyVersionsBackend",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/files_versions/lib/Storage.php",
          "line": 217,
          "function": "createVersion",
          "class": "OCA\\Files_Versions\\Versions\\VersionManager",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/files_versions/lib/Hooks.php",
          "line": 56,
          "function": "store",
          "class": "OCA\\Files_Versions\\Storage",
          "type": "::"
        },
        {
          "file": "/var/www/nextcloud/lib/private/legacy/OC_Hook.php",
          "line": 106,
          "function": "write_hook",
          "class": "OCA\\Files_Versions\\Hooks",
          "type": "::"
        },
        {
          "file": "/var/www/nextcloud/lib/private/Files/View.php",
          "line": 1284,
          "function": "emit",
          "class": "OC_Hook",
          "type": "::"
        },
        {
          "file": "/var/www/nextcloud/lib/private/Files/View.php",
          "line": 1154,
          "function": "runHooks",
          "class": "OC\\Files\\View",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/Files/View.php",
          "line": 706,
          "function": "basicOperation",
          "class": "OC\\Files\\View",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/Files/Node/Folder.php",
          "line": 198,
          "function": "file_put_contents",
          "class": "OC\\Files\\View",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/dav/lib/BulkUpload/BulkUploadPlugin.php",
          "line": 81,
          "function": "newFile",
          "class": "OC\\Files\\Node\\Folder",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
          "line": 89,
          "function": "httpPost",
          "class": "OCA\\DAV\\BulkUpload\\BulkUploadPlugin",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
          "line": 472,
          "function": "emit",
          "class": "Sabre\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
          "line": 253,
          "function": "invokeMethod",
          "class": "Sabre\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
          "line": 321,
          "function": "start",
          "class": "Sabre\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/dav/lib/Server.php",
          "line": 339,
          "function": "exec",
          "class": "Sabre\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php",
          "line": 35,
          "function": "exec",
          "class": "OCA\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/remote.php",
          "line": 166,
          "args": [
            "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php"
          ],
          "function": "require_once"
        }
      ],
      "File": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php",
      "Line": 26,
      "Previous": {
        "Exception": "PDOException",
        "Message": "SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction",
        "Code": "40001",
        "Trace": [
          {
            "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
            "line": 92,
            "function": "execute",
            "class": "PDOStatement",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
            "line": 1163,
            "function": "execute",
            "class": "Doctrine\\DBAL\\Driver\\PDO\\Statement",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/DB/Connection.php",
            "line": 267,
            "function": "executeStatement",
            "class": "Doctrine\\DBAL\\Connection",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
            "line": 354,
            "function": "executeStatement",
            "class": "OC\\DB\\Connection",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
            "line": 287,
            "function": "execute",
            "class": "Doctrine\\DBAL\\Query\\QueryBuilder",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/Files/Cache/Cache.php",
            "line": 379,
            "function": "execute",
            "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/Files/Cache/Wrapper/CacheWrapper.php",
            "line": 145,
            "function": "update",
            "class": "OC\\Files\\Cache\\Cache",
            "type": "->",
            "args": [
              "*** sensitive parameters replaced ***"
            ]
          },
          {
            "file": "/var/www/nextcloud/lib/private/Files/Cache/Wrapper/CacheWrapper.php",
            "line": 145,
            "function": "update",
            "class": "OC\\Files\\Cache\\Wrapper\\CacheWrapper",
            "type": "->",
            "args": [
              "*** sensitive parameters replaced ***"
            ]
          },
          {
            "file": "/var/www/nextcloud/lib/private/Files/Cache/Updater.php",
            "line": 256,
            "function": "update",
            "class": "OC\\Files\\Cache\\Wrapper\\CacheWrapper",
            "type": "->",
            "args": [
              "*** sensitive parameters replaced ***"
            ]
          },
          {
            "file": "/var/www/nextcloud/lib/private/Files/Cache/Updater.php",
            "line": 138,
            "function": "correctParentStorageMtime",
            "class": "OC\\Files\\Cache\\Updater",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/Files/View.php",
            "line": 323,
            "function": "update",
            "class": "OC\\Files\\Cache\\Updater",
            "type": "->",
            "args": [
              "*** sensitive parameters replaced ***"
            ]
          },
          {
            "file": "/var/www/nextcloud/lib/private/Files/View.php",
            "line": 943,
            "function": "writeUpdate",
            "class": "OC\\Files\\View",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/files_versions/lib/Versions/LegacyVersionsBackend.php",
            "line": 93,
            "function": "copy",
            "class": "OC\\Files\\View",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/files_versions/lib/Versions/VersionManager.php",
            "line": 92,
            "function": "createVersion",
            "class": "OCA\\Files_Versions\\Versions\\LegacyVersionsBackend",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/files_versions/lib/Storage.php",
            "line": 217,
            "function": "createVersion",
            "class": "OCA\\Files_Versions\\Versions\\VersionManager",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/files_versions/lib/Hooks.php",
            "line": 56,
            "function": "store",
            "class": "OCA\\Files_Versions\\Storage",
            "type": "::"
          },
          {
            "file": "/var/www/nextcloud/lib/private/legacy/OC_Hook.php",
            "line": 106,
            "function": "write_hook",
            "class": "OCA\\Files_Versions\\Hooks",
            "type": "::"
          },
          {
            "file": "/var/www/nextcloud/lib/private/Files/View.php",
            "line": 1284,
            "function": "emit",
            "class": "OC_Hook",
            "type": "::"
          },
          {
            "file": "/var/www/nextcloud/lib/private/Files/View.php",
            "line": 1154,
            "function": "runHooks",
            "class": "OC\\Files\\View",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/Files/View.php",
            "line": 706,
            "function": "basicOperation",
            "class": "OC\\Files\\View",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/Files/Node/Folder.php",
            "line": 198,
            "function": "file_put_contents",
            "class": "OC\\Files\\View",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/dav/lib/BulkUpload/BulkUploadPlugin.php",
            "line": 81,
            "function": "newFile",
            "class": "OC\\Files\\Node\\Folder",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
            "line": 89,
            "function": "httpPost",
            "class": "OCA\\DAV\\BulkUpload\\BulkUploadPlugin",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line": 472,
            "function": "emit",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line": 253,
            "function": "invokeMethod",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line": 321,
            "function": "start",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/dav/lib/Server.php",
            "line": 339,
            "function": "exec",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php",
            "line": 35,
            "function": "exec",
            "class": "OCA\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/remote.php",
            "line": 166,
            "args": [
              "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php"
            ],
            "function": "require_once"
          }
        ],
        "File": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
        "Line": 92
      }
    },
    "CustomMessage": "--"
  },
  "id": "61a636a484de3"
}

The text was updated successfully, but these errors were encountered:

szaimen · 2021-11-30T14:51:27Z

cc @artonge @nextcloud/desktop

KRSogaard · 2022-01-23T19:32:05Z

Why was this closed, the issue being referred #29987 is referring to this issue. I am still having this issue

samuelsson · 2022-01-23T20:22:55Z

I also had this exact same issue and decided to make some re-installations and tests. Since I have a NAS at home I decided to keep all files on the NAS but running NextCloud on a separate server (Proxmox). Mounting the NFS share on the Nextcloud server works fine.

The problems started to occur for me when also hosting the database (both postgres and mariadb) on the NAS, and accessing it over the network by IP in Nextcloud. When I changed to a locally installed database on the nextcloud server itself and accessing it over the socket everything works flawlessly. Could it be network speeds or just that the DB is externally accessed? LAN speed it 1000/1000 in my tests.

I also tried with and without Redis as cache but that didn't change anything. I've been running nextcloud with a local database for several weeks now without any problems.

obel1x · 2022-03-02T19:29:12Z

Yes, had deadlocks here too. They were corresponding to the query:
UPDATE oc_filecache SET mtime = ?, storage_mtime = ? WHERE (fileid = ?) AND (((mtime <> ?) OR (mtime IS NULL)) OR ((storage_mtime <> ?) OR (storage_mtime IS NULL)))

This does not look like a very complex statement, so its up to mariadb to get this going. I also have not found bad behaviour of NC or php with that (deadlocks before made it impossible to sync, as it always restarted to sync and always ran into that deadlock).

I changed mariadb- config to use fsync instead of direct writes (https://mariadb.com/docs/reference/mdb/system-variables/innodb_flush_method/), than upped the Version of mariadb to 10.6.5 now there had been no deadlock any more since some days, but my system is currently not on heavy load.

Maybe check your config and temporary tables by using mysqltuner.pl and your slow.log to eleminate bottlenecks for your db, it may help to reduce deadlocks when the db performs good in general

AndyXheli · 2022-03-22T13:42:10Z

Still an issue on NC 23.0.2

danieltuzes · 2022-04-06T21:50:17Z

Still an issue on NC 23.0.3

obel1x · 2022-04-15T08:21:06Z

ok, still a problem here too. Can you check in the logs, if this is related to the "update oc_filecache" as it is for me?
This time it was:
"args":["UPDATE oc_filecache SET size = :dcValue3 WHERE (fileid = :dcValue1) AND ((size <> :dcValue2) OR (size IS NULL))",{"dcValue1":"*** sensitive parameter replaced ***","dcValue2":282256502,"dcValue3":282256502},{"dcValue1":1,"dcValue2":2,"dcValue3":2}]}

It was caused when deleting multiple files in a directory and when recalculationg the directory size, which seems to do it recursivly (scanning all upper folders) by function move2trash of "class":"OCA\Files_Trashbin\Trashbin".

The way this is done looks like the calculation of directory sizes is not synchronised by nexcloud, so it would not suprise me if the next deletion would cause deadlocks in that table for the entry - which may not be bad as such, but may lead to wrong data.

Maybe some approach to nextcloud for this: synchronise filecache- operations...

danieltuzes · 2022-04-15T08:37:28Z

My log from the web interface is:

{
   "reqId":"0P4cP8wjXhtwyT1Ktb0n",
   "level":4,
   "time":"2022-04-14T22:16:55+00:00",
   "remoteAddr":"172.19.0.1",
   "user":"tuzes",
   "app":"webdav",
   "method":"PUT",
   "url":"/remote.php/dav/files/tuzes/kamera/r%C3%A9gi%20k%C3%A9pt%C3%A1r/%C3%9Cnnepi/temet%C3%A9s/nagypapa/K%C3%A9p%20045.jpg",
   "message":"An exception occurred while executing a query: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction",
   "userAgent":"Mozilla/5.0 (Windows) mirall/3.4.4stable-Win64 (build 20220318) (Nextcloud, windows-10.0.19044 ClientArchitecture: x86_64 OsArchitecture: x86_64)",
   "version":"23.0.3.2",
   "exception":{
      "Exception":"Doctrine\\DBAL\\Exception\\DeadlockException",
      "Message":"An exception occurred while executing a query: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction",
      "Code":1213,
      "Trace":[
         {
            "file":"/var/www/html/3rdparty/doctrine/dbal/src/Connection.php",
            "line":1780,
            "function":"convert",
            "class":"Doctrine\\DBAL\\Driver\\API\\MySQL\\ExceptionConverter",
            "type":"->",
            "args":[
               {
                  "__class__":"Doctrine\\DBAL\\Driver\\PDO\\Exception"
               },
               {
                  "__class__":"Doctrine\\DBAL\\Query"
               }
            ]
         },
         {
            "file":"/var/www/html/3rdparty/doctrine/dbal/src/Connection.php",
            "line":1719,
            "function":"handleDriverException",
            "class":"Doctrine\\DBAL\\Connection",
            "type":"->",
            "args":[
               {
                  "__class__":"Doctrine\\DBAL\\Driver\\PDO\\Exception"
               },
               {
                  "__class__":"Doctrine\\DBAL\\Query"
               }
            ]
         },
         {
            "file":"/var/www/html/3rdparty/doctrine/dbal/src/Connection.php",
            "line":1173,
            "function":"convertExceptionDuringQuery",
            "class":"Doctrine\\DBAL\\Connection",
            "type":"->",
            "args":[
               {
                  "__class__":"Doctrine\\DBAL\\Driver\\PDO\\Exception"
               },
               "UPDATE `oc_filecache` SET `mtime` = ?, `storage_mtime` = ? WHERE (`fileid` = ?) AND (((`mtime` <> ?) OR (`mtime` IS NULL)) OR ((`storage_mtime` <> ?) OR (`storage_mtime` IS NULL)))",
               [
                  1649974615,
                  1649974615,
                  "*** sensitive parameter replaced ***",
                  1649974615,
                  1649974615
               ],
               [
                  2,
                  2,
                  1,
                  2,
                  2
               ]
            ]
         },
         {
            "file":"/var/www/html/lib/private/DB/Connection.php",
            "line":270,
            "function":"executeStatement",
            "class":"Doctrine\\DBAL\\Connection",
            "type":"->",
            "args":[
               "UPDATE `oc_filecache` SET `mtime` = ?, `storage_mtime` = ? WHERE (`fileid` = ?) AND (((`mtime` <> ?) OR (`mtime` IS NULL)) OR ((`storage_mtime` <> ?) OR (`storage_mtime` IS NULL)))",
               [
                  1649974615,
                  1649974615,
                  "*** sensitive parameter replaced ***",
                  1649974615,
                  1649974615
               ],
               [
                  2,
                  2,
                  1,
                  2,
                  2
               ]
            ]
         },
         {
            "file":"/var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
            "line":354,
            "function":"executeStatement",
            "class":"OC\\DB\\Connection",
            "type":"->",
            "args":[
               "UPDATE `oc_filecache` SET `mtime` = :dcValue4, `storage_mtime` = :dcValue5 WHERE (`fileid` = :dcValue1) AND (((`mtime` <> :dcValue2) OR (`mtime` IS NULL)) OR ((`storage_mtime` <> :dcValue3) OR (`storage_mtime` IS NULL)))",
               {
                  "dcValue1":"*** sensitive parameter replaced ***",
                  "dcValue2":1649974615,
                  "dcValue3":1649974615,
                  "dcValue4":1649974615,
                  "dcValue5":1649974615
               },
               {
                  "dcValue1":1,
                  "dcValue2":2,
                  "dcValue3":2,
                  "dcValue4":2,
                  "dcValue5":2
               }
            ]
         },
         {
            "file":"/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php",
            "line":287,
            "function":"execute",
            "class":"Doctrine\\DBAL\\Query\\QueryBuilder",
            "type":"->",
            "args":[
               
            ]
         },
         {
            "file":"/var/www/html/lib/private/Files/Cache/Cache.php",
            "line":379,
            "function":"execute",
            "class":"OC\\DB\\QueryBuilder\\QueryBuilder",
            "type":"->",
            "args":[
               
            ]
         },
         {
            "file":"/var/www/html/lib/private/Files/Cache/Updater.php",
            "line":256,
            "function":"update",
            "class":"OC\\Files\\Cache\\Cache",
            "type":"->",
            "args":[
               "*** sensitive parameters replaced ***"
            ]
         },
         {
            "file":"/var/www/html/lib/private/Files/Cache/Updater.php",
            "line":138,
            "function":"correctParentStorageMtime",
            "class":"OC\\Files\\Cache\\Updater",
            "type":"->",
            "args":[
               "*** sensitive parameter replaced ***"
            ]
         },
         {
            "file":"/var/www/html/apps/dav/lib/Connector/Sabre/File.php",
            "line":339,
            "function":"update",
            "class":"OC\\Files\\Cache\\Updater",
            "type":"->",
            "args":[
               "*** sensitive parameters replaced ***"
            ]
         },
         {
            "file":"/var/www/html/apps/dav/lib/Connector/Sabre/Directory.php",
            "line":155,
            "function":"put",
            "class":"OCA\\DAV\\Connector\\Sabre\\File",
            "type":"->",
            "args":[
               null
            ]
         },
         {
            "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line":1098,
            "function":"createFile",
            "class":"OCA\\DAV\\Connector\\Sabre\\Directory",
            "type":"->",
            "args":[
               "Kép 045.jpg",
               null
            ]
         },
         {
            "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/CorePlugin.php",
            "line":504,
            "function":"createFile",
            "class":"Sabre\\DAV\\Server",
            "type":"->",
            "args":[
               "files/tuzes/kamera/régi képtár/Ünnepi/temetés/nagypapa/Kép 045.jpg",
               null,
               null
            ]
         },
         {
            "file":"/var/www/html/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
            "line":89,
            "function":"httpPut",
            "class":"Sabre\\DAV\\CorePlugin",
            "type":"->",
            "args":[
               {
                  "__class__":"Sabre\\HTTP\\Request"
               },
               {
                  "__class__":"Sabre\\HTTP\\Response"
               }
            ]
         },
         {
            "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line":472,
            "function":"emit",
            "class":"Sabre\\DAV\\Server",
            "type":"->",
            "args":[
               "method:PUT",
               [
                  {
                     "__class__":"Sabre\\HTTP\\Request"
                  },
                  {
                     "__class__":"Sabre\\HTTP\\Response"
                  }
               ]
            ]
         },
         {
            "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line":253,
            "function":"invokeMethod",
            "class":"Sabre\\DAV\\Server",
            "type":"->",
            "args":[
               {
                  "__class__":"Sabre\\HTTP\\Request"
               },
               {
                  "__class__":"Sabre\\HTTP\\Response"
               }
            ]
         },
         {
            "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line":321,
            "function":"start",
            "class":"Sabre\\DAV\\Server",
            "type":"->",
            "args":[
               
            ]
         },
         {
            "file":"/var/www/html/apps/dav/lib/Server.php",
            "line":339,
            "function":"exec",
            "class":"Sabre\\DAV\\Server",
            "type":"->",
            "args":[
               
            ]
         },
         {
            "file":"/var/www/html/apps/dav/appinfo/v2/remote.php",
            "line":35,
            "function":"exec",
            "class":"OCA\\DAV\\Server",
            "type":"->",
            "args":[
               
            ]
         },
         {
            "file":"/var/www/html/remote.php",
            "line":166,
            "args":[
               "/var/www/html/apps/dav/appinfo/v2/remote.php"
            ],
            "function":"require_once"
         }
      ],
      "File":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php",
      "Line":37,
      "Previous":{
         "Exception":"Doctrine\\DBAL\\Driver\\PDO\\Exception",
         "Message":"SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction",
         "Code":1213,
         "Trace":[
            {
               "file":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
               "line":94,
               "function":"new",
               "class":"Doctrine\\DBAL\\Driver\\PDO\\Exception",
               "type":"::",
               "args":[
                  {
                     "__class__":"PDOException",
                     "errorInfo":[
                        "40001",
                        1213,
                        "Deadlock found when trying to get lock; try restarting transaction"
                     ]
                  }
               ]
            },
            {
               "file":"/var/www/html/3rdparty/doctrine/dbal/src/Connection.php",
               "line":1163,
               "function":"execute",
               "class":"Doctrine\\DBAL\\Driver\\PDO\\Statement",
               "type":"->",
               "args":[
                  
               ]
            },
            {
               "file":"/var/www/html/lib/private/DB/Connection.php",
               "line":270,
               "function":"executeStatement",
               "class":"Doctrine\\DBAL\\Connection",
               "type":"->",
               "args":[
                  "UPDATE `oc_filecache` SET `mtime` = ?, `storage_mtime` = ? WHERE (`fileid` = ?) AND (((`mtime` <> ?) OR (`mtime` IS NULL)) OR ((`storage_mtime` <> ?) OR (`storage_mtime` IS NULL)))",
                  [
                     1649974615,
                     1649974615,
                     "*** sensitive parameter replaced ***",
                     1649974615,
                     1649974615
                  ],
                  [
                     2,
                     2,
                     1,
                     2,
                     2
                  ]
               ]
            },
            {
               "file":"/var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
               "line":354,
               "function":"executeStatement",
               "class":"OC\\DB\\Connection",
               "type":"->",
               "args":[
                  "UPDATE `oc_filecache` SET `mtime` = :dcValue4, `storage_mtime` = :dcValue5 WHERE (`fileid` = :dcValue1) AND (((`mtime` <> :dcValue2) OR (`mtime` IS NULL)) OR ((`storage_mtime` <> :dcValue3) OR (`storage_mtime` IS NULL)))",
                  {
                     "dcValue1":"*** sensitive parameter replaced ***",
                     "dcValue2":1649974615,
                     "dcValue3":1649974615,
                     "dcValue4":1649974615,
                     "dcValue5":1649974615
                  },
                  {
                     "dcValue1":1,
                     "dcValue2":2,
                     "dcValue3":2,
                     "dcValue4":2,
                     "dcValue5":2
                  }
               ]
            },
            {
               "file":"/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php",
               "line":287,
               "function":"execute",
               "class":"Doctrine\\DBAL\\Query\\QueryBuilder",
               "type":"->",
               "args":[
                  
               ]
            },
            {
               "file":"/var/www/html/lib/private/Files/Cache/Cache.php",
               "line":379,
               "function":"execute",
               "class":"OC\\DB\\QueryBuilder\\QueryBuilder",
               "type":"->",
               "args":[
                  
               ]
            },
            {
               "file":"/var/www/html/lib/private/Files/Cache/Updater.php",
               "line":256,
               "function":"update",
               "class":"OC\\Files\\Cache\\Cache",
               "type":"->",
               "args":[
                  "*** sensitive parameters replaced ***"
               ]
            },
            {
               "file":"/var/www/html/lib/private/Files/Cache/Updater.php",
               "line":138,
               "function":"correctParentStorageMtime",
               "class":"OC\\Files\\Cache\\Updater",
               "type":"->",
               "args":[
                  "*** sensitive parameter replaced ***"
               ]
            },
            {
               "file":"/var/www/html/apps/dav/lib/Connector/Sabre/File.php",
               "line":339,
               "function":"update",
               "class":"OC\\Files\\Cache\\Updater",
               "type":"->",
               "args":[
                  "*** sensitive parameters replaced ***"
               ]
            },
            {
               "file":"/var/www/html/apps/dav/lib/Connector/Sabre/Directory.php",
               "line":155,
               "function":"put",
               "class":"OCA\\DAV\\Connector\\Sabre\\File",
               "type":"->",
               "args":[
                  null
               ]
            },
            {
               "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
               "line":1098,
               "function":"createFile",
               "class":"OCA\\DAV\\Connector\\Sabre\\Directory",
               "type":"->",
               "args":[
                  "Kép 045.jpg",
                  null
               ]
            },
            {
               "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/CorePlugin.php",
               "line":504,
               "function":"createFile",
               "class":"Sabre\\DAV\\Server",
               "type":"->",
               "args":[
                  "files/tuzes/kamera/régi képtár/Ünnepi/temetés/nagypapa/Kép 045.jpg",
                  null,
                  null
               ]
            },
            {
               "file":"/var/www/html/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
               "line":89,
               "function":"httpPut",
               "class":"Sabre\\DAV\\CorePlugin",
               "type":"->",
               "args":[
                  {
                     "__class__":"Sabre\\HTTP\\Request"
                  },
                  {
                     "__class__":"Sabre\\HTTP\\Response"
                  }
               ]
            },
            {
               "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
               "line":472,
               "function":"emit",
               "class":"Sabre\\DAV\\Server",
               "type":"->",
               "args":[
                  "method:PUT",
                  [
                     {
                        "__class__":"Sabre\\HTTP\\Request"
                     },
                     {
                        "__class__":"Sabre\\HTTP\\Response"
                     }
                  ]
               ]
            },
            {
               "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
               "line":253,
               "function":"invokeMethod",
               "class":"Sabre\\DAV\\Server",
               "type":"->",
               "args":[
                  {
                     "__class__":"Sabre\\HTTP\\Request"
                  },
                  {
                     "__class__":"Sabre\\HTTP\\Response"
                  }
               ]
            },
            {
               "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
               "line":321,
               "function":"start",
               "class":"Sabre\\DAV\\Server",
               "type":"->",
               "args":[
                  
               ]
            },
            {
               "file":"/var/www/html/apps/dav/lib/Server.php",
               "line":339,
               "function":"exec",
               "class":"Sabre\\DAV\\Server",
               "type":"->",
               "args":[
                  
               ]
            },
            {
               "file":"/var/www/html/apps/dav/appinfo/v2/remote.php",
               "line":35,
               "function":"exec",
               "class":"OCA\\DAV\\Server",
               "type":"->",
               "args":[
                  
               ]
            },
            {
               "file":"/var/www/html/remote.php",
               "line":166,
               "args":[
                  "/var/www/html/apps/dav/appinfo/v2/remote.php"
               ],
               "function":"require_once"
            }
         ],
         "File":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php",
         "Line":26,
         "Previous":{
            "Exception":"PDOException",
            "Message":"SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction",
            "Code":"40001",
            "Trace":[
               {
                  "file":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
                  "line":92,
                  "function":"execute",
                  "class":"PDOStatement",
                  "type":"->",
                  "args":[
                     null
                  ]
               },
               {
                  "file":"/var/www/html/3rdparty/doctrine/dbal/src/Connection.php",
                  "line":1163,
                  "function":"execute",
                  "class":"Doctrine\\DBAL\\Driver\\PDO\\Statement",
                  "type":"->",
                  "args":[
                     
                  ]
               },
               {
                  "file":"/var/www/html/lib/private/DB/Connection.php",
                  "line":270,
                  "function":"executeStatement",
                  "class":"Doctrine\\DBAL\\Connection",
                  "type":"->",
                  "args":[
                     "UPDATE `oc_filecache` SET `mtime` = ?, `storage_mtime` = ? WHERE (`fileid` = ?) AND (((`mtime` <> ?) OR (`mtime` IS NULL)) OR ((`storage_mtime` <> ?) OR (`storage_mtime` IS NULL)))",
                     [
                        1649974615,
                        1649974615,
                        "*** sensitive parameter replaced ***",
                        1649974615,
                        1649974615
                     ],
                     [
                        2,
                        2,
                        1,
                        2,
                        2
                     ]
                  ]
               },
               {
                  "file":"/var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
                  "line":354,
                  "function":"executeStatement",
                  "class":"OC\\DB\\Connection",
                  "type":"->",
                  "args":[
                     "UPDATE `oc_filecache` SET `mtime` = :dcValue4, `storage_mtime` = :dcValue5 WHERE (`fileid` = :dcValue1) AND (((`mtime` <> :dcValue2) OR (`mtime` IS NULL)) OR ((`storage_mtime` <> :dcValue3) OR (`storage_mtime` IS NULL)))",
                     {
                        "dcValue1":"*** sensitive parameter replaced ***",
                        "dcValue2":1649974615,
                        "dcValue3":1649974615,
                        "dcValue4":1649974615,
                        "dcValue5":1649974615
                     },
                     {
                        "dcValue1":1,
                        "dcValue2":2,
                        "dcValue3":2,
                        "dcValue4":2,
                        "dcValue5":2
                     }
                  ]
               },
               {
                  "file":"/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php",
                  "line":287,
                  "function":"execute",
                  "class":"Doctrine\\DBAL\\Query\\QueryBuilder",
                  "type":"->",
                  "args":[
                     
                  ]
               },
               {
                  "file":"/var/www/html/lib/private/Files/Cache/Cache.php",
                  "line":379,
                  "function":"execute",
                  "class":"OC\\DB\\QueryBuilder\\QueryBuilder",
                  "type":"->",
                  "args":[
                     
                  ]
               },
               {
                  "file":"/var/www/html/lib/private/Files/Cache/Updater.php",
                  "line":256,
                  "function":"update",
                  "class":"OC\\Files\\Cache\\Cache",
                  "type":"->",
                  "args":[
                     "*** sensitive parameters replaced ***"
                  ]
               },
               {
                  "file":"/var/www/html/lib/private/Files/Cache/Updater.php",
                  "line":138,
                  "function":"correctParentStorageMtime",
                  "class":"OC\\Files\\Cache\\Updater",
                  "type":"->",
                  "args":[
                     "*** sensitive parameter replaced ***"
                  ]
               },
               {
                  "file":"/var/www/html/apps/dav/lib/Connector/Sabre/File.php",
                  "line":339,
                  "function":"update",
                  "class":"OC\\Files\\Cache\\Updater",
                  "type":"->",
                  "args":[
                     "*** sensitive parameters replaced ***"
                  ]
               },
               {
                  "file":"/var/www/html/apps/dav/lib/Connector/Sabre/Directory.php",
                  "line":155,
                  "function":"put",
                  "class":"OCA\\DAV\\Connector\\Sabre\\File",
                  "type":"->",
                  "args":[
                     null
                  ]
               },
               {
                  "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
                  "line":1098,
                  "function":"createFile",
                  "class":"OCA\\DAV\\Connector\\Sabre\\Directory",
                  "type":"->",
                  "args":[
                     "Kép 045.jpg",
                     null
                  ]
               },
               {
                  "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/CorePlugin.php",
                  "line":504,
                  "function":"createFile",
                  "class":"Sabre\\DAV\\Server",
                  "type":"->",
                  "args":[
                     "files/tuzes/kamera/régi képtár/Ünnepi/temetés/nagypapa/Kép 045.jpg",
                     null,
                     null
                  ]
               },
               {
                  "file":"/var/www/html/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
                  "line":89,
                  "function":"httpPut",
                  "class":"Sabre\\DAV\\CorePlugin",
                  "type":"->",
                  "args":[
                     {
                        "__class__":"Sabre\\HTTP\\Request"
                     },
                     {
                        "__class__":"Sabre\\HTTP\\Response"
                     }
                  ]
               },
               {
                  "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
                  "line":472,
                  "function":"emit",
                  "class":"Sabre\\DAV\\Server",
                  "type":"->",
                  "args":[
                     "method:PUT",
                     [
                        {
                           "__class__":"Sabre\\HTTP\\Request"
                        },
                        {
                           "__class__":"Sabre\\HTTP\\Response"
                        }
                     ]
                  ]
               },
               {
                  "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
                  "line":253,
                  "function":"invokeMethod",
                  "class":"Sabre\\DAV\\Server",
                  "type":"->",
                  "args":[
                     {
                        "__class__":"Sabre\\HTTP\\Request"
                     },
                     {
                        "__class__":"Sabre\\HTTP\\Response"
                     }
                  ]
               },
               {
                  "file":"/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
                  "line":321,
                  "function":"start",
                  "class":"Sabre\\DAV\\Server",
                  "type":"->",
                  "args":[
                     
                  ]
               },
               {
                  "file":"/var/www/html/apps/dav/lib/Server.php",
                  "line":339,
                  "function":"exec",
                  "class":"Sabre\\DAV\\Server",
                  "type":"->",
                  "args":[
                     
                  ]
               },
               {
                  "file":"/var/www/html/apps/dav/appinfo/v2/remote.php",
                  "line":35,
                  "function":"exec",
                  "class":"OCA\\DAV\\Server",
                  "type":"->",
                  "args":[
                     
                  ]
               },
               {
                  "file":"/var/www/html/remote.php",
                  "line":166,
                  "args":[
                     "/var/www/html/apps/dav/appinfo/v2/remote.php"
                  ],
                  "function":"require_once"
               }
            ],
            "File":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
            "Line":92
         }
      },
      "CustomMessage":"--"
   },
   "id":"62592c7b5bbaa"
}

obel1x · 2022-04-15T09:19:55Z

yes, this is all due to filecache updates. In your case putting a file to nextcloud, in my case deleting it, the top case too (maybe in the versions-manager, which irritated me, but its basically the same).
This also explains, why it happens more often if the database is large or slow.

Nextcloud should really think about serialisation of the class "OC\Files\Cache\Updater" function "update" to not interfere with itself when doing many changes, but i would rate it minor, as it will not impact basic functionality of the server.

meldarionqeusse · 2022-05-07T17:41:10Z

still an issue with Nextcloud 24

nderambure · 2022-05-17T11:34:16Z

Still a blocking issue, users can not upload bulk directories, making Nextcloud useless for them…

slavoren · 2022-06-15T09:21:02Z

Is there any realiable workaround for this? Huge issue when users are migrating data from old DMS to nextcloud, because it breaks somewhere in the middle.

bdrewery · 2022-10-19T20:28:29Z

Same as #22482 which is locked and all of these get duped to.

GieltjE · 2022-11-17T21:27:02Z

This has become much more prominent with the v25 release, simply uploading something with loads of small files through the webui always results in this issue.
Uploading the same files through the client results in "Network Error:99".

Resulting in simply unworkable situations.

garphil · 2022-11-26T13:35:51Z

I got same issue over and over on a new installation despite following many advices from different forums & docs. I tried the snap install on another VM I and was able to upload smoothly the full directory without issue.

meldarionqeusse · 2023-01-04T14:17:05Z

Same problem when upload folder with lots of small files. Compress file uploaded without a hitch

nderambure · 2023-01-04T15:52:42Z

I've applied the recommended configuration of MySQL (MariaDB for me) seen here : https://docs.nextcloud.com/server/stable/admin_manual/configuration_database/linux_database_configuration.html

Adding this to my conf file :

transaction_isolation   = READ-COMMITTED
binlog_format           = ROW
innodb_large_prefix     = on
innodb_file_format      = barracuda

makes the error disappear.
I'm still on NC 24, can you confirm too on 25 ?

meldarionqeusse · 2023-01-05T08:39:18Z

thanks @nderambure it seems your suggestion fixed the issue and I am running NC 25.

For others not so familiar with sql and this sort of things like I am. I added the following to /etc/mysql/my.cnf as per https://docs.nextcloud.com/server/stable/admin_manual/configuration_database/linux_database_configuration.html :

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
transaction_isolation = READ-COMMITTED
binlog_format = ROW
innodb_large_prefix=on
innodb_file_format=barracuda
innodb_file_per_table=1

labor4 · 2023-01-05T09:14:10Z

Wow.
I don't know for sure if that was the trouble, but I just had my first working bulk POST.

tail -f /var/log/apache2/other_vhosts_access.log |grep 'bulk\|PUT'

XXXXXXX - - [05/Jan/2023:09:09:02 +0000] "POST /remote.php/dav/bulk HTTP/1.1" 200 4475 "-" "Mozilla/5.0 (Macintosh) mirall/3.6.4git (build 12826) (Nextcloud, osx-22.1.0 ClientArchitecture: x86_64 OsArchitecture: x86_64)"

Tested with 200x random 10kb files in 1 directory. NC25.

This goes in an awesome direction. Approaching happiness.

btw
I adjusted the following in postgresql.conf
default_transaction_isolation = 'read committed'

szotsaki · 2023-01-05T10:28:13Z

Thank you for figuring this out!

According to MySQL manual:

Transaction Isolation Levels
READ COMMITTED
For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.

This might explain the lack of deadlocks with this configuration.

Nextcloud could also set this isolation level during connection time (so we wouldn't need to set this globally, affecting every database):

A user can change the isolation level for a single session or for all subsequent connections with the SET TRANSACTION statement.

Similar situation with binlog_format:

The logging format in a running MySQL server is controlled by setting the binlog_format server system variable. This variable can be set with session or global scope.

nderambure · 2023-01-05T15:14:51Z

About the "discover" of this configuration recommandation in NC doc, I think that when I first Install my NC (the 15th !), this doc was not existing, and since I'm not a Database specialist, I missed it.

Thank you @szotsaki for the precision, I'm wondering why NC does not set this for himself instead of configuring it globally, which force to use a custom instance of MySQL specifically for NC.

Any NC dev that could enlighten us ?

GieltjE · 2023-01-05T15:39:28Z

Changed the aformentioned values in MariaDB, re-enabled bulk upload in the config, deleted and uploaded 3k files and still encountered the same issues.

szaimen · 2023-01-09T10:45:43Z

Closing as this seems to be a configuration issue based on #29985 (comment)

c-schulz-rd13 · 2023-04-27T08:45:40Z

Same here changing the db config to READ_COMMITTED did not solve the problem.
Still an issue together with md5 hash is incorrect.

robbytobby · 2023-05-02T08:16:27Z

I'm running postgres (where default_transaction_isolation = 'read committed' is actually the default) and the problem exists with nc 26.0.1

obel1x · 2023-05-02T14:02:18Z

@robbytobby @szaimen @szotsaki
Read committed won't fix the Problem, as it is a problem of coding and handling parrallelisation.
I analysed this before: Nextcloud is doing all recalculations of top Directory sizes for each affected file in one parrallel execution by adding/substracting Values. This is very bad, as it performes many updates and will cause deadlocks, cause when changing more than one File in one (DB-) Commit, this tells the Database to synchroniously add or substract two values depending of the result of the other operation.
This MUST lead to a Deadlock and can only be solved by refacturing the code of Size- Calculation.

Unfortunatelly currently i am low on ressources - but please Nextclouders, have a look at this!

Best approach imo would either be to cache the sizecalculation (best to memcached/redis) and only write Results once after te Bunch of commits to the DB (when using Bulkuploads) - or to synchronise Access to the DB, which would be much slower and maybe complicated to do so, but would solve the Deadlocks.

yylzcom · 2023-10-26T01:37:32Z

@robbytobby @szaimen @szotsaki Read committed won't fix the Problem, as it is a problem of coding and handling parrallelisation. I analysed this before: Nextcloud is doing all recalculations of top Directory sizes for each affected file in one parrallel execution by adding/substracting Values. This is very bad, as it performes many updates and will cause deadlocks, cause when changing more than one File in one (DB-) Commit, this tells the Database to synchroniously add or substract two values depending of the result of the other operation. This MUST lead to a Deadlock and can only be solved by refacturing the code of Size- Calculation.

Unfortunatelly currently i am low on ressources - but please Nextclouders, have a look at this!

Best approach imo would either be to cache the sizecalculation (best to memcached/redis) and only write Results once after te Bunch of commits to the DB (when using Bulkuploads) - or to synchronise Access to the DB, which would be much slower and maybe complicated to do so, but would solve the Deadlocks.

@obel1x Hi, just wondering if there is any temporary solution? Like manually delete something? My nextcloud is totally stopped for days.

yylzcom · 2023-10-26T08:31:09Z

@robbytobby @szaimen @szotsaki Read committed won't fix the Problem, as it is a problem of coding and handling parrallelisation. I analysed this before: Nextcloud is doing all recalculations of top Directory sizes for each affected file in one parrallel execution by adding/substracting Values. This is very bad, as it performes many updates and will cause deadlocks, cause when changing more than one File in one (DB-) Commit, this tells the Database to synchroniously add or substract two values depending of the result of the other operation. This MUST lead to a Deadlock and can only be solved by refacturing the code of Size- Calculation.
Unfortunatelly currently i am low on ressources - but please Nextclouders, have a look at this!
Best approach imo would either be to cache the sizecalculation (best to memcached/redis) and only write Results once after te Bunch of commits to the DB (when using Bulkuploads) - or to synchronise Access to the DB, which would be much slower and maybe complicated to do so, but would solve the Deadlocks.

@obel1x Hi, just wondering if there is any temporary solution? Like manually delete something? My nextcloud is totally stopped for days.

It seems that this error did not make my nextcloud stopped working.

I deleted all the file lock in the MySQL and execute occ files:scan --all , then the problem is gone.

apg1980 added 0. Needs triage Pending check for reproducibility or if it fits our roadmap bug labels Nov 30, 2021

MichaIng added the 23-feedback label Nov 30, 2021

This comment has been minimized.

Sign in to view

solracsf changed the title ~~Nextcloud 23 mariadb deadlog~~ Nextcloud 23 MariaDB Deadlock Nov 30, 2021

This comment has been minimized.

Sign in to view

artonge mentioned this issue Dec 1, 2021

Bulk Upload Issues #29702

Open

2 tasks

This comment has been minimized.

Sign in to view

solracsf changed the title ~~Nextcloud 23 MariaDB Deadlock~~ Bulk Upload - Deadlock found when trying to get lock Dec 29, 2021

solracsf mentioned this issue Dec 29, 2021

Nextcloud 23 a lot of 'shared locks' messages #29987

Closed

GieltjE mentioned this issue Nov 17, 2022

[Bug]: Serialization failure: 1213 Deadlock error lead to a "conflict" the next time sync runs #34697

Open

9 tasks

szaimen closed this as completed Jan 9, 2023

labor4 mentioned this issue Jan 16, 2023

Windows, Linux, and macOS clients freezing when bulk uploading nextcloud/desktop#4106

Closed

labor4 mentioned this issue Apr 14, 2023

Bulk Upload - Computed md5 hash is incorrect #29984

Open

szaimen reopened this May 2, 2023

szaimen added 27-feedback and removed 23-feedback labels May 2, 2023

This was referenced May 2, 2023

DeadlockException executing UPDATE oc_filecache #22482

Closed

[Bug]: Deadlock when Deleting shared files inside shared folder #36328

Closed

szaimen added technical debt and removed bug 27-feedback labels May 22, 2023

keithf4 mentioned this issue Jun 21, 2023

Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction #6899

Closed

joshtrichards added the performance 🚀 label Aug 24, 2023

joshtrichards added the feature: dav label Sep 14, 2023

JulienFS added this to Data integrity and its friends May 25, 2024

joshtrichards added the feature: filesystem label Jul 8, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Bulk Upload - Deadlock found when trying to get lock #29985

Bulk Upload - Deadlock found when trying to get lock #29985

apg1980 commented Nov 30, 2021 •

edited by solracsf

Loading

This comment has been minimized.

szaimen commented Nov 30, 2021

This comment has been minimized.

This comment has been minimized.

KRSogaard commented Jan 23, 2022

samuelsson commented Jan 23, 2022

obel1x commented Mar 2, 2022 •

edited

Loading

AndyXheli commented Mar 22, 2022

danieltuzes commented Apr 6, 2022

obel1x commented Apr 15, 2022 •

edited

Loading

danieltuzes commented Apr 15, 2022

obel1x commented Apr 15, 2022

meldarionqeusse commented May 7, 2022

nderambure commented May 17, 2022

slavoren commented Jun 15, 2022

bdrewery commented Oct 19, 2022

GieltjE commented Nov 17, 2022

garphil commented Nov 26, 2022

meldarionqeusse commented Jan 4, 2023

nderambure commented Jan 4, 2023

meldarionqeusse commented Jan 5, 2023

labor4 commented Jan 5, 2023 •

edited

Loading

szotsaki commented Jan 5, 2023

nderambure commented Jan 5, 2023 •

edited

Loading

GieltjE commented Jan 5, 2023

szaimen commented Jan 9, 2023

c-schulz-rd13 commented Apr 27, 2023

robbytobby commented May 2, 2023

obel1x commented May 2, 2023 •

edited

Loading

yylzcom commented Oct 26, 2023

yylzcom commented Oct 26, 2023

Bulk Upload - Deadlock found when trying to get lock #29985

Bulk Upload - Deadlock found when trying to get lock #29985

Comments

apg1980 commented Nov 30, 2021 • edited by solracsf Loading

This comment has been minimized.

szaimen commented Nov 30, 2021

This comment has been minimized.

This comment has been minimized.

KRSogaard commented Jan 23, 2022

samuelsson commented Jan 23, 2022

obel1x commented Mar 2, 2022 • edited Loading

AndyXheli commented Mar 22, 2022

danieltuzes commented Apr 6, 2022

obel1x commented Apr 15, 2022 • edited Loading

danieltuzes commented Apr 15, 2022

obel1x commented Apr 15, 2022

meldarionqeusse commented May 7, 2022

nderambure commented May 17, 2022

slavoren commented Jun 15, 2022

bdrewery commented Oct 19, 2022

GieltjE commented Nov 17, 2022

garphil commented Nov 26, 2022

meldarionqeusse commented Jan 4, 2023

nderambure commented Jan 4, 2023

meldarionqeusse commented Jan 5, 2023

labor4 commented Jan 5, 2023 • edited Loading

szotsaki commented Jan 5, 2023

nderambure commented Jan 5, 2023 • edited Loading

GieltjE commented Jan 5, 2023

szaimen commented Jan 9, 2023

c-schulz-rd13 commented Apr 27, 2023

robbytobby commented May 2, 2023

obel1x commented May 2, 2023 • edited Loading

yylzcom commented Oct 26, 2023

yylzcom commented Oct 26, 2023

apg1980 commented Nov 30, 2021 •

edited by solracsf

Loading

obel1x commented Mar 2, 2022 •

edited

Loading

obel1x commented Apr 15, 2022 •

edited

Loading

labor4 commented Jan 5, 2023 •

edited

Loading

nderambure commented Jan 5, 2023 •

edited

Loading

obel1x commented May 2, 2023 •

edited

Loading