MySQL - Table Locking

MySQL: Table Locking Explained

In a multi-user database environment, concurrency control is essential to prevent data corruption when multiple clients access the same data simultaneously. MySQL provides several mechanisms for this, including table locking.

Important Context: Explicit LOCK TABLES is an older concurrency mechanism, primarily associated with the MyISAM storage engine. The modern default, InnoDB, uses a more sophisticated, higher-concurrency method: row-level locking within transactions. You should almost always prefer InnoDB transactions over explicit table locks for typical application workloads.

When to Use LOCK TABLES

Despite being less common today, LOCK TABLES still has valid use cases:

• Bulk Operations: During large data loads or batch updates where you need to prevent any other access to ensure consistency.
• Non-Transactional Engines: When working with MyISAM or other non-transactional tables.
• Emulating Transactions: For complex, multi-table operations that cannot be easily wrapped in a single standard transaction, a table lock can ensure a consistent snapshot.

Understanding Table Lock Types

The LOCK TABLES statement acquires locks for the current client session. No other session can access the locked tables in a conflicting way until the locks are released. The main lock types are:

• READ LOCK: The session holding the lock can read from the table, but not write to it. Other sessions can also read from the table but cannot acquire a WRITE lock or modify data.
• WRITE LOCK: The session holding the lock has exclusive access. It can read from and write to the table. No other session can read from or write to the table.

Syntax for Locking and Unlocking

-- To acquire locks
LOCK TABLES table1_name [READ | WRITE], table2_name [READ | WRITE], ...;

-- To release all locks held by the current session
UNLOCK TABLES;

Example: Safe Data Archiving

Imagine you need to archive old orders from an active_orders table to an archived_orders table. To ensure no new orders are modified or deleted during this process, we can use table locks.

First, let’s set up the tables:

CREATE TABLE active_orders (
    id INT PRIMARY KEY,
    order_data TEXT,
    order_date DATE
);

CREATE TABLE archived_orders LIKE active_orders;

INSERT INTO active_orders VALUES
(1, 'Order A', '2022-01-15'),
(2, 'Order B', '2023-05-20'),
(3, 'Order C', '2022-03-10');

Now, perform the archiving operation within a lock block:

-- Acquire a READ lock on the source and a WRITE lock on the destination
LOCK TABLES active_orders READ, archived_orders WRITE;

-- 1. Copy old orders to the archive table
INSERT INTO archived_orders
SELECT * FROM active_orders WHERE order_date < '2023-01-01';

-- 2. This step would fail because we only have a READ lock
-- DELETE FROM active_orders WHERE order_date < '2023-01-01';
-- ERROR 1099 (HY000): Table 'active_orders' was locked with a READ lock and can't be updated

-- Release the locks
UNLOCK TABLES;

-- To perform both INSERT and DELETE, you'd need WRITE locks on both tables
LOCK TABLES active_orders WRITE, archived_orders WRITE;
INSERT INTO archived_orders SELECT * FROM active_orders WHERE order_date < '2023-01-01';
DELETE FROM active_orders WHERE order_date < '2023-01-01';
UNLOCK TABLES;

Verification

After running the correct sequence with WRITE locks, you can verify the tables:

SELECT * FROM archived_orders;
-- Returns orders 1 and 3

SELECT * FROM active_orders;
-- Returns order 2

Modern Alternative: InnoDB Transactions

For the same archiving task on InnoDB tables, a transaction is the superior approach. It provides atomicity (all or nothing) and uses fine-grained row-level locks, allowing other parts of the table to remain accessible.

START TRANSACTION;

-- The SELECT statement places a read lock on the matching rows
INSERT INTO archived_orders
SELECT * FROM active_orders WHERE order_date < '2023-01-01';

-- The DELETE statement places a write lock on the matching rows
DELETE FROM active_orders WHERE order_date < '2023-01-01';

-- If both succeed, make the changes permanent
COMMIT;

-- If an error occurs, you would issue a ROLLBACK to undo the changes.

Using Table Locks Programmatically

If you must use LOCK TABLES, here’s how to do it in application code. Always ensure UNLOCK TABLES is called, even if an error occurs, typically in a finally block.

NodeJS
Python
Java
PHP



Demonstrating the lock/unlock flow with `mysql2/promise`.

const mysql = require('mysql2/promise');

async function lockedOperation() {
    const connection = await mysql.createConnection(process.env.DATABASE_URL);
    try {
        console.log('Acquiring locks...');
        await connection.execute('LOCK TABLES active_orders WRITE, archived_orders WRITE');

        console.log('Performing locked operations...');
        await connection.execute("INSERT INTO archived_orders SELECT * FROM active_orders WHERE id = 1");
        await connection.execute('DELETE FROM active_orders WHERE id = 1');
        console.log('Operations successful.');

    } catch (error) {
        console.error('Error during locked operation:', error);
    } finally {
        console.log('Releasing locks...');
        await connection.execute('UNLOCK TABLES');
        await connection.end();
    }
}

lockedOperation();



Using Python's `try...finally` to guarantee locks are released.

import mysql.connector

def locked_operation():
    conn = mysql.connector.connect(**config) # Assume config is defined
    cursor = conn.cursor()
    try:
        print('Acquiring locks...')
        cursor.execute('LOCK TABLES active_orders WRITE, archived_orders WRITE')

        print('Performing locked operations...')
        cursor.execute('INSERT INTO archived_orders SELECT * FROM active_orders WHERE id = 1')
        cursor.execute('DELETE FROM active_orders WHERE id = 1')
        conn.commit()
        print('Operations successful.')

    except mysql.connector.Error as err:
        print(f'Error: {err}')
    finally:
        print('Releasing locks...')
        cursor.execute('UNLOCK TABLES')
        cursor.close()
        conn.close()

locked_operation()



Using Java's `try-finally` block to ensure `UNLOCK TABLES` is always executed.

import java.sql.*;

public class TableLocker {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS); // Assume constants defined
            Statement stmt = conn.createStatement();

            System.out.println("Acquiring locks...");
            stmt.execute("LOCK TABLES active_orders WRITE, archived_orders WRITE");

            System.out.println("Performing operations...");
            stmt.executeUpdate("INSERT INTO archived_orders SELECT * FROM active_orders WHERE id = 1");
            stmt.executeUpdate("DELETE FROM active_orders WHERE id = 1");
            System.out.println("Operations successful.");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try (Statement finalStmt = conn.createStatement()) {
                    System.out.println("Releasing locks...");
                    finalStmt.execute("UNLOCK TABLES");
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}



Using PHP with a `try...finally` block (PHP 5.5+) to ensure cleanup.

<?php
mysqli_report(MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'root', 'password', 'test_db');

try {
    echo "Acquiring locks...\n";
    $mysqli->query('LOCK TABLES active_orders WRITE, archived_orders WRITE');

    echo "Performing operations...\n";
    $mysqli->query('INSERT INTO archived_orders SELECT * FROM active_orders WHERE id = 1');
    $mysqli->query('DELETE FROM active_orders WHERE id = 1');
    echo "Operations successful.\n";

} catch (mysqli_sql_exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
} finally {
    if (isset($mysqli)) {
        echo "Releasing locks...\n";
        $mysqli->query('UNLOCK TABLES');
        $mysqli->close();
    }
}