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

💡 TIP

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:

tests/docker-compose.yml defines a Python and SQLAlchemy version matrix – it combines Python 3.8 to 3.12 and SQLAlchemy v1/v2 for test cases. We can run it by:
```
cd tests
docker compose up --abort-on-container-exit
```

How to build docs¶

This documentation is made with Sphinx, be sure to install it’s dependencies:
```
pip install -r docs/requirements.txt
```
Ignore this step if requirements already installed.
(Optional) Re-generate API-Docs, if source tree changed:
```
sphinx-apidoc -o docs/apidocs -eMTf src
```
Build HTML documentation:
```
make -C docs html
```

The built static web site is output to docs/_build/html, we can serve it:

python -m http.server -d docs/_build/html

then open http://localhost:8000/ in a web browser.

Tip

Try another port if 8000 is already in use. For example, to serve on port 8080:

python -m http.server -d docs/_build/html 8080