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

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

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

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

Hint

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

Practice

1. What is the main purpose of a shared lock in MySQL?

easy

A. Allow multiple users to read data simultaneously without conflicts

B. Prevent any user from reading data

C. Allow only one user to write data at a time

D. Delete data safely from the database

Solution

1. Step 1: Understand shared lock behavior

   A shared lock allows many users to read the same data at the same time safely.

2. Step 2: Compare with other lock types

   Exclusive locks prevent others from reading or writing, but shared locks only prevent writing.

3. Final Answer:

   Allow multiple users to read data simultaneously without conflicts -> Option A

4. Quick Check:

   Shared lock = multiple readers allowed [OK]

Hint: Shared locks allow many readers, exclusive locks block others [OK]

Common Mistakes:

• Confusing shared lock with exclusive lock
• Thinking shared locks block reading
• Assuming shared locks allow writing

2. Which of the following is the correct syntax to acquire a shared lock on a table in MySQL?

easy

A. LOCK TABLES table_name WRITE;

B. LOCK TABLES table_name SHARE;

C. LOCK TABLES table_name EXCLUSIVE;

D. LOCK TABLES table_name READ;

Solution

1. Step 1: Recall MySQL lock syntax

   In MySQL, to acquire a shared lock, you use LOCK TABLES table_name READ;.

2. Step 2: Eliminate incorrect options

   WRITE is for exclusive locks, EXCLUSIVE and SHARE are not valid MySQL lock keywords.

3. Final Answer:

   LOCK TABLES table_name READ; -> Option D

4. Quick Check:

   Shared lock syntax uses READ keyword [OK]

Hint: Use READ for shared lock, WRITE for exclusive lock [OK]

Common Mistakes:

• Using WRITE instead of READ for shared lock
• Using unsupported keywords like EXCLUSIVE or SHARE
• Confusing lock syntax with SQL SELECT statements

3. Consider two transactions in MySQL: Transaction A acquires a shared lock on a row, and Transaction B tries to acquire an exclusive lock on the same row. What happens?

medium

A. Transaction B immediately acquires the exclusive lock

B. Transaction B waits until Transaction A releases the shared lock

C. Transaction A releases its shared lock automatically

D. Both transactions acquire locks simultaneously

Solution

1. Step 1: Understand lock compatibility

   Shared locks allow multiple readers but block exclusive locks until released.

2. Step 2: Analyze the scenario

   Transaction B must wait for Transaction A to release the shared lock before getting exclusive lock.

3. Final Answer:

   Transaction B waits until Transaction A releases the shared lock -> Option B

4. Quick Check:

   Exclusive lock waits for shared locks to release [OK]

Hint: Exclusive locks wait if shared locks exist on data [OK]

Common Mistakes:

• Assuming exclusive lock can override shared lock immediately
• Thinking shared lock releases automatically
• Believing both locks can coexist on same data

4. You wrote this MySQL code to lock a table for writing but it causes an error:

LOCK TABLES my_table SHARE;

What is the error and how to fix it?

medium

A. Error: Missing semicolon. Fix: Add semicolon at end.

B. Error: Table does not exist. Fix: Create the table first.

C. Error: Invalid lock type 'SHARE'. Fix: Use 'WRITE' instead of 'SHARE'.

D. Error: Cannot lock table twice. Fix: Unlock before locking again.

Solution

1. Step 1: Identify invalid lock keyword

   MySQL does not support 'SHARE' as a lock type; it uses 'READ' or 'WRITE'.

2. Step 2: Correct the lock type

   To lock for writing, use LOCK TABLES my_table WRITE; instead.

3. Final Answer:

   Error: Invalid lock type 'SHARE'. Fix: Use 'WRITE' instead of 'SHARE'. -> Option C

4. Quick Check:

   Use WRITE for exclusive lock, SHARE is invalid [OK]

Hint: Use READ or WRITE only; SHARE is not valid in MySQL [OK]

Common Mistakes:

• Using unsupported lock keywords
• Ignoring error messages about syntax
• Assuming SHARE is synonym for READ

5. You want to ensure that multiple users can read data from a table simultaneously, but only one user can update it at a time. Which locking strategy should you use in MySQL?

hard

A. Use shared locks (READ) for reading and exclusive locks (WRITE) for updating

B. Use exclusive locks (WRITE) for both reading and updating

C. Use shared locks (READ) for updating and exclusive locks (WRITE) for reading

D. Do not use any locks; rely on automatic commit

Solution

1. Step 1: Understand shared and exclusive lock roles

   Shared locks allow multiple readers; exclusive locks allow one writer.

2. Step 2: Apply correct locks for reading and writing

   Use shared locks for reading so many users can read safely, and exclusive locks for updating to prevent conflicts.

3. Final Answer:

   Use shared locks (READ) for reading and exclusive locks (WRITE) for updating -> Option A

4. Quick Check:

   Shared for read, exclusive for write ensures safe concurrency [OK]

Hint: Shared locks for reading, exclusive locks for writing [OK]

Common Mistakes:

• Using exclusive locks for reading blocks all users
• Using shared locks for writing causes conflicts
• Ignoring locks and risking data corruption