SQLite still isn't suitable for production with oTree Lite

[anders...@gmail.com]

It is stated in the new docs that "in principle, oTree 3.0+ should do fine with its default SQLite in production, since the server is now is single threaded." We tested it in your new 24-person experiment and got a lot of database-locked errors. We reverted back to postgresql and errors are gone. So even though otree 5+ is single threaded, you still will run into database locked issues when a lot people are submitting forms simultaneously.

That said, in the new server setup docs, instructions on how to setup postgresql database (create otree databases and users, etc) are gone. Is there any place I can find the old instructions?

Ming

[Chris @ oTree]

Thanks for sharing this. It’s a bit curious to me that this error happened because I have never seen a database lock with otree 5 even though I have run browser bots that submit lots of pages simultaneously.  Do you have a traceback I can look at?

How are you starting the server ? Are you using a process manager that starts multiple processes?

Sent from my phone

On Apr 23, 2021, at 10:42 AM, anders...@gmail.com <anders...@gmail.com> wrote:

It is stated in the new docs that "in principle, oTree 3.0+ should do fine with its default SQLite in production, since the server is now is single threaded." We tested it in your new 24-person experiment and got a lot of database-locked errors. We reverted back to postgresql and errors are gone. So even though otree 5+ is single threaded, you still will run into database locked issues when a lot people are submitting forms simultaneously.

That said, in the new server setup docs, instructions on how to setup postgresql database (create otree databases and users, etc) are gone. Is there any place I can find the old instructions?

Ming

--
You received this message because you are subscribed to the Google Groups "oTree help & discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to otree+un...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/otree/7341466c-6ca2-47f6-989c-c1fa8a0c83c4n%40googlegroups.com.

[anders...@gmail.com]

I simply installed latest otree, set all required environmental variables, and started otree with prodserver. I used to also install postgres and redis.

What happened was that on subject screens they saw http 500 errors; on the console it showed database locked errors thrown out by sqlalchemy stack. I asked them to refresh their browsers in waves, and some of them can continue the experiment.

[anders...@gmail.com]

I'm runnning otree in ubuntu 20.04 lxc containers.

[Jannik Greif]

We did an experiment with 20 people and did run into database is locked errors as well. We rerun the experiment and the error still occured in the second try for one of the groups. We are running otree 5.2.7.

This was the traceback we got if it helps:

27.0.0.1:42452 - "GET /p/ypvhih66/payment_info/PaymentInfo/253 HTTP/1.1" 500 Internal Server Error
Exception("Error occurred when opening http://127.0.0.1:8004/p/ypvhih66/payment_info/PaymentInfo/253: <HTTPError 500:                   'Internal Server Error'>")
Traceback (most recent call last):
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/tasks.py", line 57, in listen
    getattr(self, task.method)(**task.kwargs())
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/tasks.py", line 120, in ensure_pages_visited
    get(urljoin(self.base_url, path))
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/tasks.py", line 34, in get
    raise Exception(f'Error occurred when opening {url}: {repr(exc)}') from None
Exception: Error occurred when opening http://127.0.0.1:8004/p/ypvhih66/payment_info/PaymentInfo/253: <HTTPError 500:                   'Internal Server Error'>
Exception in ASGI application
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 609, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: database is locked

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/antisocial/.local/lib/python3.8/site-packages/uvicorn/protocols/http/h11_impl.py", line 396, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/home/antisocial/.local/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 45, in __call__
    return await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/dist-packages/starlette/applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/usr/local/lib/python3.8/dist-packages/starlette/middleware/base.py", line 26, in __call__
    await response(scope, receive, send)
  File "/usr/local/lib/python3.8/dist-packages/starlette/responses.py", line 224, in __call__
    await run_until_first_complete(
  File "/usr/local/lib/python3.8/dist-packages/starlette/concurrency.py", line 24, in run_until_first_complete
    [task.result() for task in done]
  File "/usr/local/lib/python3.8/dist-packages/starlette/concurrency.py", line 24, in <listcomp>
    [task.result() for task in done]
  File "/usr/local/lib/python3.8/dist-packages/starlette/responses.py", line 216, in stream_response
    async for chunk in self.body_iterator:
  File "/usr/local/lib/python3.8/dist-packages/starlette/middleware/base.py", line 56, in body_stream
    task.result()
  File "/usr/local/lib/python3.8/dist-packages/starlette/middleware/base.py", line 38, in coro
    await self.app(scope, receive, send)
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/errorpage.py", line 247, in __call__
    raise exc  # from None
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/errorpage.py", line 227, in __call__
    await self.app(scope, receive, _send)
  File "/usr/local/lib/python3.8/dist-packages/starlette/middleware/base.py", line 25, in __call__
    response = await self.dispatch_func(request, self.call_next)
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/middleware.py", line 42, in dispatch
    response = await call_next(request)
  File "/usr/local/lib/python3.8/dist-packages/starlette/middleware/base.py", line 45, in call_next
    task.result()
  File "/usr/local/lib/python3.8/dist-packages/starlette/middleware/base.py", line 38, in coro
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/dist-packages/starlette/middleware/sessions.py", line 75, in __call__
    await self.app(scope, receive, send_wrapper)
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/patch.py", line 41, in __call__
    raise exc  # from None
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/patch.py", line 29, in __call__
    await self.app(scope, receive, sender)
  File "/usr/local/lib/python3.8/dist-packages/starlette/routing.py", line 582, in __call__
    await route.handle(scope, receive, send)
  File "/usr/local/lib/python3.8/dist-packages/starlette/routing.py", line 243, in handle
    await self.app(scope, receive, send)
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/views/abstract.py", line 109, in dispatch
    response = await run_in_threadpool(self.inner_dispatch, request)
  File "/usr/local/lib/python3.8/dist-packages/starlette/concurrency.py", line 40, in run_in_threadpool
    return await loop.run_in_executor(None, func, *args)
  File "/usr/lib/python3.8/concurrent/futures/thread.py", line 57, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/views/abstract.py", line 401, in inner_dispatch
    return self.get()
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/views/abstract.py", line 459, in get
    obj = self.get_object()
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/views/abstract.py", line 490, in get_object
    'group': self.group,
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/views/abstract.py", line 218, in group
    return self.player.group
  File "/home/antisocial/.local/lib/python3.8/site-packages/otree/database.py", line 539, in __getattribute__
    res = super().__getattribute__(attr)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/attributes.py", line 294, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/attributes.py", line 730, in get
    value = self.callable_(state, passive)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/strategies.py", line 759, in _load_for_state
    return self._emit_lazyload(
  File "<string>", line 1, in <lambda>
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/strategies.py", line 847, in _emit_lazyload
    q(session)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/ext/baked.py", line 615, in _load_on_pk_identity
    result = list(bq.for_session(self.session).params(**params))
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/ext/baked.py", line 439, in __iter__
    self.session._autoflush()
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 1633, in _autoflush
    util.raise_(e, with_traceback=sys.exc_info()[2])
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 1622, in _autoflush
    self.flush()
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 2540, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 2682, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 2642, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/unitofwork.py", line 586, in execute
    persistence.save_obj(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/persistence.py", line 230, in save_obj
    _emit_update_statements(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/persistence.py", line 994, in _emit_update_statements
    c = cached_connections[connection].execute(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 609, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush                   block if this flush is occurring prematurely)
(sqlite3.OperationalError) database is locked
[SQL: UPDATE otree_participant SET _last_request_timestamp=? WHERE otree_participant.id = ?]
[parameters: (1635508774, 5)]
(Background on this error at: http://sqlalche.me/e/13/e3q8)