Locking Tables with Transactions in MySQL

Note: this page has been created with the use of AI. Please take caution, and note that the content of this page does not necessarily reflect the opinion of Cratecode.

Before we dive into the world of table locks and transactions in MySQL, let's take a moment to understand what they actually are. Because, as they say, you can't hit a target if you don't know what it is.

Table Locks

Table locks are just like those traditional, old-school locks that we use to safeguard our homes, except they are used to safeguard data in a database table. When a table lock is applied, it prevents other users from accessing or modifying the locked data until the lock is released. This can be particularly handy when dealing with situations where concurrent access to the same data might lead to conflicts or inconsistencies.

Transactions

Next, we have transactions. Ever bought anything online? If you have, then you've participated in a transaction. But, in the world of databases, a transaction is a group of operations that are treated as a single unit. All operations within a transaction are either executed in full (committed), or are not executed at all (rolled back) when an error occurs. This is similar to an online purchase - either you get the product, or you get the money back.

Now with the basics covered, let's see how we can lock tables with transactions in MySQL. Consider the scenario where we want to update the price of a product in the inventory and at the same time, deduct the appropriate amount from a user’s account. We can use transactions and table locks to ensure that these operations occur together and the data remains consistent.

BEGIN;
LOCK TABLES inventory WRITE, accounts WRITE;

UPDATE inventory 
SET price = price * 0.9 
WHERE product_id = 101;

UPDATE accounts 
SET balance = balance - (SELECT price FROM inventory WHERE product_id = 101) 
WHERE account_id = 201;

UNLOCK TABLES;
COMMIT;

In the above code, we begin a transaction with the BEGIN statement. The LOCK TABLES statement locks the inventory and accounts tables. The two UPDATE statements modify the product price and deduct the amount from the account balance. The UNLOCK TABLES statement releases the locks, and finally, COMMIT applies the changes.

And voila, we’ve just locked tables with a transaction in MySQL! Now, wasn't that a walk in the park?

FAQ