Repository navigation
race condition in insertOrUpdate queries #22563
Description
race condition in insertOrUpdate queries (completely reworked in 9.0 - try to curricumvent them in stable8.1/2 code too) - the reason for this is that those queries are technically two queries (there is a subquery) and if a parallel insert happens after the subquery is executed this could happen
error message:
{"Message":"An exception occurred while executing 'INSERT INTO "oc_preferences" ("configvalue","userid","appid","configkey") SELECT ?,?,?,? FROM "oc_preferences" WHERE "configkey" = ? AND "userid" = ? AND "appid" = ? HAVING COUNT(*) = 0' with params [1455358499.591, "foo@bar.baz", "files_sharing", "last_propagate", "last_propagate", "foo@bar.baz", "files_sharing"]:
SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "oc_preferences_pkey"
DETAIL: Key (userid, appid, configkey)=(foo@bar.baz, files_sharing, last_propagate) already exists.","Code":0,"Trace":"
#0 3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(116): Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException('An exception oc...', Object(Doctrine\DBAL\Driver\PDOException))
#1 3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(996): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDOPgSql\Driver), Object(Doctrine\DBAL\Driver\PDOException), 'INSERT INTO "oc...', Array)
#2 lib/private/db/connection.php(151): Doctrine\DBAL\Connection->executeUpdate('INSERT INTO "oc...', Array, Array)
#3 lib/private/db/adapter.php(93): OC\DB\Connection->executeUpdate('INSERT INTO `*P...', Array)
#4 lib/private/db/connection.php(190): OC\DB\Adapter->insertIfNotExist('*PREFIX*prefere...', Array, Array)
#5 lib/private/allconfig.php(217): OC\DB\Connection->insertIfNotExist('*PREFIX*prefere...', Array, Array)
#6 apps/files_sharing/lib/propagation/recipientpropagator.php(83): OC\AllConfig->setUserValue('foo@bar.baz', 'files_sharing', 'last_propagate', 1455358499.591)
#7 apps/files_sharing/lib/mountprovider.php(64): OCA\Files_Sharing\Propagation\RecipientPropagator->propagateDirtyMountPoints(Array)
#8 lib/private/files/config/mountprovidercollection.php(61): OCA\Files_Sharing\MountProvider->getMountsForUser(Object(OC\User\User), Object(OC\Files\Storage\StorageFactory))
#9 [internal function]: OC\Files\Config\MountProviderCollection->OC\Files\Config\{closure}(Object(OCA\Files_Sharing\MountProvider))
#10 lib/private/files/config/mountprovidercollection.php(62): array_map(Object(Closure), Array)
#11 lib/private/files/filesystem.php(425): OC\Files\Config\MountProviderCollection->getMountsForUser(Object(OC\User\User))
#12 apps/files_sharing/lib/cache.php(67): OC\Files\Filesystem::initMountPoints('foo@bar.baz')
#13 apps/files_sharing/lib/cache.php(104): OC\Files\Cache\Shared_Cache->getSourceCache('')
#14 lib/private/files/view.php(1215): OC\Files\Cache\Shared_Cache->get('')
#15 apps/files/appinfo/remote.php(59): OC\Files\View->getFileInfo('')
#16 [internal function]: {closure}(Object(Sabre\HTTP\Request), Object(Sabre\HTTP\Response))
#17 3rdparty/sabre/event/lib/EventEmitterTrait.php(105): call_user_func_array(Object(Closure), Array)
#18 3rdparty/sabre/dav/lib/DAV/Server.php(456): Sabre\Event\EventEmitter->emit('beforeMethod', Array)
#19 3rdparty/sabre/dav/lib/DAV/Server.php(254): Sabre\DAV\Server->invokeMethod(Object(Sabre\HTTP\Request), Object(Sabre\HTTP\Response))
#20 apps/files/appinfo/remote.php(83): Sabre\DAV\Server->exec()
#21 remote.php(132): require_once('/var/www/ownclo...')
#22 {main}","File":"3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php","Line":63}
and:
error while running hook (OCA\Files_Versions\Hooks::write_hook): An exception occurred while executing 'INSERT INTO "oc_filecache" ("mimepart","mimetype","mtime","size","etag","storage_mtime","permissions","parent","path_hash","path","name","storage") SELECT ?,?,?,?,?,?,?,?,?,?,?,? FROM "oc_filecache" WHERE "storage" = ? AND "path_hash" = ? HAVING COUNT(*) = 0' with params [1, 2, 1455369346, -1, "56bf2c8286739", 1455369346, 31, 92362664, "6b25e3ed6d9bb265731382b35b1d54ff", "files_versions\/data\/scripts\/dtc", "dtc", 17184, 17184, "6b25e3ed6d9bb265731382b35b1d54ff"]:
SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "fs_storage_path_hash"
DETAIL: Key (storage, path_hash)=(17184, 6b25e3ed6d9bb265731382b35b1d54ff) already exists.
Since we implemented a non standard insertIfNotExists we cannot fix this by db specific SQL extensions. While the above use cases are fixed for OC 9 because the code for the propagation has changed other code paths using the current insertIfNotExists may run into this race condition.
Depending on the not exists parameter this race condition might throw the uniqe index violation error. Adding the primary key to the conditions is not always possible, would change the semantics of the query and we could still run into the race condition. It might be possible to use DB specific SQL extensions to ignore the error. Or we just catch the Exception and log it as a warning?
Activity
nickvergessen commentedon Feb 22, 2016
cc @icewind1991
PVince81 commentedon Mar 18, 2016
How about this ? ^
But then, what if two processes are trying to write two different values there instead of the same one ? Which one is allowed to legimitately fail ?
71 remaining items
mrow4a commentedon Apr 3, 2018
@guruz I think I once tried it in some PR, and it did not work on sqlite properly.
PVince81 commentedon Apr 25, 2018
moving to "triage" for rescheduling
butonic commentedon Feb 25, 2021
upsert was implemented in #28698, which got merged long ago. closing.