README#

sqlalchemy-dlock#

sqlalchemy-dlock is a distributed-lock library based on Database and SQLAlchemy.

It currently supports below locks:

Database	Lock
MySQL	named lock
PostgreSQL	advisory lock

Install#

pip install sqlalchemy-dlock

Usage#

Work with SQLAlchemy Connection:

from sqlalchemy import create_engine
from sqlalchemy_dlock import create_sadlock

key = 'user/001'

engine = create_engine('postgresql://scott:tiger@127.0.0.1/')
conn = engine.connect()

# Create the D-Lock on the connection
lock = create_sadlock(conn, key)

# it's not lock when constructed
assert not lock.locked

# lock
lock.acquire()
assert lock.locked

# un-lock
lock.release()
assert not lock.locked

with statement

from contextlib import closing

from sqlalchemy import create_engine
from sqlalchemy_dlock import create_sadlock

key = 'user/001'

engine = create_engine('postgresql://scott:tiger@127.0.0.1/')
with engine.connect() as conn:

    # Create the D-Lock on the connection
    with create_sadlock(conn, key) as lock:
        # It's locked
        assert lock.locked

    # Auto un-locked
    assert not lock.locked

    # If do not want to be locked in `with`, a `closing` wrapper may help
    with closing(create_sadlock(conn, key)) as lock2:
        # It's NOT locked here !!!
        assert not lock2.locked
        # lock it now:
        lock2.acquire()
        assert lock2.locked

    # Auto un-locked
    assert not lock2.locked

Work with SQLAlchemy ORM Session:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy_dlock import create_sadlock

key = 'user/001'

engine = create_engine('postgresql://scott:tiger@127.0.0.1/')
Session = sessionmaker(bind=engine)

with Session() as session:
  with create_sadlock(session, key) as lock:
      assert lock.locked
  assert not lock.locked

Asynchronous I/O Support

NOTE

SQLAlchemy 1.x’s asynchronous I/O: https://docs.sqlalchemy.org/14/orm/extensions/asyncio.html

SQLAlchemy 2.x’s asynchronous I/O: https://docs.sqlalchemy.org/20/orm/extensions/asyncio.html

from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy_dlock.asyncio import create_async_sadlock

key = 'user/001'

engine = create_async_engine('postgresql+asyncpg://scott:tiger@127.0.0.1/')

async with engine.connect() as conn:
    async with create_async_sadlock(conn, key) as lock:
        assert lock.locked
        await lock.release()
        assert not lock.locked
        await lock.acquire()
    assert not lock.locked

NOTE

aiomysql, asyncpg and psycopg are tested asynchronous drivers.

We can install it with asynchronous DB libraries:
pip install SQLAlchemy[asyncio] aiomysql sqlalchemy-dlock
or
pip install SQLAlchemy[asyncio] asyncpg sqlalchemy-dlock

Test#

Following drivers are tested:

MySQL:
- mysqlclient (synchronous)
- pymysql (synchronous)
- aiomysql (asynchronous)
Postgres:
- psycopg2 (synchronous)
- asyncpg (asynchronous)
- psycopg (synchronous and asynchronous)

You can run unit-tests

on local environment:
1. Install the project in editable mode with asyncio optional dependencies, and libraries/drivers needed in test. A virtual environment (venv) is strongly advised:
```
pip install -e .[asyncio] -r tests/requirements.txt
```
2. start up mysql and postgresql service
  
  There is a docker compose file db.docker-compose.yml in project’s top directory, which can be used to run mysql and postgresql develop environment conveniently:
```
docker compose -f db.docker-compose.yml up
```
3. set environment variables TEST_URLS and TEST_ASYNC_URLS for sync and async database connection url. Multiple connections separated by space.
  
  eg: (following values are also the defaults, and can be omitted)
```
TEST_URLS=mysql://test:test@127.0.0.1/test postgresql://postgres:test@127.0.0.1/
TEST_ASYNC_URLS=mysql+aiomysql://test:test@127.0.0.1/test postgresql+asyncpg://postgres:test@127.0.0.1/
```
  NOTE
  
  The test cases would load environment variables from dot-env file tests/.env.
4. run unit-test
```
python -m unittest
```
or on docker compose:

Name of compose services for Python and SQLAlchemy version matrix defined in compose file docker-compose.yml has such format:
```
python{{X.Y}}-sqlalchemy{{X}}
```
For example, if want to run tests for Python 3.8 and SQLAlchemy 1.x, we shall up to run unit-tests as below:
```
cd tests
docker compose up python3.8-sqlalchemy1
```
For Python 3.10 and SQLAlchemy 2.x:
```
cd tests
docker compose up python3.10-sqlalchemy2
```