Explaining InnoDB Explicit Locking Mechanisms

Introduction

Locking mechanisms are critical to keep the database consistent (a fact that is not well understood by some developers). However, unnecessary locks can have a negative impact on system performance. So, it’s crucial to any developer to understand the principles of locking as well as to know when, where, and how to acquire a lock.

In this article, I'll like to introduce and explain the concepts of explicit locking mechanisms in InnoDB using several real-world examples.

Locking Concepts

There are basically two type of locking mechanisms:

Read Lock (Shared Lock). These are locks taken on resources to provide consistent reads by not allowing write operations to that resource.

If there is no write lock set on the resource, the transaction acquires the read lock immediately. Many transactions can acquire read locks on the same resource at the same time. While a transaction holds read lock, no write lock can be acquired on that resource, putting the transaction into the wait queue.

Write Lock (Exclusive Lock). These are locks taken on a resource when it needs to be modified. There can be only one write lock on a resource at a given time. If there is no (read/write) lock on the resource, the transaction acquires the write lock immediately. While a transaction holds write lock on a resource, all other lock requests must wait.

It's noteworthy that write lock has a higher priority than read lock. When a resource is unlocked, and if there are lock requests waiting in the queue, then the lock is granted in the following manner:

grant the lock first to the request waiting in the write lock queue
if there is no lock request for the resource in the write lock queue, then grant the lock to the first request in the read lock queue

Locking in InnoDB

InnoDB supports both table-level and row-level locking.

Row-level locking offers good performance and throughput for heavy workloads where multiple transactions are reading from and/or writing to the same table simultaneously, but writing different on different rows, without making each other to wait.

InnoDB also supports Multi-Version Concurrency Control (MVCC), an optimistic locking mechanisms that allows different snapshots of the data to be available to different transactions. This mechanism is used to implement the following transaction isolation levels: Read Committed and Repeatable Reads.

However, if we have the transaction isolation level set to Serializable, then the engine uses a pessimistic locking mechanism, traditionally implemented using the two-phase locking protocol.

To understand locking concepts, let’s create the sample_db database and testing_tbl table as shown in listing 01.

mysql> CREATE DATABASE sample_db;

mysql> CREATE TABLE sample_db.testing_tbl (
  id int NOT NULL AUTO_INCREMENT,
  payload varchar(128) NOT NULL,
  PRIMARY KEY (id)
)ENGINE=innodb;

mysql> INSERT INTO sample_db.testing_tbl (payload)
VALUES('row1');

Listing 01

Row-level locking

You can perform explicit row-level locking in InnoDB in two ways:

FOR UPDATE
LOCK IN SHARE MODE

Any lock held with the FOR UPDATE clause will not allow other transactions to read (using the clause FOR UPDATE), update, or delete the rows until the transaction gets committed or rolled back, releasing the lock. This is basically an exclusive/write lock.

Let’s explain these concepts with examples. Open a session to the database and acquire an exclusive lock on a data row, as shown in listing 02.

# transaction 01
mysql> START TRANSACTION;

mysql> SELECT id, payload
FROM sample_db.testing_tbl
WHERE id=1
FOR UPDATE;

Listing 02

Now open a different session, and try to read (using the clause FOR UPDATE) the same data row from the table and you’ll see that the transaction is blocked, as shown in listing 03.

# transaction 02
mysql> START TRANSACTION;

mysql> SELECT id, payload
FROM sample_db.testing_tbl
WHERE id=1
FOR UPDATE;
# transaction is blocked

Listing 03

Next try to update or delete the same row (opening new sessions and executing different transactions); you’ll see that the transactions will be put into the waiting state, as shown in listing 04.

# transaction 03
mysql> START TRANSACTION;

mysql> UPDATE sample_db.testing_tbl
SET payload='row3'
WHERE id=1;
# transaction is blocked

# transaction 04
mysql> START TRANSACTION;

mysql> DELETE FROM sample_db.testing_tbl
WHERE id=1;
# transaction is blocked

Listing 04

In order to release the lock, go back to the first transaction and execute a commit/rollback operation, and the all transactions can continue.

Any lock held with the LOCK IN SHARE MODE clause will allow other transactions to read the locked row but will not allow other transactions to write on the row until the transaction get committed or rolled back and the lock is released. This is basically a shared/read lock.

Let’s explain with examples. Open a database session and acquire an exclusive lock on a data row as shown in listing 05.

# transaction 01
mysql> START TRANSACTION;

mysql> SELECT id, payload
FROM sample_db.testing_tbl
WHERE id=1
LOCK IN SHARE MODE;

Listing 05

Now open a different session, and try to read (using the clause LOCK IN SHARE MODE) the same data row from the table and you’ll see the transaction runs fine but you can’t update or delete the data row as shown in listing 06.

# transaction 02
mysql> START TRANSACTION;
 
mysql> SELECT id, payload
FROM sample_db.testing_tbl
WHERE id=1
LOCK IN SHARE MODE;
# transaction runs fine
 
mysql> UPDATE sample_db.testing_tbl
SET payload='row3'
WHERE id=1;
# transaction is blocked

Listing 06

In order for the transaction 02 to keep on running, we need to go back to the transaction 01 and commit the changes releasing the lock.

Table-level locking

In InnoDB, transactions can also acquire locks at the table level.

In order to acquire a table lock, we can use the following statement: LOCK TABLES table_name [READ | WRITE].

In order to release a table lock, we must execute the following statement: UNLOCK TABLES.

READ/WRITE locking mode is similar to the ones explained above.

Let’s test each locking mode.

WRITE TABLE LOCK.

Notice that only the transaction that holds the WRITE table lock can read and write data from the table. Other transactions cannot read and write from the table until the WRITE table lock is released.

Open a session and execute a transaction that acquires a WRITE table lock as shown in listing 07. We can insert a new row into the table or read data inside this transaction.

# transaction 01
mysql> START TRANSACTION;
 
mysql> LOCK TABLE sample_db.testing_tbl WRITE;
 
mysql> SELECT  id, payload
FROM sample_db.testing_tbl;
# transaction runs ok
 
mysql> INSERT INTO sample_db.testing_tbl (payload)
VALUES('row2');
# transaction runs ok

Listing 07

Open a second session, create a transaction and try to read data from the table or insert a new row and we can see both operations are put into waiting state, as shown in listing 08.

# transaction 02
mysql> START TRANSACTION;
 
mysql> SELECT  id, payload
FROM sample_db.testing_tbl;
# transaction is blocked
 
# transaction 03
mysql> START TRANSACTION;
 
mysql> INSERT INTO sample_db.testing_tbl (payload)
VALUES('row3');
# transaction is blocked

Listing 08

In order for the first transaction to release the table lock, we can execute the statement shown in listing 09.

# transaction 01
mysql> UNLOCK TABLES;

Listing 09

READ TABLE LOCK.

Open a session and execute a transaction to acquire a read table lock using the statement shown in listing 10.

# transaction 01
mysql> START TRANSACTION;
 
mysql> LOCK TABLE sample_db.testing_tbl READ;

Listing 10

The next step is to open a new session and execute another transaction trying to insert a new row into the table. We can see that the transaction is blocked on executing the INSERT operation because the first transaction already holds the read lock, not allowing the writing of any data.

# transaction 02
mysql> START TRANSACTION;
 
mysql> SELECT  id, payload
FROM sample_db.testing_tbl;
# transaction runs ok
 
mysql> INSERT INTO sample_db.testing_tbl (payload)
VALUES('row1');
# transaction is blocked

Listing 11

In order for the first transaction to release the table lock, we can execute the statement shown in listing 12.

# transaction 01
mysql> UNLOCK TABLES;

Listing 12

Finally, we can check that the INSERT operation from the second transaction is really executed.

Conclusion

In this article, I've explained explicit locking mechanisms in InnoDB. Now you can apply these concepts in your own database solutions.