Lock types (shared, exclusive) in MySQL - Mini Project: Build & Apply

Understanding Lock Types: Shared and Exclusive Locks in MySQL

📖 Scenario: You are managing a small library database where multiple users can read book information simultaneously, but only one user can update the book details at a time to avoid conflicts.

🎯 Goal: Build a simple MySQL transaction example that demonstrates acquiring a shared lock for reading and an exclusive lock for updating a book record using separate sessions.

📋 What You'll Learn

Create a table called books with columns id (INT, primary key), title (VARCHAR(100)), and author (VARCHAR(100))

Insert exactly three book records with given values

Start a transaction and acquire a shared lock on the book with id = 1

In another session, start another transaction and acquire an exclusive lock on the book with id = 2

Commit both transactions

💡 Why This Matters

🌍 Real World

Databases use locks to manage multiple users reading and writing data safely without conflicts.

💼 Career

Understanding locks is essential for database administrators and developers to ensure data integrity and performance.

1

Create the books table and insert data

Create a table called books with columns id as INT primary key, title as VARCHAR(100), and author as VARCHAR(100). Then insert these three records exactly: (1, 'The Hobbit', 'J.R.R. Tolkien'), (2, '1984', 'George Orwell'), and (3, 'Pride and Prejudice', 'Jane Austen').

MySQL

-- Your code here to create the table and insert the three records

Need a hint?

Use CREATE TABLE to define the structure and INSERT INTO to add the three books.

2

Start a transaction and acquire a shared lock

Write SQL to start a transaction and select the book with id = 1 using SELECT ... LOCK IN SHARE MODE to acquire a shared lock.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien'),
(2, '1984', 'George Orwell'),
(3, 'Pride and Prejudice', 'Jane Austen');

-- Your code here to start a transaction and acquire a shared lock on id=1

Need a hint?

Use START TRANSACTION; and then select with LOCK IN SHARE MODE to get a shared lock.

3

Start another transaction and acquire an exclusive lock

In a new database session, write SQL to start a second transaction and select the book with id = 2 using SELECT ... FOR UPDATE to acquire an exclusive lock.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien'),
(2, '1984', 'George Orwell'),
(3, 'Pride and Prejudice', 'Jane Austen');

START TRANSACTION;
SELECT * FROM books WHERE id = 1 LOCK IN SHARE MODE;

-- In a new database session:
-- Your code here to start a second transaction and acquire an exclusive lock on id=2

Need a hint?

In a new database session, use START TRANSACTION; and then select with FOR UPDATE to get an exclusive lock.

4

Commit both transactions

Write SQL to commit both transactions to release the locks. (Run COMMIT; in each session.)

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien'),
(2, '1984', 'George Orwell'),
(3, 'Pride and Prejudice', 'Jane Austen');

START TRANSACTION;
SELECT * FROM books WHERE id = 1 LOCK IN SHARE MODE;

-- In a new database session:
START TRANSACTION;
SELECT * FROM books WHERE id = 2 FOR UPDATE;

-- Commit both sessions:
-- Your code here to commit both transactions

Need a hint?

In each session, use COMMIT; twice (one for each session) to commit both transactions and release locks.