MySQL locks

On a habr the principles of work of MySQL are often discussed. This habratopik is devoted to the lock mechanisms used in MySQL. The topic will help beginners to learn MySQL and, to some extent, experienced habra-people.

MySQL locking mechanism

The simultaneous access of several clients to the data warehouse can lead to errors of various types. For example, simultaneous reading by one client and writing by another client of the same row in the table is likely to lead to a failure or reading of incorrect data. Locking mechanisms allow avoiding situations of simultaneous access to data, regulating the mechanism of user interaction between themselves.

MySQL on behalf of one of the clients locks on a certain resource, while other clients wait for the lock to be released. A lock can be at the table level (the table is locked) or at the row level (certain table rows are blocked). The MyISAM storage engine (used by default) implements table locking, while the InnoDB engine uses row-based locking. Line-by-line locking is achieved by complicating the structure of the storage: in MyISAM, the structure of the data file is a simple enumeration of table rows, while the InnoDB storage is structured and supports multiversion data. Therefore, InnoDB wins in applications in which multi-threaded data changes to the same table, despite the necessary losses for the maintenance of a more complex storage.

There are two types of locks: read and write.

1. If A wants to read data, then other clients can also read data, but no one can write until A finishes reading (read lock).
2. If A wants to write data, then other clients should neither read nor write this data until A finishes (write lock).

A lock can be imposed explicitly or implicitly.

1. If the client does not explicitly assign a lock, the MySQL server implicitly sets the required type of lock for the duration of the expression or transaction. For example, in the case of executing a SELECT statement, the server will set READ LOCK, and in the case of UPDATE, WRITE LOCK. In case of implicit locking, the locking level depends on the type of data storage: for MyISAM, MEMORY and MERGE, the entire table is locked, for InnoDB - only the rows used in the expression (if the set of these rows can be uniquely determined by the primary key values ​​- otherwise, the entire table is locked )
2. Often there is a need to execute several requests in a row without the intervention of other clients at this time. Implicit locking is not suitable for these purposes, since it is set only for the duration of a single request. In this case, the client can explicitly assign and then cancel the lock using the LOCK TABLES and UNLOCK TABLES expressions. Explicit locking always locks the entire table, regardless of storage mechanism.

Using explicit locks

In the case of an explicit lock, you can get a performance gain due to a one-time lock assignment and a delay in writing updated indexes until the lock time. When assigning an explicit lock, the table name and type of lock are indicated: LOCK TABLES Country READ, City WRITE; The UNLOCK TABLES statement has no arguments and removes all locks set explicitly in the current session.

Pay attention to the following features of explicit locks:

• While the client holds an explicit lock, it cannot use other tables, so you need to lock everything you need at once (in one expression), since reusing the LOCK TABLES statement cancels previously made locks.
• The lock can be set to view (VIEW) starting with version 5.0.6. For earlier versions, it is necessary to set a lock on all the tables included in the view.
• Termination of the connection or termination of a session automatically removes all the locks established within the framework of this session.
• Table locking can be broken by a transaction and vice versa. START TRANSACTION implicitly performs UNLOCK TABLES and vice versa LOCK TABLES rolls back an incomplete transaction.
• To set a lock, you must have LOCK TABLES and SELECT privileges on each table to be locked.
• If one of the required tables is locked in another session, the lock statement will not be executed until all tables are freed.

Types of Locks:

• READ - locks the table for reading. All clients can receive data at the same time, but no one can change them, even the client who set the lock.
• WRITE - locks the table for writing. Only the client who set the lock can receive and modify data.
• READ LOCAL - locks the table for reading, but allows data insertion (INSERT). Applicable only to MyISAM tables that do not have holes formed by modifying or deleting rows. In this case, new data is added to the end of the table. If the table has holes, they can be fixed using the OPTIMIZE TABLE statement.
• LOW_PRIORITY WRITE - locks a table for writing, but while waiting for a lock, skips those clients who are queued to receive a READ lock. While waiting for a lock, new incoming READ type lock requests are also skipped forward, which can potentially result in never being written (if there are always clients in the read queue).

Note : the system variable concurrent_inserts determines whether data can be added to the end of a MyISAM table locked for reading. By default, this variable is 1, which means the ability to add data if there are no holes in the table. Thus, in the event of an implicit lock, READ LOCAL is set for MyISAM tables, not READ. A value of concurrent_inserts equal to 0 prevents data from being added in parallel with reading, and a value of 2 allows data to be inserted at the end of the table even if there are holes in the table.

Conclusion

The article covers only the basic issues of using locks in MySQL. Transactions, isolation levels, advisory lock, etc. are not covered. Write in the comments which of the questions you are interested in.

Crosspost Locks in MySQL with Webew.