Lock types (shared, exclusive) in MySQL

Introduction

Locks help keep data safe when many people use the database at the same time. They stop conflicts by controlling who can read or change data.

When multiple users read the same data without changing it.

When a user needs to update data and prevent others from changing it at the same time.

When you want to make sure data stays correct during a transaction.

When running reports that need consistent data without interference.

When preventing two users from deleting or updating the same row simultaneously.

Syntax

MySQL

LOCK TABLES table_name READ;
LOCK TABLES table_name WRITE;

READ lock is a shared lock. Many users can read but no one can write.

WRITE lock is an exclusive lock. Only one user can write or read until the lock is released.

Examples

This sets a shared lock on the employees table. Others can also read but cannot write.

MySQL

LOCK TABLES employees READ;

This sets an exclusive lock on the employees table. Only this session can read or write until unlocked.

MySQL

LOCK TABLES employees WRITE;

This releases all locks held by the current session.

MySQL

UNLOCK TABLES;

Sample Program

This locks the employees table for reading, runs a query to get sales department employees, then unlocks the table.

MySQL

LOCK TABLES employees READ;
SELECT * FROM employees WHERE department = 'Sales';
UNLOCK TABLES;

Important Notes

Always unlock tables after locking to avoid blocking others.

Shared locks allow multiple readers but block writers.

Exclusive locks block both readers and writers except the locker.

Summary

Shared locks let many users read data safely at the same time.

Exclusive locks let one user write data safely without interference.

Use locks to keep data correct when many users access the database.