sqlalchemy-dlock 0.2.1

A distributed lock implementation based on SQLAlchemy

SQLAlchemy-DLock

Distributed lock based on Database and SQLAlchemy.

It currently supports below locks:

• MySQL - named lock: https://dev.mysql.com/doc/refman/8.0/en/locking-functions.html
• PostgreSQL - advisory lock: https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS

❗ Note:

The project is not stable enough and DO NOT use it in production.

Usages

• Work with SQLAlchemy's Connection object:

  from sqlalchemy import create_engine
  from sqlalchemy_dlock import create_sadlock
  
  key = 'user/001'
  
  engine = create_engine('postgresql://scott:tiger@localhost/')
  conn = engine.connect()
  
  # Create the D-Lock on the connection
  lock = create_sadlock(conn, key)
  
  # it's not lock when constructed
  assert not lock.acquired
  
  # lock
  lock.acquire()
  assert lock.acquired
  
  # un-lock
  lock.release()
  assert not lock.acquired
  

• Used in 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@localhost/')
  with engine.connect() as conn:
  
      # Create the D-Lock on the connection
      with create_sadlock(conn, key) as lock:
          # It's locked
          assert lock.acquired
  
      # Auto un-locked
      assert not lock.acquired
  
      # 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.acquired
          # lock it now:
          lock2.acquire()
          assert lock2.acquired
  
      # Auto un-locked
      assert not lock2.acquired
  

• Work with SQLAlchemy's ORM session:

  ❗ Note:

  According to https://docs.sqlalchemy.org/14/orm/extensions/asyncio.html:

  • The asyncio extension as of SQLAlchemy 1.4.3 can now be considered to be beta level software.
  • The asyncio extension requires at least Python version 3.6

  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@localhost/')
  Session = sessionmaker(bind=engine)
  
  with Session() as session:
    with create_sadlock(session, key) as lock:
        assert lock.acquired
    assert not lock.acquired
  

• Work asynchronously

  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@localhost/')
  
  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
  

Tests

Set environment variables TEST_URLS and TEST_ASYNC_URLS for sync and async database connection url. Multiple connections separated by space.

Set environment variables NO_ASYNCIO to 1 disable asyncio tests.

The test cases load environment variables in tests/.env.

eg (and also the defaults):

TEST_URLS=mysql://test:test@localhost/test postgresql://postgres:test@localhost/
TEST_ASYNC_URLS=mysql+aiomysql://test:test@localhost/test postgresql+asyncpg://postgres:test@localhost/

CHANGELOG

v0.2.1

Date: 2023-02-25

• New:
  • support SQLAlchemy 2.0

v0.2

Date: 2021-03-23

First v0.2.x version released.

v0.2b2/b3

Date: 2021-03-23

• Add:
  • More unit tests
  • Optimized CI

v0.2b1

Date: 2021-03-16

• Add:

  • New unit tests
  • CI by github workflows

v0.2a3

Date: 2021-03-14

• Change:

  • Drop Python 3.5 support.
  • Remove SQLAlchemy version requires earlier than 1.4 in setup, it's not supported actually.
  • Adjust PostgreSQL lock's constructor arguments order

• Add:

  • More test cases, and add test/deploy workflow in github actions.
  • Add docker-compose test scripts

v0.2a2

Date: 2021-03-09

• Change:

  • Rename a lot of function/class:

    • sadlock -> create_sadlock
    • asyncio.sadlock -> asyncio.create_async_sadlock

    and some other ...

v0.2a1

Date: 2021-03-08

• New:

  • Asynchronous IO Support by:

    • aiomysql for MySQL

      Connection URL is like: "mysql+aiomysql://user:password@host:3306/schema?charset=utf8mb4"

    • asyncpg for PostgreSQL

      Connection URL is like: "postgresql+asyncpg://user:password@host:5432/db"

    Read https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html for details

v0.1.2

Date: 2021-01-26

Still an early version, not for production.

• Changes:
  • Arguments and it's default value of acquire now similar to stdlib's multiprossing.Lock, instead of Threading.Lock
  • MySQL lock now accepts float-point value as timeout
• Adds
  • Several new test cases
• Other
  • Many other small adjustment

v0.1.1

• A very early version, maybe not stable enough.
• Replace black2b with crc64-iso in PostgreSQL key convert function
• Only named arguments as extra parameters allowed in Lock's implementation class

AUTHORS

• Liu Xue Yan ([e-mail])