Answer: Locking Reads
Explore the use of locking reads in MySQL to maintain data consistency across concurrent sessions. Understand how transactions control locks, the roles of shared and exclusive locks, and how isolation levels impact locking behavior. This lesson guides you through practical examples of managing locks in multi-session environments and handling locked rows effectively.
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
OnlineStoreready for your use.Type
mysqland connect to MySQL in both terminals. Once you are inside the MySQL shell, select theOnlineStoredatabase in both terminals by running the following command:
USE OnlineStore;
In Terminal 1 (Session A), start a transaction and take a shared lock on a high-value order:
START TRANSACTION;-- Take a shared lock so others cannot update the rowSELECT OrderID, CustomerID, OrderDate, TotalAmount, DeliveryStatusFROM OrdersWHERE OrderID = 53FOR SHARE;
Session A now holds a shared lock on this row. Shared locks allow other shared readers, but they block writers.
In Terminal 2 (Session B), try updating a different row first to confirm there is no lock on it:
UPDATE OrdersSET TotalAmount = TotalAmount + 10WHERE OrderID = 1;
This update should complete immediately because OrderID = 1 is not locked.
Now, try to update the locked row:
UPDATE OrdersSET TotalAmount = TotalAmount + 10WHERE OrderID = 53;