Repository navigation
Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction #6899
Description
Activity
I'm affected by same issue, Nextcloud 12.0.3 with MariaDB/nginx and latest php 7.1.
Reacted by Max Schmitt, Michael Hamburger, baconeggsangwich and Alex LavertyI am also seeing this. Nextcloud 12.0.4 with MariaDB 10.2.10, PHP 7.1.12. Redis enabled for caching and locking.
Reacted by Michael HamburgerThis is affecting a fresh install using Nextcloud VM for me as well. I've seen that transaction isolated has been the cause of this message in the past, but mine is set to READ-COMMITED.
but mine is set to READ-COMMITED.
Yup, the VM is by the book. :)
I had to set mine to READ-COMMITTED manually.
Has anyone figured out the cause and fix?
I had to set mine to READ-COMMITTED manually.
Hmm, that shouldn't be the case.
I too am getting Deadlock errors consistently by using the nextcloud client.
Operating system: Nextcloud Docker
Nextcloud version: 12.0.5
PHP version: 7.1.13
Database: MariaDB 10.2.12I have also enabled File Locking via Redis.
The interesting thing is, that when I try to find the root cause of the Deadlock, the mysql database has not detected any deadlocks.
Normally executing SHOW ENGINE INNODB STATUS in the mysql database, outputs a section called "LATEST DETECTED DEADLOCK", which analyzes the two Quaries that lead to the Deadlock. But my database does not detect (or better said report) any Deadlock that is caused by the nextcloud client syncing.
On the other hand, when I try to make a manual Deadlock, it is reported as expected.Can anybody else test if their database detects/reports the Deadlock?
Hey Greek64,
Same for me - there's no entry in LATEST DETECTED DEADLOCK.
I'm wondering if this issue has something to do with READ_COMMITED being incompatible with row-based logging:
https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html
If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. It is possible to change the logging format to STATEMENT, but doing so at runtime leads very rapidly to errors because InnoDB can no longer perform inserts.Reading the Nextcloud docco, it's pretty straight up that READ COMMITTED is the only way to go:
https://docs.nextcloud.com/server/14/admin_manual/configuration_database/linux_database_configuration.html#db-binlog-label As discussed above Nextcloud is using the TRANSACTION_READ_COMMITTED transaction isolation level.
The other is to change the BINLOG_FORMAT = STATEMENT in your database configuration file, or possibly in your database startup script, to BINLOG_FORMAT = MIXED.
I might try disabling binary logging altogether and see how that goes. Will keep you posted.
@snozberries-ln
Yes, you have to disable/modify binary logging.
On my Database it was disabled by default, so I didn't think to mention it.I installed nextcloud using @enoch85's VM - which is really well put together and has been largely hands-free, aside from this minor issue (ty enoch for maintaining the repo!).
With binary logging enabled (which it was by default), I could trigger the issue very easily by copying a folder with ~150 odd photos into a Nextcloud sync folder. The photos would be uploaded, but a few files would not be saved by Nextcloud due to the deadlock error and would be re-uploaded. That's fine for photos that are a couple of MB, but would be murder for large videos or other files that are a couple of GB in size.
Disabling the binary logging has helped massively. I'm still getting the error, but it only triggers after uploading ~2,000 odd files. So binary logging wasn't the culprit, but disabling it has reduced the frequency of the deadlocks.
I'm a DB noob (hence using the Nextcloud VM auto-build repo), so I'm not exactly sure what to troubleshoot next...
Reacted by enoch85, Denis Paavilainen and Sergej PupykinJust hit this problem with NextCloud v12.0.5 backed by MySql 5.6.35 on Amazon RDS. In this case it is not an option to turn off binary logging as that appears to be a requirement for automated snapshot backup to operate.
I'm going to have to see if we can use a containerised version of MySql instead so that we have control of the binary logging and schedule sql dumps as backups instead.
This is obviously really far from ideal.
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2018-04-27 13:20:05 2b10e63fa700 *** (1) TRANSACTION: TRANSACTION 18854137, ACTIVE 0 sec inserting mysql tables in use 2, locked 1 LOCK WAIT 3 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1 MySQL thread id 1143054, OS thread handle 0x2b10fef03700, query id 12906640 10.0.1.60 archivematica executing INSERT INTO `oc_filecache` (`mimepart`,`mimetype`,`mtime`,`size`,`etag`,`storage_mtime`,`permissions`,`checksum`,`path_hash`,`path`,`parent`,`name`,`storage`) SELECT '1','2','1524745799','-1','5ae3238520699','1524745799','23','','d41d8cd98f00b204e9800998ecf8427e','','-1','','8' FROM `oc_filecache` WHERE `storage` = '8' AND `path_hash` = 'd41d8cd98f00b204e9800998ecf8427e' HAVING COUNT(*) = 0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 645 page no 4 n bits 112 index `fs_storage_path_hash` of table `NEXTCLOUD`.`oc_filecache` trx id 18854137 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 40 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000009; asc ;; 1: len 30; hex 306163393366383431313734373866373962306666653663653935326131; asc 0ac93f84117478f79b0ffe6ce952a1; (total 32 bytes); 2: len 4; hex 80000027; asc ';; *** (2) TRANSACTION: TRANSACTION 18853864, ACTIVE 2 sec setting auto-inc lock mysql tables in use 2, locked 2 7 lock struct(s), heap size 1184, 47 row lock(s), undo log entries 37 MySQL thread id 1143053, OS thread handle 0x2b10e63fa700, query id 12906644 10.0.1.60 archivematica executing INSERT INTO `oc_filecache` (`mimepart`,`mimetype`,`mtime`,`size`,`etag`,`storage_mtime`,`permissions`,`parent`,`checksum`,`path_hash`,`path`,`name`,`storage`) SELECT '3','9','1513616097','334066','ac824ddf86a0d72a14f203d719e3c58d','1513616097','27','39','','8848486f593b029ff33429a373c1a74d','data/RCM-reimport/10.1016/j.puhe.2015.12.005/PH16 v01.pdf','PH16 v01.pdf','9' FROM `oc_filecache` WHERE `storage` = '9' AND `path_hash` = '8848486f593b029ff33429a373c1a74d' HAVING COUNT(*) = 0 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 645 page no 4 n bits 80 index `fs_storage_path_hash` of table `NEXTCLOUD`.`oc_filecache` trx id 18853864 lock mode S locks gap before recI'm having the same problem:
Nextcloud 13.0.2.1
PHP 7.0.28
MariaDB 10.2
Binary Log disabled
Redis 4.0.9 (if i disable it, the problem is still there)I see deadlocks in the nextcloud logs but not in the Mysql command SHOW ENGINE INNODB STATUS
118 remaining items
@obel1x i tried before but same result
Still seeing these deadlock issues on 25.0.7, php 8.1.20, PostgreSQL 15.3. I'm due to update to version 26 in the near future, but i see others above still reporting it on that version.
While removing indexes could help solve speed issues, I don't see how that would affect the deadlock problem that was originally reported. That's due entirely to the order that writes are being done to the database by concurrent transactions. I run a very small server, so I'm seeing almost zero performance issues even with the locks, it just creates a mess in the logs. And for PostgreSQL at least, it's a rather serious problem because of the conflicting transactions, there's no guarantee which one of them actually succeeds.
[no app in context] Warning: OC\DB\Exceptions\DbalException: An exception occurred while executing a query: SQLSTATE[40P01]: Deadlock detected: 7 ERROR: deadlock detected DETAIL: Process 42820 waits for ShareLock on transaction 7552219; blocked by process 42818. Process 42818 waits for ShareLock on transaction 7552210; blocked by process 42820. HINT: See server log for query details. CONTEXT: while rechecking updated tuple (813,6) in relation "oc_filecache" at <<closure>> 0. /usr/local/www/apache24/nextcloud_2507/lib/private/DB/QueryBuilder/QueryBuilder.php line 329 OC\DB\Exceptions\DbalException::wrap(["Doctrine\\DBAL ... "]) 1. /usr/local/www/apache24/nextcloud_2507/lib/private/Files/Cache/Propagator.php line 137 OC\DB\QueryBuilder\QueryBuilder->executeStatement() 2. /usr/local/www/apache24/nextcloud_2507/lib/private/Files/Cache/HomePropagator.php line 48 OC\Files\Cache\Propagator->propagateChange("*** sensitive parameters replaced ***", 1686969556, 540) 3. /usr/local/www/apache24/nextcloud_2507/lib/private/Files/Cache/Updater.php line 144 OC\Files\Cache\HomePropagator->propagateChange("*** sensitive parameters replaced ***", 1686969556, 540) 4. /usr/local/www/apache24/nextcloud_2507/apps/dav/lib/Connector/Sabre/File.php line 368 OC\Files\Cache\Updater->update("*** sensitive parameters replaced ***") 5. /usr/local/www/apache24/nextcloud_2507/apps/dav/lib/Connector/Sabre/Directory.php line 151 OCA\DAV\Connector\Sabre\File->put(null) 6. /usr/local/www/apache24/nextcloud_2507/3rdparty/sabre/dav/lib/DAV/Server.php line 1098 OCA\DAV\Connector\Sabre\Directory->createFile("digits.c", null) 7. /usr/local/www/apache24/nextcloud_2507/3rdparty/sabre/dav/lib/DAV/CorePlugin.php line 504 Sabre\DAV\Server->createFile("files/tyriaan/m ... c", null, null) 8. /usr/local/www/apache24/nextcloud_2507/3rdparty/sabre/event/lib/WildcardEmitterTrait.php line 89 Sabre\DAV\CorePlugin->httpPut(["Sabre\\HTTP\\Request"], ["Sabre\\HTTP\\Response"]) 9. /usr/local/www/apache24/nextcloud_2507/3rdparty/sabre/dav/lib/DAV/Server.php line 472 Sabre\DAV\Server->emit("method:PUT", [["Sabre\\HTTP\\ ... ]]) 10. /usr/local/www/apache24/nextcloud_2507/3rdparty/sabre/dav/lib/DAV/Server.php line 253 Sabre\DAV\Server->invokeMethod(["Sabre\\HTTP\\Request"], ["Sabre\\HTTP\\Response"]) 11. /usr/local/www/apache24/nextcloud_2507/3rdparty/sabre/dav/lib/DAV/Server.php line 321 Sabre\DAV\Server->start() 12. /usr/local/www/apache24/nextcloud_2507/apps/dav/lib/Server.php line 360 Sabre\DAV\Server->exec() 13. /usr/local/www/apache24/nextcloud_2507/apps/dav/appinfo/v2/remote.php line 35 OCA\DAV\Server->exec() 14. /usr/local/www/apache24/nextcloud_2507/remote.php line 172 require_once("/usr/local/www/ ... p")Hi, there have been patches to address this issue that will land in 26.0.3. Can you please verify if 26.0.3-rc1 improves the situation around this issue?
Just curious if you mean this patch?
I'm not that familiar with this DB library in php, but just wondering if this is just ignoring the deadlock report from the database or actually preventing it? If not the latter, while this might remove the error from the nextcloud/php logs, you're not actually preventing the deadlock and the error will still spam the database logs and cause unpredictable behavior.
Reacted by Adam ChristieReacted by Daniel Scharon@keithf4 I'm not certain which PRs @szaimen was referring to specifically, but, yes, those would be two relevant ones from the looks of them.
The referenced PRs look to be for a specific case where
mtime(modify time), for the same folder, is being updated by multiple concurrent requests. It makes little sense to hard fail (nor wait or retry further) on a deadlock in this scenario since they'll effectively cancel each other out (that's my interpretation anyway - I was not involved in the implementation):// with failures concurrent updates, someone else would have already done it.` // in the worst case the storage_mtime isn't updated, which should at most only trigger an extra rescanAlso, one of those PRs adds explicit logging (at the WARN level) when this happens. The logs for this particular case will be cleaner (i.e. no repeated unhandled exceptions and now only at a specific loglevel so there's some flexibility/control).
Progress is often incremental. As this thread has shown, there are multiple contributing factors and therefore multiple non-mutually exclusive angles to attack this from. Reducing the impact of scenarios where deadlocks still can arise (where possible) is one such angle of attack. Obviously in addition to the other angle: reducing their frequency.
The referenced PRs are an example of the former by my read.
Reacted by Simon L. and Julius KnorrReacted by Simon L., Julius Knorr and AnnaAppreciate that that is what it's doing and may help mitigate that it keeps retrying more often, but just on an initial read it simply looked like it was reclassifying the error and passing on it which was very concerning. While I hope it improves things, the source of the problem seems much more complex as noted below and part of me is a little concerned that it's not making clear in the Nextcloud logs that an actual deadlock event is happening.
Hi, there have been patches to address this issue that will land in 26.0.3. Can you please verify if 26.0.3-rc1 improves the situation around this issue?
has this patch been implemented in the version 27? I have a clear new install and I have this issue still.
@Badb0yBadb0y Yes, the recently referenced PRs in this thread are in NC27. Keep in mind #6899 (comment) - there are various circumstances deadlocks can occur under. These PRs are incremental improvements. You may still encounter deadlocks under various circumstances.
P.S. Also, which PRs appear in which releases can be found at: https://nextcloud.com/changelog/
Seems like my issue was a galera cluster behind haproxy, I had to set 1 node out of the 2 as backup so can't happen double write as stated in one of the owncloud documentation.
This issue has been automatically marked as stale because it has not had recent activity and seems to be missing some essential information. It will be closed if no further activity occurs. Thank you for your contributions.
Are there any fixes to this issue?
My instance now has a deadlock and I don't know how to fix the issue. I'm unable to delete files from a folder.
After 1 year without issues and now having reached version 29.0.5, I decided to recreate the original structure of the oc_filecache table and test using the same sample with 20,000 small ico files.
Immediately, DeadlockException, Serialization failure messages started again... as well as corrupted uploads.
This happens on all three of our production servers (Debian 11, Apache 2.4.56, PHP 8.2, MariaDB 10.5.21)
The only workaround that works for me is deleting the indexes as shown in post #6899 (comment)
Specifically, the only three indexes I have now in table oc_filecache are the following:
PRIMARY KEY USING BTREE (fileid),
UNIQUE KEYfs_storage_path_hashUSING BTREE (storage,path_hash),
KEYfs_parentUSING BTREE (parent)Reacted by GSchenck and Kin
Steps to reproduce
Expected behaviour
No error
Actual behaviour
Server configuration
Operating system: Debian 9.1 PXE:
Web server: nginx/1.10.3
Database: MariaDB
PHP version: 7.1.9
Nextcloud version: 12.0.3
Updated from an older Nextcloud/ownCloud or fresh install: Fresh Install
Where did you install Nextcloud from: https://download.nextcloud.com/server/releases/nextcloud-12.0.3.zip
Signing status:
Signing status
No errors have been found.List of activated apps:
App list
Nextcloud configuration:
Config report
Are you using external storage, if yes which one: local/smb/sftp/.. No.
Are you using encryption: No
Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/... Only webdav
Client configuration
Browser: Chrome 62
Operating system: Windows 7 Enterprise
Logs
Web server error log
Web server error log
Nextcloud log (data/nextcloud.log)
Nextcloud log
Browser log
Not relevant