MySQL Locks: A Comprehensive Guide 101

MySQL is the leading SQL-based RDBMS system developed by Oracle. It provides advanced functionality along with an easy interface for database management.

This article talks in-depth about the MySQL Locks feature.

What is MySQL?

Introduced in 1995, MySQL is a powerful database management system. This DBMS makes use of SQL (Structured Query Language) to govern data and data-associated functions are carried out. MySQL is an Open-source software that means it can essentially be accessed via way of means of everybody who desires to use the service. However, there are sure accurate variations of MySQL designed for your precise commercial enterprise needs. You will pay a month-to-month price to apply those MySQL model capabilities. Due to its many capabilities and benefits, today`s establishments rely upon the MySQL platform for scalable and dependable data solutions.

 The simplicity by means of MySQL definitely has a bonus over Oracle databases and Microsoft SQL Server. In addition, you could enforce any programming language at no cost with this DBMS platform. Another benefit of the usage of MySQL is that it may be blended with Linux, Unix, Windows, and different running systems. In addition, MySQL lets you select the deployment mode so that you can use it after putting it online or for your nearby system.

Key Features of MySQL

MySQL has ended up a famous DBMS available in the marketplace with the subsequent capabilities:

• High Performance: The MySQL engine gives a completely unique mixture of excessive processing pace and an easy-to-use interface. In addition, you could host more than one client in an equal time, taking into consideration the quicker MySQL get admission from anywhere.
• Compatibility: In addition to secure, low-latency facts transactions, MySQL additionally offers surroundings for putting in more than one net improvement tool.
• Scalability: The MySQL platform facilitates you to scale up or down your facts load at any time. The platform additionally seamlessly adapts to the maximum famous running systems inclusive of Linux, OS X, and Windows.

What are MySQL Locks?

A MySQL Locks is nothing but a flag that can be assigned to a table to alter its properties. MySQL allows a table lock that can be assigned by a client-server to prevent other sessions from being able to access the same table during a specific time frame.

A client can acquire or release MySQL Locks only for his session. this means that locks for another session or release locks held by another session cannot be accessed by the client.

MySQL Locks: Syntax

LOCK TABLES
    table_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

LOCK Tables for current client sessions can acquire locks only when declared explicitly. Base tables or views also support the use of locks. For utilizing the locks and objects to be locked you must have LOCK Tables privileges and Select Privileges.

As soon as you lock a table using the Lock table any tables used in triggers are also locked implicitly. any tables related by a foreign key constraint are opened and locked implicitly. For consecutive shared-nothing WRITE, the lock is taken on related tables that are involved in the operation.

To acquire table locks within the current session, use the Lock Table statement, which acquires metadata locks.

MySQL Locks: Examples

MySQL Locks: Example 1:

mysql> LOCK TABLES t1 READ;
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
mysql> SELECT COUNT(*) FROM t2;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

The error of t2 has occurred because it was not locked in the lock statement.

MySQL Locks: Example 2:

mysql> LOCK TABLE t WRITE, t AS t1 READ;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

Locks cannot be accessed multiple times with the same name. Multiple aliases can be used with the same name to overcome this issue.

The error occurs for the first INSERT because there are two references to the same name for a locked table. The second INSERT succeeds because the references to the table use different names.

MySQL Locks: Example 3:

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

If your statements refer to a table by means of an alias, you must lock the table using that same alias.

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

lock a table using an alias, you must refer to it in your statements using that alias

What are 2 Types of MySQL Locks?

This section will discuss the two types of MySQL Locks that can be put to use for your unique business use case to achieve peak efficiency:

• MySQL Locks: Read Locks
• MySQL Locks: Write Locks

MySQL Locks: Read Locks

The following are the features of the READ Lock:

• MySQL allows multiple sessions to bi in READ lock for a table concurrently. And it also allows for other sessions to read the table without acquiring the lock.
• If the session holds the READ lock on a table, they cannot perform a write operation on it. It is because the READ lock can only read data from the table. All other sessions that do not acquire a READ lock are not able to write data into the table without releasing the READ lock. The write operations go into the waiting states until we have not released the READ lock.
• When the session is terminated normally or abnormally, MySQL implicitly releases all types of locks onto the table. This feature is also relevant for the WRITE lock.

MySQL Locks: Write Locks

The following are the features of a WRITE Lock:

• It is the session that holds the lock of a table and can read and write data both from the table.
• It is the only session that accesses the table by holding a lock. And all other sessions cannot access the data of the table until the WRITE lock is released.

The following lock types are available:

READ [LOCAL] lock:

• The session that holds the lock can read the table but not write it.
• Multiple sessions can acquire a READ lock for the table at the same time.
• Other sessions can read the table without explicitly acquiring a READ lock.
• The LOCAL modifier enables nonconflicting INSERT statements (concurrent inserts) by other sessions to execute while the lock is held. However, READ LOCAL cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock.

[LOW_PRIORITY] WRITE lock:

• The session that holds the lock can read and write the table.
• Only the session that holds the lock can access the table. No other session can access it until the lock is released.
• Lock requests for the table by other sessions block while the WRITE lock is held.
• The LOW_PRIORITY modifier has no effect. In previous versions of MySQL, it affected locking behavior, but this is no longer true. It is now deprecated and its use produces a warning.

WRITE locks have higher priority when compared to READ locks to ensure that updates are processed asap. This means that if a session has obtained a READ lock and simultaneously another session requests a WRITE lock, the session with READ lock requests has to wait until the session that requested the WRITE the lock has obtained the lock and released it.

If MySQL Locks Table statement must wait due to locks held by other sessions on any of the tables, it blocks until all locks can be acquired.

A session that requires locks must acquire all the locks that it needs in a single Lock statement. While the locks thus obtained are held, the session can access only the locked tables.

How to unlock the table in MySQL?

When the MySQL locks held by a session get released, they are all released at the same time. A session might release its locks explicitly, or it might be released implicitly under certain conditions.

• If a session issues a LOCK TABLES statement to acquire a lock while already holding MySQL locks, its existing locks are released implicitly before the new locks get granted.
• A session can release its locks explicitly with the help of UNLOCK TABLES.
• If a session begins a transaction, an implicit UNLOCK TABLES is performed, which causes existing MySQL locks to be released.

If the connection for a client session gets terminated, whether normally or abnormally, the server will implicitly release all MySQL locks held by the session, If the client reconnects, the MySQL locks are no longer in effect.

How to Lock Tables and Triggers in MySQL?

If you lock a table explicitly with LOCK TABLES, any tables used in triggers also get locked implicitly:

• The lock on a table used in a trigger depends on whether the table is used only for reading. If so, a read lock suffices. Otherwise, a write lock is used.
• The MySQL locks are taken at the same time as those acquired explicitly with the LOCK TABLES statement.
• If a table is locked explicitly for reading with LOCK TABLES but needs to be locked for writing since it might be modified within a trigger, a write lock might be taken instead of a read lock.

Say, if you lock two tables, t1 and t2, using this statement:

LOCK TABLES t1 WRITE, t2 READ;

If t1 or t2 possesses any triggers, tables used within the triggers also get locked. Say, if t1 has a trigger defined like this:

CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW
BEGIN
  UPDATE t4 SET count = count+1
      WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);
  INSERT INTO t2 VALUES(1, 2);
END;

The result of the LOCK TABLES statement is that t1 and t2 get locked because they appear in the statement, and t3 and t4 are locked because they are used in the trigger:

• t2 is locked for writing, even though the request was for a READ lock. This occurs because t2 is inserted within the trigger, so the READ request gets converted to a WRITE request.
• t1 is locked for writing as per the WRITE lock request.
• t3 is locked for reading since it is only read from within the trigger.
• t4 is locked for writing since it might be updated within the trigger.

Interaction of Table Locking and Transactions

UNLOCK TABLES and LOCK TABLES interact with the use of transactions as follows:

• UNLOCK TABLES implicitly commits any active transaction, but only if LOCK TABLES have been used to acquire table locks. For instance, in the following set of statements, UNLOCK TABLES releases the global read lock but doesn’t commit the transaction because no table locks are in effect:

FLUSH TABLES WITH READ LOCK;
START TRANSACTION;
SELECT ... ;
UNLOCK TABLES;

• LOCK TABLES is not transaction-safe and implicitly commit any active transaction before attempting to lock the tables.
• Beginning a transaction, for instance, START TRANSACTION implicitly commits any current transaction and releases existing MySQL locks.
• FLUSH TABLES WITH READ LOCK acquires a global read lock instead of table locks, therefore, it is not subject to the same behavior as UNLOCK TABLES and LOCK TABLES with respect to table locking and implicit commits.
• ROLLBACK doesn’t release table locks.
• Other statements that implicitly cause transactions to be committed do not release existing table locks.
• The right way to use UNLOCK TABLES and LOCK TABLES with transactional tables, such as InnoDB tables, is to start a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES and to not call UNLOCK TABLES until you commit the transaction explicitly. For instance, if you need to write table t1 and read from table t2, you can execute this:

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

When you summon LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own MySQL lock. InnoDB releases its internal table lock at the next commit, but for MySQL to release its table lock, you have to summon UNLOCK TABLES. You should not have autocommit = 1, because then InnoDB releases its internal table lock immediately after the call of LOCK TABLES, and deadlocks can very easily happen. InnoDB does not acquire the internal table lock at all if autocommit = 1, to help old applications avoid unnecessary deadlocks.

What are the MySQL Table Locking Restrictions and Conditions?

You can leverage KILL to terminate a session that is waiting for a table lock. UNLOCK TABLES and LOCK TABLES cannot be used within stored programs. Tables present in the performance_schema database cannot be locked with LOCK TABLES, except the setup_xxx tables.

The scope of a MySQL lock generated by LOCK TABLES is a single MySQL server. MySQL Locks aren’t compatible with the NDB cluster, which has no way of enforcing an SQL-level across various instances of mysqlId. The following statements are prohibited while a LOCK TABLES statement is in effect: CREATE VIEW, DROP VIEW, CREATE TABLE, CREATE TABLE … LIKE, along with DDL statements on stored functions and events and procedures.

Usually, you don’t need to lock tables, because all the single UPDATE statements are atomic, so, no other session can interfere with any other currently executing SQL statement.

• If you are using tables for a nontransactional storage engine, you will have to use LOCK TABLES to ensure that no other session modifies the tables between a SELECT and an UPDATE.
• If you are going to run various operations on a set of MyISAM tables, it is much faster to lock the tables you are going to use. Locking MyISAM tables will speed up updating, inserting, or deleting them because MySQL does not flush the key cache for the locked tables until UNLOCK TABLES is called.

Conclusion

This article gives a comprehensive guide on MySQL Locks in detail. It provide us the in-depth of MYSQL Table Locks and MYSQL Read Locks.

MySQL is a trusted source that a lot of companies use as it provides many benefits but transferring data from it into a data warehouse is a hectic task. The Automated data pipeline helps in solving this issue and this is where Hevo comes into the picture. Hevo Data is a No-code Data Pipeline and has awesome 150+ pre-built Integrations that you can choose from.

Hevo can help you Integrate your data from numerous sources and load them into a destination to Analyze real-time data with a BI tool such as Tableau. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure.

Sign Up for a 14-day free trial and see the difference!

Share your experience of learning about MySQL Locks in the comments section below.

FAQs