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

Introduction

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

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;
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 REFERENCES Sales (SaleID) ON UPDATE CASCADE ON DELETE CASCADE,
    ProductID INTEGER NOT NULL,
    Quantity INTEGER,
    SoldPrice DECIMAL(19,2),

    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.

Project Start
Execute the following command sequence to connect to the server as root (using Mysqlcom.pl):

cd c:\
Mysqlcom.pl (possibly in c:\Perl\lib directory)
connect "root", "localhost", 3306;
Password: sql

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.

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;

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):

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 Mysqlcom 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 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);
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 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.

Chrys

Broad Network

Related Articles

Table Locking in MySQL

MySQL Access Privileges - Part 6

Introduction

Related Links

Comments