Table Locking in MySQL
MySQL Access Privileges - Part 6
Foreword: In this part of the series we see why tables are locked in MySQL and how to lock them.
By: Chrysanthus Date Published: 18 Jul 2015
We shall carry out a small project: We shall build a small database with a few tables as the root. We shall then create a user, James and give him some privileges, which include the LOCK TABLE privilege. You will then login as James to test the code. As you carry out this project, you will see how locking fits into your own project.
Creating the Database
The following code will create a database, select the database, and create two tables in it, then create a user (James) and then give him privileges. Let us assume that the database is for a supermarket and that the things sold are input into the computer by hand.
#create and select database
create database supermarket;
create table Sales
SaleID INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (SaleID ASC)
) ENGINE = INNODB;
CREATE TABLE SaleDetails
SaleID INTEGER NOT NULL REFERENCES Sales (SaleID) ON UPDATE CASCADE ON DELETE CASCADE,
ProductID INTEGER NOT NULL,
PRIMARY KEY (SaleID, ProductID)
) ENGINE = INNODB;
#create user and give him privileges on the two tables
create user james identified by 'jimi123';
grant INSERT, SELECT, UPDATE on supermarket.Sales to james;
grant INSERT, SELECT, UPDATE on supermarket.SaleDetails to james;
Explanation: The first code segment creates the database, supermarket, and selects it. The second code segment creates the tables, Sales and SaleDetails.
Execute the following command sequence to connect to the server as root (using Mysqlcom.pl):
Mysqlcom.pl (possibly in c:\Perl\lib directory)
connect "root", "localhost", 3306;
Type all the above SQL statements (you can copy and paste) at the Mysqlcom command prompt. Remember to press the Enter key after the last line of the SQL statements.
Reasons for Locking Tables
Consider the above two tables. The relationship between the SaleDetails table and the Sales table is many-to-one. Each time James inserts a row into the Sales table, he has to insert at least one row into the SaleDetails table. When James inserts a row into the Sales table (see below), he then has to read the value of the SaleID from the row he has just inserted in the Sales table. The SaleID read becomes an element of the row he will insert into the SaleDetails table.
Now, the SaleID column of the Sales table is AUTO_INCREMENT. After inserting a row into the Sales table, before reading the SaleID, which was determined by the computer (auto_increment), another user might have inserted a new row into the Sales table. And so, James would read the wrong SaleID. He would not read the SaleID for the product that he sold. He would read the one for the next product that the next person sold. To avoid this problem, James should lock the Sales table before he carries out his transaction, which consists of inserting a row into the Sales table, reading the latest SaleID, and then inserting one or more rows into the SaleDetails table that has the last SaleID from the Sales table. After the transaction is finished, James should unlock the Sales table, so that others can use it. James does not have to lock the SaleDetails table; however MySQL wants James to lock all the tables of a transaction.
In order to lock a table, a user needs the LOCK TABLES and SELECT privileges. The user uses these same privileges to unlock the tables. The user can lock a table so that only he alone reads data from the table. The user can lock a table so that only he alone reads and writes data to the table; this is what James needs.
In simple terms, the syntax to lock a single table is:
lock tables tble_name1 lock_type
The syntax to lock more than one table is:
lock tables tble_name1 lock_type, tble_name2 lock_type, tble_name3 lock_type . . .
lock_type can be either the word, READ, or the word, WRITE . It is READ for a table, so that the user alone can read the table; it is WRITE for a table, so that the user alone can read and write the table.
To unlock all the tables locked in a transaction, the user would just type the following:
Now, you as root will give the privileges that James needs to lock and unlock the Sales table. Type the following statement and press Enter in your root mysql window:
grant SELECT, LOCK TABLES on supermarket.* to james;
A user needs to have the SELECT privilege before he can have the LOCK TABLES privilege. That is why we have the SELECT and LOCK TABLES privileges in the statement. The user has been given the privileges to lock all the tables in the supermarket database. These are the same privileges to unlock the tables of the database.
Now, open a second command prompt window, and login to the Mysqlcom client program as James.
Now, type the following code segment, which will select the supermarket database, lock the Sales table (and the SaleDetails table for formality), insert a row into it, and selects the last (maximum) SaleID from the Sales table (do not forget to press the Enter Key after the last statement):
lock tables Sales WRITE, SaleDetails WRITE;
insert into Sales (EmployeeID, CustomerID) values (25,45);
set @SaleID = NULL;
select max(SaleID) from Sales into @SaleID;
The Set statement provides the variable to hold the last SaleID of the sales table.
insert into Sales (EmployeeID, CustomerID) values (72,86);
As James, in his Mysqlcom client window, type the following code segment (do not forget to press the Enter key after the last statement):
insert into SaleDetails (SaleID, ProductID, Quantity, SoldPrice) values (@SaleID, 20, 5, 15.99);
The first of these statements, inserts a row with the SaleID read from the Sales table, into the SaleDetails table. The second one unlocks all the tables.
Now, go to the root Mysqlcom window and note that the statement waiting has been executed, because tables have been unlocked.
So, when you have a transaction and you do not want anybody to interfere with the tables, lock the tables. For tables in the transaction that do not have to be locked, lock them for formality as required by MySQL server. When the transaction is finished, unlock the tables so that other people can use them.
That is it for this part of the series. We stop here and continue in the next part.
Related LinksImplementing Database in MySQL
Programming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course