README#

SQLAlchemy-DLock#

Distributed lock 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: https://docs.sqlalchemy.org/14/orm/extensions/asyncio.html

SQLAlchemy 2.x: 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.begin() 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 asynchronous DB libraries like one of blows:
pip install sqlalchemy-dlock[asyncpg]
pip install sqlalchemy-dlock[asyncio] aiomysql
pip install SQLALchemy[asyncio] sqlalchemy-dlock psycopg

Test#

Following drivers are tested:

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

You can run unit-tests

on local environment:
1. Install the project with drivers and asyncio extra requires, a virtual environment (venv) is strongly advised:
```
pip install -e .[mysqlclient psycopg2-binary aiomysql asyncpg]
```
2. start up mysql and postgresql
  
  There is a docker compose file db.docker-compose.yml in the project dir, 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. The test cases load environment variables in tests/.env.
  
  eg (and also the defaults):
```
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/
```
4. run unit-test
```
python -m unittest
```
or on docker-compose:
1. build the project
```
python -m pip install build && python -m build -w
```
2. run unit-test
  
  Name of services for Python and SQLAlchemy version matrix in the compose file has such format:
```
python{{X.Y}}-sqlalchemy{{X}}
```
  For example, if want to take a test for Python 3.9 and SQLAlchemy 2.x, we shall up to run unit-tests as below:
```
cd tests
docker compose up python3.9-sqlalchemy2
```
  For Python 3.8 and SQLAlchemy 1.x:
```
cd tests
docker compose up python3.8-sqlalchemy1
```