In multi-user database systems, multiple clients may try to read or modify the same data at the same time. To maintain data consistency and integrity, MySQL provides a mechanism called locking.

There are two types of locking levels, Table-level locking and Row-level locking.

Table-level locking means MySQL locks the entire table, preventing other sessions from accessing it in certain ways until the lock is released.

When a table is locked:

• Other connections may be blocked from reading
• Or blocked from writing
• Or blocked from both

Table-level locking is simple, fast for small tables, poor for high concurrency.

On the other hand the Row-level locking locks specific rows only. The MyISAM storage engine supports Table-level locks only while the InnoDB storage engine supports the Row-level locks + table locks

• Row-level locking

START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;

Row locks are typically acquired when:

• Rows are selected using FOR UPDATE
• Rows are selected using LOCK IN SHARE MODE
• Rows are modified (UPDATE, DELETE, INSERT)

In this example, the user with id=1 is locked and no other transaction can modify it until the current transaction completes. The “For Update” statement indicates row-level locking and it’s called an exclusive lock.

Types of Row-Level Locks in MySQL (InnoDB)

1. Exclusive Lock (X Lock): An exclusive lock prevents both reads and writes by other transactions. 

Example: 

SELECT * FROM users WHERE id = 1 FOR UPDATE;

Behavior:

• Only one transaction can hold the lock
• Blocks other reads (with locks) and writes
• Required for updates and deletes

Use Cases:

• Updating balances
• Booking systems
• Financial transactions

2. Exclusive Lock (X Lock): An exclusive lock prevents both reads and writes by other transactions. 

Example:

SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

Behavior:

• Read-only access
• Multiple transactions can acquire shared locks
• No transaction can update or delete the row

Use Cases:

• Reading data that must remain unchanged
• Validation before update
• Consistent reads during a transaction

3. Automatic locks during DML operations:

Sometimes mysql applies row locking automatically for certain operations as in this example:

START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;

Here MySQL automatically locks the affected row. 

Autocommit and Row Locks

With default MySQL settings: 

autocommit = 1

SELECT * FROM users WHERE id = 1 FOR UPDATE;

What about the lock duration and release?

Row-level locks live only for the duration of a transaction. Locks are released when: COMMIT, ROLLBACK, Transaction is killed, Connection is closed.

Row locking in Laravel:

lockForUpdate() (Exclusive Lock)

DB::transaction(function () {
    $user = DB::table('users')
        ->where('id', 1)
        ->lockForUpdate()
        ->first();

    DB::table('users')
        ->where('id', 1)
        ->update(['balance' => $user->balance - 100]);
});

DB::transaction(function () {
    $user = DB::table('users')
        ->where('id', 1)
        ->sharedLock()
        ->first();
});

DB::transaction(function () use ($seatId) {

    $seat = Seat::where('id', $seatId)
        ->lockForUpdate()
        ->first();

    if ($seat->is_booked) {
        throw new Exception('Seat already booked');
    }

    $seat->update(['is_booked' => true]);
});

• Table-level locking

MySQL supports two main types of table locks:

– READ Lock

LOCK TABLES users READ;

In this lock mode multiple sessions can read the table. No session can write to the table (including the one holding the lock).

The READ lock is suitable for operations like reporting, data analysis, exporting data so that data remain unchanged during reads.

– WRITE Lock

LOCK TABLES users WRITE;

In this lock type only the session holding the lock can read and write. And all other sessions are blocked

The write lock is suitable on cases like bulk updates, maintenance tasks, data migration scripts.

Keep in mind that table locks are connection-based not transaction-based in contrast to row-locking.

Unlocking Tables

UNLOCK TABLES;

LOCK TABLES users WRITE;

UPDATE users SET status = 'inactive' WHERE last_login < '2022-01-01';

UNLOCK TABLES;

LOCK TABLES users READ;

SELECT * FROM users;

UNLOCK TABLES;

LOCK TABLES users WRITE, orders READ;

UNLOCK TABLES;