concurent queries

[alex-eri]

Peewee recomends to conect() just before request and close() after executing user code.

i.e. from documentation:
http://docs.peewee-orm.com/en/latest/peewee/database.html#creating-a-database-connection-and-tables

database = SqliteDatabase('my_app.db')

def before_request_handler():
    database.connect()

def after_request_handler():
    database.close()

On async app handlers are coroitines and requests can be processed while another one waiting bigger data.

Is there way to get another connection from pool and do requsts on it instead of using shared database connection?

Context manager maybe?

related #53

[alex-eri]

Managing your database connections is as simple as calling connect() when you need to open a connection, and close() when you are finished. In a web-app, you would typically connect when you receive a request, and close the connection when you return a response. Because connection state is stored in a thread-local, you do not need to worry about juggling connection objects – peewee will handle it for you.

Is coroutines runnind in one single thread?

[rudyryk]

@alex-eri If you're using pooled connection driver it internally does acquire connection per request (or per transaction if used). Yes, all connections live on a single thread.

[alex-eri]

Are coroutines (not about only driver, my or server asyncs) running in single thread? If they single threaded - it uses one connection.

So i need context processor db.execution_context() or not?

[alex-eri]

I asked https://ru.stackoverflow.com/a/720586/16574 (in russian)
An event loop runs in a thread and executes all callbacks and tasks in the same thread.

Then we acquire connection it stored in thread-local, acquire new connection replaces old. If second call comes before first executes query, then first and second query runs in second connection.
Seems it happened in #53.

So creating connections as db.connect() not safe.

We need rewrite connections to use db.execution_context() API. It stored in variable, not in thread storage.
http://docs.peewee-orm.com/en/latest/peewee/database.html#advanced-connection-management

[rudyryk]

@alex-eri Not sure if I understand you - It's not really related to ORM itself. There are several async database drivers, i.e. aiopg - it runs many connections on single asyncio loop and is fine with that and we just wrap that functionality into ORM. And we don't use thread-locals of course, we have asyncio-task based context when necessary.

Then we acquire connection it stored in thread-local

Where did you see this?

[rudyryk]

So creating connections as db.connect() not safe.

And we don't do so (well, supposed not to do so - or there's a bug somewhere). For PosgreSQL we create all coonections via aiopg.create_pool() call, like here: https://aiopg.readthedocs.io/en/stable/core.html#pool

[alex-eri]

i know about pool.

pool used as (pseudocode)

con = await db.conect()
con.execute(...)

ORM used (pseudocode)

await db.connect()
db.execute()

So aquired connection stored in db instance and shared to all coroutines, tasks.

Then we acquire connection it stored in thread-local

Where did you see this?

peewee docs

Where connection stored before using it?

[rudyryk]

@alex-eri We don't normally manage storing connections, they are stored under the hood of async driver, like that:

@asyncio.coroutine
def acquire(self):
    """Acquire connection from pool.
    """
    return (yield from self.pool.acquire())

Our "database" class for PostgreSQL is actually a wrapper around connection pool from aiopg and that lib stores connections internally.

So normally we're just acquiring a new connection for every request and don't store them anywhere, see _run_sql()

peewee docs

We don't use peewee database management logic, we have our own methods and don't execute original peewee's connect() etc.

Could you provide some code to demonstrate your concerns?

[alex-eri]

So i dont need to connect() and close() connect in every request as in sync peewee?

[alex-eri]

looking at _run_sql. it runs database.cursor_async(). it run self.connect_async(). there it stores connection in self._async_conn. i.e. on database instance, which is shared.

Connection can be replaced by another call while first running.

Code is not thread-safe (sync peewee is thread-safe). BTW only thead-safe is not
sufficient with asyncio. One "await" switches context.

We need to remove _async_conn attribute and pass it as argument through.

[rudyryk]

On the line 875 we have:

       if self._async_conn:
            return

So connect_async() is called only once to initialize a connection pool. AsyncDatabase class is not the same as in original peewee, it's just a single instance per application (usually) - wrapper to real async database driver which handles multiple connections under the hood.

[alex-eri]

Okey. Thanks alot for disribes!