Locking Tables in MySQL
MySQL Access Privileges - Part 6
Division 7
Forward: In this part of the series we see why tables are locked in MySQL and how to lock them.
By: Chrysanthus Date Published: 8 Aug 2012
Introduction
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 login as James and test the locking feature. You need a MySQL 5 and a personal web server already setup together for this, in your computer. By carrying 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 use 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;
use supermarket;
#create tables
create table Sales
(
SaleID INTEGER NOT NULL AUTO_INCREMENT,
DateAndTime TIMESTAMP,
EmployeeID INTEGER,
CustomerID INTEGER,
PRIMARY KEY (SaleID ASC)
) ENGINE = INNODB;
create table SaleDetails
(
SaleID INTEGER NOT NULL,
ProductID INTEGER NOT NULL,
Quantity INTEGER,
SoldPrice DECIMAL(19,2),
PRIMARY KEY (SaleID, ProductID),
FOREIGN KEY (SaleID) REFERENCES Sales (SaleID) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = INNODB;
#create user and give him privileges on the two tables
create user 'james'@'localhost' identified by 'jimi123';
grant INSERT, SELECT, UPDATE on supermarket.Sales to 'james'@'localhost';
grant INSERT, SELECT, UPDATE on supermarket.SaleDetails to 'james'@'localhost';
Explanation: The first code segment creates the database, supermarket, and selects it. The second code segment creates the tables, Sales and SaleDetails.
Start your Personal Web Server. Start MySQL 5.x.x (you can use command prompt). Start the mysql client program at a new command prompt window and login as root. For my computer, the commands to start the MySQL server and mysql client are as follows:
"C:Program FilesMySQLMySQL Server 5.1binmysqld" --console
"C:Program FilesMySQLMySQL Server 5.1binmysql" -h localhost -u root –ppswrd
Your own starting commands may be similar. Type all the above SQL statements (you can copy and paste) at the mysql 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 a row 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 in 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:
UNLOCK TABLES
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'@'localhost';
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.
Now, open a third command prompt window, and login with the mysql client program as James. In my computer I typed the following command:
"C:Program FilesMySQLMySQL Server 5.1binmysql" -h localhost -u james -pjimi123
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):
use supermarket;
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.
Now, go to the root mysql window and type the following statement and note that the root mysql client program will go into a waiting state. This is because the Sales table is locked. After James unlocks the Sales table, the root statement will be executed (run).
insert into Sales (EmployeeID, CustomerID) values (72,86);
As James, in his mysql 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);
unlock tables;
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 mysql 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.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT