search

zodb / relstorage

A backend for ZODB that stores pickles in a relational database.
Other
54 stars 46 forks source link

ORA-08177: can't serialize access for this transaction #449

Closed NicolasGoeddel closed 1 year ago

NicolasGoeddel commented 3 years ago

After deleting all the tables, sequences and packages from my oracle schema and starting Plone in foreground I get this error:

Traceback (most recent call last):
  File "/home/nicolas/plone/instance/parts/client1/bin/interpreter", line 307, in <module>
    exec(compile(__file__f.read(), __file__, "exec"))
  File "/home/nicolas/plone/buildout-cache/eggs/Zope-4.5.1-py3.8.egg/Zope2/Startup/serve.py", line 255, in <module>
    sys.exit(main() or 0)
  File "/home/nicolas/plone/buildout-cache/eggs/Zope-4.5.1-py3.8.egg/Zope2/Startup/serve.py", line 251, in main
    return command.run()
  File "/home/nicolas/plone/buildout-cache/eggs/Zope-4.5.1-py3.8.egg/Zope2/Startup/serve.py", line 189, in run
    app = self.loadapp(app_spec, name=app_name, relative_to=base,
  File "/home/nicolas/plone/buildout-cache/eggs/Zope-4.5.1-py3.8.egg/Zope2/Startup/serve.py", line 220, in loadapp
    return loadapp(app_spec, name=name, relative_to=relative_to, **kw)
  File "/home/nicolas/plone/buildout-cache/eggs/PasteDeploy-2.1.0-py3.8.egg/paste/deploy/loadwsgi.py", line 253, in loadapp
    return loadobj(APP, uri, name=name, **kw)
  File "/home/nicolas/plone/buildout-cache/eggs/PasteDeploy-2.1.0-py3.8.egg/paste/deploy/loadwsgi.py", line 278, in loadobj
    return context.create()
  File "/home/nicolas/plone/buildout-cache/eggs/PasteDeploy-2.1.0-py3.8.egg/paste/deploy/loadwsgi.py", line 715, in create
    return self.object_type.invoke(self)
  File "/home/nicolas/plone/buildout-cache/eggs/PasteDeploy-2.1.0-py3.8.egg/paste/deploy/loadwsgi.py", line 209, in invoke
    app = context.app_context.create()
  File "/home/nicolas/plone/buildout-cache/eggs/PasteDeploy-2.1.0-py3.8.egg/paste/deploy/loadwsgi.py", line 715, in create
    return self.object_type.invoke(self)
  File "/home/nicolas/plone/buildout-cache/eggs/PasteDeploy-2.1.0-py3.8.egg/paste/deploy/loadwsgi.py", line 152, in invoke
    return fix_call(context.object, context.global_conf, **context.local_conf)
  File "/home/nicolas/plone/buildout-cache/eggs/PasteDeploy-2.1.0-py3.8.egg/paste/deploy/util.py", line 55, in fix_call
    val = callable(*args, **kw)
  File "/home/nicolas/plone/buildout-cache/eggs/Zope-4.5.1-py3.8.egg/Zope2/Startup/run.py", line 71, in make_wsgi_app
    starter.prepare()
  File "/home/nicolas/plone/buildout-cache/eggs/Zope-4.5.1-py3.8.egg/Zope2/Startup/starter.py", line 41, in prepare
    self.startZope()
  File "/home/nicolas/plone/buildout-cache/eggs/Zope-4.5.1-py3.8.egg/Zope2/Startup/starter.py", line 99, in startZope
    Zope2.startup_wsgi()
  File "/home/nicolas/plone/buildout-cache/eggs/Zope-4.5.1-py3.8.egg/Zope2/__init__.py", line 50, in startup_wsgi
    _startup()
  File "/home/nicolas/plone/buildout-cache/eggs/Zope-4.5.1-py3.8.egg/Zope2/App/startup.py", line 117, in startup
    DB = dbtab.getDatabase('/', is_root=1)
  File "/home/nicolas/plone/buildout-cache/eggs/Zope-4.5.1-py3.8.egg/Zope2/Startup/datatypes.py", line 266, in getDatabase
    db = factory.open(name, self.databases)
  File "/home/nicolas/plone/buildout-cache/eggs/Zope-4.5.1-py3.8.egg/Zope2/Startup/datatypes.py", line 159, in open
    DB = self.createDB(database_name, databases)
  File "/home/nicolas/plone/buildout-cache/eggs/Zope-4.5.1-py3.8.egg/Zope2/Startup/datatypes.py", line 156, in createDB
    return ZODBDatabase.open(self, databases)
  File "/home/nicolas/plone/buildout-cache/eggs/ZODB-5.6.0-py3.8.egg/ZODB/config.py", line 146, in open
    return ZODB.DB(
  File "/home/nicolas/plone/buildout-cache/eggs/ZODB-5.6.0-py3.8.egg/ZODB/DB.py", line 470, in __init__
    conn.get(z64)
  File "/home/nicolas/plone/buildout-cache/eggs/ZODB-5.6.0-py3.8.egg/ZODB/Connection.py", line 247, in get
    p, _ = self._storage.load(oid)
  File "src/perfmetrics/metric.py", line 66, in perfmetrics._metric._AbstractMetricImpl.__call__
  File "/home/nicolas/plone/buildout-cache/eggs/RelStorage-3.3.2-py3.8-linux-x86_64.egg/relstorage/storage/load.py", line 107, in load
    state, tid_int = self.__load_using_method(load_cursor, self.cache.load, oid_int)
  File "/home/nicolas/plone/buildout-cache/eggs/RelStorage-3.3.2-py3.8-linux-x86_64.egg/relstorage/storage/load.py", line 83, in __load_using_method
    return meth(load_cursor, argument)
  File "/home/nicolas/plone/buildout-cache/eggs/RelStorage-3.3.2-py3.8-linux-x86_64.egg/relstorage/cache/storage_cache.py", line 425, in load
    return self.adapter.mover.load_current(cursor, oid_int)
  File "src/perfmetrics/metric.py", line 66, in perfmetrics._metric._AbstractMetricImpl.__call__
  File "/home/nicolas/plone/buildout-cache/eggs/RelStorage-3.3.2-py3.8-linux-x86_64.egg/relstorage/adapters/mover.py", line 81, in load_current
    stmt.execute(cursor, (oid,))
  File "/home/nicolas/plone/buildout-cache/eggs/RelStorage-3.3.2-py3.8-linux-x86_64.egg/relstorage/adapters/sql/query.py", line 221, in execute
    cursor.execute(stmt, params)
cx_Oracle.OperationalError: ORA-00604: error occurred at recursive SQL level 1
ORA-08177: can't serialize access for this transaction

However if I start Plone a second time it works without a problem. I tried RelStorage in version 3.3.2 and 3.4.0. Plone was installed using the UnifiedInstaller for Version 5.2.2 on a CentOS docker image.

Any idea what the reason what that is? And what can I do about it if I want to automate the startup process of the docker build?

jamadden commented 3 years ago

Sorry for the trouble. I have to tell you that Oracle+RelStorage is barely supported and not regularly tested. If possible, I encourage people to migrate to PostgreSQL 11+.

However if I start Plone a second time it works without a problem

Do you mean, you stop plone and just start it again? Or you start a second instance? I'm not sure what difference it makes because I don't know why you would be getting this.

Once you start a second instance, the trouble disappears for good?

ORA-08177: can't serialize access for this transaction

IIRC, the error you're getting means that there are multiple transactions in progress that conflict. I don't know enough about Zope/Plone startup to guess why that would be. Normally I wouldn't expect that. Perhaps you're starting multiple worker processes at the same time?

Especially here, when it's just trying to load the root object conn.get(z64).

Googling suggests that this may be a problem related to particular versions of Oracle and how it handles the undo segments. If that's the case, simply connecting to the database from a single client (ZODB.config.open(…)) before attempting to start multiple processes/threads ought to initialize the rollback segments and solve the issue.

NicolasGoeddel commented 3 years ago

I start just one instance (./bin/client1 fg), nothing more. However I think one client usually spawns multiple worker threads. I am assuming that the error only happens while the database is created. After that it works normal.

We have a customer who wishes to use its Oracle database, so we do it with our application. I don't think we can switch to an other database backend now. For other projects we usually use Postgres as well.

jamadden commented 3 years ago

I am assuming that the error only happens while the database is created. After that it works normal.

I don't know what to suggest, other than what I suggested before (open a "pre-flight" connection).

jamadden commented 1 year ago

Closing; no updates since 2021.