Broad Network


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

This is part 6 of my series, MySQL Access Privileges. I assume you have read the previous part of the series, before reaching here. In this part of the series we see why tables are locked in MySQL and how to lock them.

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.

Project Start
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.

Lock Tables Statements
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

Project Continues
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 Design
Web Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT

Comments

Become the Writer's Fan
Send the Writer a Message