MySQL - Table Locking

Table of content

MySQL database provides a multi-user environment, that allows multiple clients to access the database at the same time. To run this environment smoothly, MySQL introduced the concept of locks.

A client in a session can lock a certain table they are working on, in order to prevent other clients from using the same table. This process will avoid any data losses that might occur when multiple users work on the same table simultaneously.

A client can lock a table and unlock it whenever needed. However, if a table is already locked by a client session, it cannot be accessed by other client sessions until it is released.

Locking Tables in MySQL

You can restrict the access to records of the tables in MYSQL by locking them. These locks are used to keep other sessions away from modifying the tables in the current session.

MySQL sessions can acquire or release locks on the table only for itself. To lock a table using the MySQL LOCK TABLES Statement you need have the TABLE LOCK and SELECT privileges.

These locks are used to solve the concurrency problems. There are two kinds of MYSQL table locks −

  • READ LOCK − If you apply this lock on a table the write operations on it are restricted. i.e., only the sessions that holds the lock can write into this table.

  • WRITE LOCK − This lock allows restricts the sessions (that does not possess the lock) from performing the read and write operations on a table.

Syntax

Following is the syntax of the MySQL LOCK TABLES Statement −

LOCK TABLES table_name [READ | WRITE];

Unlocking Tables in MySQL

Once the client session is done using/accessing a MySQL table, they must unlock the table for other client sessions to use it. To do so, you can use the MySQL UNLOCK TABLE statement. This will release the table until other sessions lock it again.

Syntax

Following is the syntax of the MySQL UNLOCK TABLES Statement −

UNLOCK TABLES;

Example

Let us start with creating a table named CUSTOMERS that contains the details as shown below −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let's insert 2 records into the above created table using the INSERT statement as −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
  (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
  (2, 'Khilan', 25, 'Delhi', 1500.00 );

Create another table named BUYERS using the following query −

CREATE TABLE BUYERS (
   B_ID INT AUTO_INCREMENT,
   B_NAME VARCHAR(20) NOT NULL,
   B_AGE INT NOT NULL,
   B_ADDRESS CHAR (25),
   B_SALARY DECIMAL (18, 2),
   PRIMARY KEY (B_ID)
);

Following queries inserts records into the BUYERS table using the INSERT INTO SELECT statement. Here, we are trying to insert records from the CUSTOMERS table to BUYERS table.

Locking and Unlocking:

Here before the transfer, we are acquiring the write lock on the BUYERS table to which we are inserting records and acquiring read lock on the CUSTOMERS table from which we are inserting records. Finally, after the transfer we are releasing the records.

LOCK TABLES CUSTOMERS READ, BUYERS WRITE;

INSERT INTO BUYERS (B_ID, B_NAME, B_AGE, B_ADDRESS, B_SALARY)
   SELECT
      ID, NAME, AGE, ADDRESS, SALARY
   FROM
      CUSTOMERS
   WHERE
      ID = 1 AND NAME = 'Ramesh';
INSERT INTO BUYERS (B_ID, B_NAME, B_AGE, B_ADDRESS, B_SALARY)
   SELECT
      ID, NAME, AGE, ADDRESS, SALARY
   FROM
      CUSTOMERS
   WHERE
      ID = 2 AND NAME = 'Khilan';
	  
UNLOCK TABLES;

Verification

We can verify the contents of the BUYERS table using the below query −

SELECT * FROM BUYERS;

As we can see in the BUYERS table, the records has been transferred.

B_ID B_NAME B_AGE B_ADDRESS B_SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00

Table Locking Using a Client Program

Besides locking a table in a MySQL database with a MySQL query, we can also use a client program to perform the LOCK TABLES operation.

Syntax

Following are the syntaxes to Lock a table in MySQL in various programming languages −

PHP NodeJS Java Python

To lock the table in MySQL database through a PHP program, we need to execute the Lock Tables statement using the mysqli function query() as −

$sql="LOCK TABLES table_name [READ | WRITE]";
$mysqli->query($sql);

To lock the table in MySQL database through a Node.js program, we need to execute the Lock statement using the query() function of the mysql2 library as −

sql = "LOCK TABLES table_name [READ | WRITE]";
con.query(sql);

To lock the table in MySQL database through a Java program, we need to execute the Lock statement using the JDBC function executeUpdate() as −

String sql="LOCK TABLES table_name [READ | WRITE]";
statement.executeUpdate(sql);

To lock the table in MySQL database through a Python program, we need to execute the Lock statement using the execute() function of the MySQL Connector/Python as −

sql="LOCK TABLES table_name [READ | WRITE]";
cursorObj.execute(sql);

Example

Following are the programs −

PHP NodeJS Java Python 
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');

// Here we are locking two table;
$sql = "LOCK TABLES tut_tbl READ, clone_table WRITE";
if ($mysqli->query($sql)) {
    printf("Table locked successfully!.
");
}
if ($mysqli->errno) {
    printf("Table could not be locked!.
", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

Table locked successfully!.

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE SalesDetails (ID INT, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(255), CustomerAge INT, CustomrtPhone BIGINT, DispatchAddress VARCHAR(255), Email VARCHAR(50));"
  con.query(sql);

  sql = "insert into SalesDetails values(1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad', 25, '9000012345', 'Hyderabad - Madhapur', 'pujasharma@gmail.com');"
  con.query(sql);
  sql = "insert into SalesDetails values(2, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai', 30, '90000123654', 'Chennai- TNagar', 'vanajarani@gmail.com');"
  con.query(sql);

  sql = "CREATE TABLE CustContactDetails (ID INT,Name VARCHAR(255), Age INT,Phone BIGINT, Address VARCHAR(255), Email VARCHAR(50));"
  con.query(sql);

  sql = "LOCK TABLES SalesDetails READ, CustContactDetails WRITE;"
  con.query(sql);

  sql = "INSERT INTO CustContactDetails (ID, Name, Age, Phone, Address, Email) SELECT ID, CustomerName, CustomerAge, CustomrtPhone, DispatchAddress, Email FROM SalesDetails  WHERE  ID = 1 AND CustomerName = 'Raja';"
  con.query(sql);

  sql = "INSERT INTO CustContactDetails (ID, Name, Age, Phone, Address, Email) SELECT ID, CustomerName, CustomerAge, CustomrtPhone, DispatchAddress, Email FROM  SalesDetails WHERE ID = 2 AND CustomerName = 'Vanaja';"
  con.query(sql);

  sql = "UNLOCK TABLES;"
  con.query(sql);

  sql = "SELECT * FROM CustContactDetails;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 1,
    Name: 'Raja',
    Age: 25,
    Phone: 9000012345,
    Address: 'Hyderabad - Madhapur',
    Email: 'pujasharma@gmail.com'
  },
  {
    ID: 2,
    Name: 'Vanaja',
    Age: 30,
    Phone: 90000123654,
    Address: 'Chennai- TNagar',
    Email: 'vanajarani@gmail.com'
  }
]

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TableLock {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Lock table....
            String sql = "LOCK TABLES tutorials_tbl READ, clone_tbl WRITE";
            statement.executeUpdate(sql);
            System.out.println("Table Locked successfully...!");
            
            connection.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

Output

The output obtained is as shown below −

Connected successfully...!
Table Locked successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'tutorials_tbl'
#Creating a cursor object 
cursorObj = connection.cursor()
lock_table_query = f"LOCK TABLES {table_name} WRITE"
cursorObj.execute(lock_table_query)
print(f"Table '{table_name}' is locked successfully.")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Table 'tutorials_tbl' is locked successfully.
Advertisements