Locking method used in SQLAlchemy (postgres)

[gbr]

Hi,

I'm using SQLAlchemy's Core to interface a postgres database (via psycopg2) component alongside Flask-SQLAlchemy in a Flask app. Everything was working fine until I recently discovered what seems to be a deadlock state which is caused by two queries locking each other (at least that's my working hypothesis).

Postgres offers a wide range of locking mechanisms, so I wonder which one is used by SQLAlchemy (i.e. where is it set?).

This is how the database engine is being created (through Flask-SQLAlchemy.

```

db = sqlalchemy.create_engine(...)

# `db` reference is reused in process

# this is how queries are executed

rv = db.session.execute(select(...))

```

Is there any way I can check the transaction handling/locking used?

[Mike Bayer]

SQLAlchemy doesn't do anything explicit as far as pessimistic locking, this comes down to the isolation level that is set on the current transaction and by default it makes a psycopg2 connection and does not change anything about it.

so to get an intro to what happens when you make a psycopg2 connection you can look at https://www.psycopg.org/docs/usage.html?highlight=locking#transactions-control, which then gets into the question of isolation levels at https://www.postgresql.org/docs/current/transaction-iso.html .

On the SQLAlchemy side, the isolation level of your psycopg2 connection is set using the isolation level execution option, which you can see documented at https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#transaction-isolation-level .      to view the current isolation level as retrieved from the database, use get_isolation_level(): https://docs.sqlalchemy.org/en/13/core/connections.html#sqlalchemy.engine.Connection.get_isolation_level .

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4825d83c-688a-4f7a-a30d-6a69e5d3341do%40googlegroups.com.

[Jonathan Vanasco]

On Monday, June 29, 2020 at 8:00:40 PM UTC-4, gbr wrote:

I'm using SQLAlchemy's Core to interface a postgres database (via psycopg2) component alongside Flask-SQLAlchemy in a Flask app. Everything was working fine until I recently discovered what seems to be a deadlock state which is caused by two queries locking each other (at least that's my working hypothesis).

Beyond what Mike said... I don't use Flask but I use Pyramid and Twisted.

The only times I have experienced locking issues with SQLAlchemy:

* unit tests: the setup uses a first db connection, but it is erroneously implemented and not closed. when test runners begin, the db is locked so everything fails.

* application design issues: if you deploy a forking server and don't reset the pool on fork (`engine.dispose()`), all sorts of integrity and locking issues pop up (multiple processes try to use a single connection which never closes properly).  if you don't have a proper connection checkout/return that can happen too.

* very high concurrency: a pileup of connections want to lock for update/insert. inevitably, some timeout and deadlock.