Home > MySQL > Table Lockingprinter version

MySQL Table Locking

This tutorial assumes that you have access to the MySQL command prompt and uses the people table created in the Create Tutorial and the payroll table referred to in the Join Queries Tutorial.

Concurrency

MySQL provides an environment where multiple users can read and write to and from database tables and this presents potential problems.

For example an application may allow a number of users to update tables based on the results of other tables.

If the first user runs a query on table 1 and delays the update on table 2, a second user meantime may update table 1.

This will lead the first user to update table 2 based on stale information from table 1.

Concurrency isn't a problem if your scripts only insert, update or delete one table row in a single operation.

The problem only occurs if your scripts allow multiple users to insert, update or delete based on results from SELECT statements.

Lock Variables

Each table has a lock variable associated with it. If a user sets the lock variable on a table then other users cannot perform particular operations on that table until the lock has been released.

When a user sets a lock variable for a table they are said to hold the lock for that table.

There are two types of lock.

A WRITE LOCK that prohibits other users from reading from or writing to that table.
A READ LOCK that prohibits other users from writing to that table or holding a WRITE LOCK for that table but does allow other users to read from the table and hold a READ LOCK for that table.

When a request for locks is made then all the tables requiring locks must be listed in a single request.

When a lock is requested it is placed in either a write-lock queue or read-lock queue.

The write-lock queue always has priority over the read-lock queue - no matter how long the read lock requests have been pending.

If not all the locks can be granted then the request is suspended until they all become available.

After operations have been completed a lock is released with UNLOCK TABLES, which releases the locks held on all the tables.

Example of Table Locking

To demonstrate an example of locking, the people and payroll table that were created in an earlier tutorial can be used. Their contents are reproduced below.

The people and payroll table

What Can Happen without Table Locking

Suppose someone in Human Resources wants to update the database because a member of staff has left and been replaced with someone on a different salary.

Both the people and payroll table need to be updated with the details of the new member of staff.

The people table is updated first but before the payroll table can be updated the person doing it is called away to deal with something else.

Meantime the Payroll Department are preparing a report that will show employees details based on information from the people table and the payroll table.

When queries are run on the tables the information from both tables don't match.

What Happens with Table Locking

Before updating the database both the people and payroll table are write locked. This prevents anyone reading from or writing to the tables until the updates have been completed.

Example of Table Locking

mysql> LOCK TABLES people WRITE, payroll WRITE;

UPDATE people SET firstname = 'Frank', lastname = 'Jones', address = 'Brick Lane', county = 'London', age = 34 WHERE id = 3;

UPDATE payroll SET firstname = 'Frank', lastname = 'Jones', salary = 11200 WHERE id = 3;

UNLOCK TABLES;

mysql>

Table Locking for Performance

The other case where locking may be used is to increase performance for an important query.

If a complex query is running slowly then stopping all other users from querying the database will speed it up.

This will inconvenience other users so should only be used where really necessary.

Previous - MySQL Functions Next - Accessing a Database