Mysql Lock

If you’ve been working with MySQL for some time, you have probably heard the terms “table-level locking” and “row-level locking”. These terms refer to the lock granularity in MySQL — in this tutorial we will explain what they mean and what they can be used for.

What is Lock Granularity in MySQL?

Each MySQL storage engine supports different levels of granularity for their locks. MySQL has three lock levels: row-level locking, page-level locking and table-level locking. Each MySQL storage engine implements locking differently giving you some distinct advantages and disadvantages. We’ll first look into what lock granularity is, then look into how everything works in different storage engines.

Broadly speaking, locks in MySQL fall into one of these categories. Locks can be:

• Page-level — such types of lock granularities were available in older engines of MySQL, specifically BDB, which is now obsolete as of MySQL 5.1. In short, BDB was a storage engine included in the older versions of MySQL and it was a transactional storage engine which performed page-level locks. Since these types of lock granularities are no longer used we will not go in-depth into them here, but in general, these locks are limited to the data and indexes that reside on a particular page. If you want to learn more about BDB, the page on MariaDB should provide some more information.
• Table-level — MySQL uses table-level locking for all storage engines except InnoDB.
• Row-level — row-level locking is used by InnoDB.

The Advantages and Disadvantages of Table-level Locking

MySQL uses table-level locking for all storage engines except InnoDB meaning that table-level locking is used for tables running the MyISAM, MEMORY and MERGE storage engines, permitting only one session to update tables at a time. Table-level locks have some distinct advantages over row-level locks (for example, table-level locking in general requires a little less memory than row-level locking because row-level locking requires some memory per row (or group) of the rows that are locked and it’s usually fast because only one lock is involved. Table write locks are put on a table if there are no locks on it — if there are pre-existing locks on the table in question, the table lock requests are put in the read request queue. It’s worth mentioning that table-level locking has some distinct disadvantages unique to itself too — for example, it might not be a very good fit for applications that require a lot of transactions that go “back and forth” (e.g., an online banking application) because only one session can write to a table at any one time and some of the tables that support table-level locking (such as MyISAM) do not support the ACID model.

Here’s an example: imagine a banking application that uses two tables in a database — let’s say those tables are called “checking” and “savings”. You need to move $100 from a person’s checking account to his savings account. Logically, you would perform the following steps:

1. Make sure the account balance is greater than $100.
2. Subtract $100 from the checking account.
3. Add $100 to the savings account.

To perform these actions, you would need a couple of queries, for example:

SELECT balance FROM checking WHERE account_id = 123;
UPDATE checking SET balance = balance - 100 WHERE account_id = 123;
UPDATE savings SET balance = balance + 100 WHERE account_id = 123;        

These queries might look simple, but if you use MyISAM (we use MyISAM as an example as it’s one of the primary storage engines that supports table-level locks), you should be familiar with the fact that the engine doesn’t support ACID either which means that if the database server crashes while performing any of those queries, you’re out of luck: people could end up with cash in both accounts or in neither one of them. The only engine that supports ACID-based transactions in MySQL is InnoDB, so if you need a lot of reliable transactions, it might be worth looking into it. InnoDB also supports row-level locking — this is what we will look into now.

The Advantages and Disadvantages of Row-level Locking

MySQL uses row-level locking for InnoDB tables to support simultaneous write access by multiple sessions. Some of the advantages of using row-level locking include the ability to lock a single row for long periods of time and fewer lock conflicts when many threads access different rows. However, row-level locking has disadvantages too: one of them is that row-level locking usually takes up more memory than page-level or table-level locking, it’s also usually slower than page-level or table-level locks because the engine must acquire more locks. InnoDB is one of the engines that is supporting a row-level locking mechanism: it’s also ACID compliant meaning that it is a good fit for transaction-based applications (refer to the example above). Now we will look into how lock granularity works in one of MySQL storage engines.

Lock Granularity and Deadlocks

Lock granularity and locking levels in MySQL can be a great thing, but they can also cause problems. One of the most frequent problems caused by lock granularity are deadlocks — a deadlock occurs when different MySQL transactions are unable to proceed because each of them holds a lock that the other needs. Thankfully, when using the InnoDB storage engine, deadlock detection is enabled by default — when a deadlock is detected, InnoDB automatically rolls back a transaction. If you encounter deadlocks when dealing with lock granularity in MySQL, don’t fret — consider simply restarting your transaction.

Hands On

A lock is a flag associated with a table. MySQL allows a client session to explicitly acquire a table lock to prevent other sessions from accessing the same table during a specific period.

A client session can acquire or release table locks only for itself. A client session cannot acquire or release table locks for other client sessions.

Before we move on, let’s create a table named messages for practicing with the table locking statements.

CREATE TABLE messages ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    message VARCHAR(100) NOT NULL
);Code language: SQL (Structured Query Language) (sql)        

MySQL LOCK TABLES statement

The following LOCK TABLES statement explicitly acquires a table lock:

LOCK TABLES table_name [READ | WRITE]Code language: SQL (Structured Query Language) (sql)        

In this syntax, you specify the name of the table that you want to lock after the LOCK TABLES keywords. In addition, you specify the type of lock, either READ or WRITE.

MySQL allows you to lock multiple tables by specifying a list of comma-separated table names with lock types that you want to lock after the LOCK TABLES keywords:

LOCK TABLES table_name1 [READ | WRITE], 
            table_name2 [READ | WRITE],
             ... ;Code language: SQL (Structured Query Language) (sql)        

MySQL UNLOCK TABLES statement

The UNLOCK TABLES statement releases any table locks held by the current session:

UNLOCK TABLES;Code language: SQL (Structured Query Language) (sql)        

READ Locks

A READ lock has the following features:

• A READ lock for a table can be acquired by multiple sessions at the same time. In addition, other sessions can read data from the table without acquiring the lock.
• The session that holds the READ lock can only read data from the table, but cannot write. And other sessions cannot write data to the table until the READ lock is released. The write operations from another session will be put into the waiting states until the READ lock is released.
• If the session is terminated, either normally or abnormally, MySQL will release all the locks implicitly. This feature is also relevant to the WRITE lock.

Let’s take a look at how the READ lock works in the following scenario.

First, connect to the database in the first session and use the CONNECTION_ID() function to get the current connection id as follows:

SELECT CONNECTION_ID();Code language: SQL (Structured Query Language) (sql)        

Then, insert a new row into the messages table.

INSERT INTO messages(message) 
VALUES('Hello');Code language: SQL (Structured Query Language) (sql)        

Next, query the data from the messages table.

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)        

After that, acquire a lock using the LOCK TABLE statement.

LOCK TABLE messages READ;Code language: SQL (Structured Query Language) (sql)        

Finally, try to insert a new row into the messages table:

INSERT INTO messages(message) 
VALUES('Hi');Code language: SQL (Structured Query Language) (sql)        

MySQL issued the following error:

Error Code: 1099. Table 'messages' was locked with a READ lock and can't be updated.Code language: SQL (Structured Query Language) (sql)        

So once the READ lock is acquired, you cannot write data to the table within the same session.

Let’s check the READ lock from a different session.

First, connect to the database and check the connection id:

SELECT CONNECTION_ID();Code language: SQL (Structured Query Language) (sql)        

Next, query data from the messages table:

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)        

Then, insert a new row into the messages table:

INSERT INTO messages(message) 
VALUES('Bye');Code language: SQL (Structured Query Language) (sql)        

Here is the output:

The insert operation from the second session is in the waiting state because a READ lock is already acquired on the messages table by the first session and it has not been released yet.

From the first session, use the SHOW PROCESSLIST statement to show detailed information:

SHOW PROCESSLIST;Code language: SQL (Structured Query Language) (sql)        

After that, go back to the first session and release the lock by using the UNLOCK TABLES statement. After you release the READ lock from the first session, the INSERT operation in the second session is executed.

Finally, check the data of the messages table to see if the INSERT operation from the second session was executed.

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)        

Write Locks

A WRITE lock has the following features:

• The only session that holds the lock of a table can read and write data from the table.
• Other sessions cannot read data from and write data to the table until the WRITE lock is released.

Let’s go into detail to see how the WRITE lock works.

First, acquire a WRITE lock from the first session.

Table Lock

LOCK TABLE messages WRITE;Code language: SQL (Structured Query Language) (sql)        

Then, insert a new row into the messages table.

INSERT INTO messages(message) 
VALUES('Good Morning');Code language: SQL (Structured Query Language) (sql)        

It worked.

Next, query data from the messages table.

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)        

It also works.

After that, from the second session, attempt to write and read data:

INSERT INTO messages(message) 
VALUES('Bye Bye');        

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)        

MySQL puts these operations into a waiting state. You can check it using the SHOW PROCESSLIST statement:

SHOW PROCESSLIST;Code language: SQL (Structured Query Language) (sql)        

Finally, release the lock from the first session.

UNLOCK TABLES;Code language: SQL (Structured Query Language) (sql)        

You will see all pending operations from the second session executed and the following picture illustrates the result:

Read vs. Write locks

• Read locks are “shared” locks that prevent a write lock is being acquired but not other read locks.
• Write locks are “exclusive ” locks that prevent any other lock of any kind.

Row Lock

1) SELECT … FOR UPDATE

Any lock placed with the FOR UPDATE will not allow other transactions to read, update or delete the row. Other transaction can read this rows only once first transaction get commit or rollback.

Example Query:

[cc lang=”mysql”]

; This Query will not allow other transaction to

; Read the row with id=10

; It will not allow update or delete too.

SELECT * FROM table_name WHERE id=10 FOR UPDATE;

[/cc]

2) LOCK IN SHARE MODE

Any lock placed with LOCK IN SHARE MODE will allow other transaction to read the locked row but it will not allow other transaction to update or delete the row.

Other transaction can update or delete the row once the first transaction gets commit or rollback.

Example Query:

[cc lang=”mysql”]

; This Query will not allow other transaction to

; Update or Delete the row with id=10

; It will allow read to other transaction.

SELECT * FROM table_name WHERE id=10 LOCK IN SHARE MODE;

[/cc]