PyMySQLLock

Common errors

• sqlalchemy.exc.InternalError: (PyMySQL.err.InternalError) (1205, 'Lock wait timeout exceeded; try restarting transaction')

  cause The MySQL server's `innodb_lock_wait_timeout` has been exceeded while attempting to acquire a lock or perform a database operation that requires a lock.
  fix This specific error often indicates an underlying transaction lock contention. While `PyMySQLLock` uses named locks, ensuring your application transactions are short and efficient can help. For `PyMySQLLock` itself, ensure the `timeout` parameter in `lock.acquire()` is set appropriately and consider increasing `innodb_lock_wait_timeout` on the MySQL server if application logic genuinely requires longer waits.

• RuntimeError: Could not obtain named lock my_critical_task_lock within 10 seconds

  cause The `lock.acquire(timeout=X)` method returned `False` because another process or thread successfully held the named lock for the entire specified `X` duration.
  fix This indicates expected contention. The application should implement a retry strategy with exponential backoff, log the failure, or inform the user that the resource is currently busy. Do not simply busy-wait, as this consumes CPU unnecessarily.

• MySQL connection not reflecting external changes (e.g., `SELECT` queries returning stale data)

  cause This is a common issue with long-lived database connections, where the connection's transaction isolation level or internal caching prevents it from seeing committed changes made by other connections without explicit refresh or re-connection.
  fix Ensure your MySQL connection is set to an appropriate isolation level (e.g., `READ COMMITTED`). For `PyMySQLLock`, if you are reusing the underlying connection for other database operations, you might need to commit (even if no writes) or explicitly close and re-open the connection to force it to see the latest state. `Locker` manages its own connection, but application code interacting with the same DB often faces this.

Warnings

• gotcha The default `acquire()` method for a lock has a `timeout` of -1, meaning it will wait indefinitely for the lock to become available. This can lead to application processes hanging if the lock is held for a prolonged period or never released.
  Fix: Always specify a sensible `timeout` argument (e.g., `lock.acquire(timeout=60)`) to prevent indefinite waits and allow the application to handle lock contention gracefully. The `refresh_interval_secs` argument should also be set if locks are held for longer periods to keep the MySQL connection alive.
• gotcha PyMySQLLock uses MySQL's `GET_LOCK()` and `RELEASE_LOCK()` functions, which are tied to the specific MySQL connection that acquired them. If the application process holding a lock crashes or the underlying database connection is lost, the lock will be implicitly released by MySQL after its connection-level timeout, potentially leading to brief windows of unprotected access.
  Fix: Implement robust error handling and ensure that `lock.release()` is called in a `finally` block. For critical operations, combine with application-level idempotency or retry mechanisms. Consider increasing `wait_timeout` on the MySQL server if connections are frequently idle and being dropped, or use `refresh_interval_secs` in `acquire()` to keep the connection active.
• gotcha Sharing a single MySQL database connection object across multiple threads within the same application process can lead to deadlocks, race conditions, or unexpected behavior, as most Python MySQL drivers are not thread-safe at the connection level.
  Fix: Each thread should establish its own separate MySQL connection (and thus its own `Locker` instance). Alternatively, use a connection pool that manages connections on a per-thread basis, ensuring a connection is used by only one thread at a time.

Install

• pip install PyMySQLLock Install PyMySQLLock

Imports

• Locker
  wrong:

  from pymysqllock import Locker

  correct:

  from PyMySQLLock import Locker

  The top-level package name for import uses camel case `PyMySQLLock` as per the project's source and documentation, not `pymysqllock`.

Quickstart

This quickstart demonstrates how to acquire and release a named MySQL-backed lock. It configures the MySQL connection using environment variables for security, attempts to acquire a lock with a timeout, performs a simulated task if successful, and ensures the lock is released and the connection closed.

import os
from PyMySQLLock import Locker

# --- Configuration (use environment variables for security) ---
MYSQL_HOST = os.environ.get('MYSQL_HOST', 'localhost')
MYSQL_USER = os.environ.get('MYSQL_USER', 'root')
MYSQL_PASSWORD = os.environ.get('MYSQL_PASSWORD', 'password')
MYSQL_DB = os.environ.get('MYSQL_DB', 'test_db')

LOCK_NAME = 'my_critical_task_lock'
ACQUIRE_TIMEOUT = 10 # seconds

def run_task_with_lock():
    locker = None
    try:
        # Locker uses connection parameters compatible with common MySQL drivers (e.g., PyMySQL)
        locker = Locker(
            host=MYSQL_HOST,
            user=MYSQL_USER,
            password=MYSQL_PASSWORD,
            database=MYSQL_DB
        )
        lock = locker.lock(LOCK_NAME)

        print(f"Attempting to acquire lock '{LOCK_NAME}' with a {ACQUIRE_TIMEOUT}s timeout...")
        # Try to acquire the lock. Default timeout is -1 (infinite wait).
        # Setting refresh_interval_secs keeps the connection alive for long-held locks.
        if lock.acquire(timeout=ACQUIRE_TIMEOUT, refresh_interval_secs=5):
            print(f"Successfully acquired lock '{LOCK_NAME}'. Performing critical task...")
            # Simulate work
            import time
            time.sleep(5)
            print("Critical task completed.")
        else:
            print(f"Failed to acquire lock '{LOCK_NAME}' within {ACQUIRE_TIMEOUT} seconds. Another instance might hold it.")
    except Exception as e:
        print(f"An error occurred: {e}")
    finally:
        if 'lock' in locals() and lock.is_acquired():
            print(f"Releasing lock '{LOCK_NAME}'.")
            lock.release()
        if locker:
            locker.close_connection()
            print("MySQL connection closed.")

if __name__ == "__main__":
    print("Ensure MySQL server is running and database exists.")
    print("Set MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB environment variables if not using defaults.")
    run_task_with_lock()