Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Table Locks with LOCK TABLES

Explore how to apply table-level locking with LOCK TABLES in MySQL to manage concurrent access during transactions. Understand read and write locks, their effects on sessions, and techniques for consistency checks using the OnlineStore example.

We'll cover the following...

Solution

You can try it yourself using the steps below in the terminal:

  • Click “Click to Connect...” in the terminal widget to establish a connection. Once connected, click the + icon next to Terminal 1 in the top left corner of the terminal pane. This will open another terminal window. We need two terminals so you can observe how multiple sessions interact in real time. We will refer to these two SQL terminals as “Session A” and “Session B.”

  • The prepended command or start script will be executed upon connecting to the terminal. It will start the MySQL service and have the setup for OnlineStore ready for your use.

  • Type mysql and connect to MySQL in both terminals. Once you are inside the MySQL shell, select the OnlineStore database in both terminals by running the following command:

USE OnlineStore;

  • In Terminal 1 (Session A), acquire a table-level read lock on both Orders and Order_Details before running the consistency check:

LOCK TABLES Orders READ, Order_Details READ;

  • With the read lock in place, run a query that finds orphaned rows in Order_Details that have no matching order in Orders:

SELECT Order_Details.OrderID, Order_Details.ProductID, Order_Details.Quantity, Order_Details.TotalItemPrice FROM Order_Details LEFT JOIN Orders ON Order_Details.OrderID = Orders.OrderID WHERE Orders.OrderID IS NULL;

Under the simulated setup, this should include the orphaned row with OrderID = 99 that was inserted while foreign key checks were disabled. If there are any other orphaned rows, they will also appear in this result.

  • Leave the lock in place for a moment and switch to Terminal 2 (Session B). With Session A still holding the read lock, confirm that reads are allowed by running the following command:

SELECT OrderID, ProductID, Quantity, TotalItemPrice FROM Order_Details WHERE OrderID = 99;

This SELECT should complete successfully, ...