MySQL locks

On Habré often discussed the principles of MySQL. This habratopic is devoted to locking mechanisms used in MySQL. The topic will help beginners to learn MySQL and, to some extent, experienced habra people.

MySQL blocking mechanism

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 another client of the same row of a table with a high probability will lead to failure or reading of incorrect data. Locking mechanisms allow you to avoid situations of simultaneous access to data, regulating the mechanism of user interaction with each other.

MySQL on behalf of one of the clients imposes a lock on a specific resource, while other clients are waiting for the lock to be released. A lock can be at the table level (the table is locked) or at the row level (certain rows of the table are blocked). The MyISAM storage engine (used by default) has a table lock, and the InnoDB mechanism is row-based. Line-by-line locking is achieved by complicating the storage structure: in MyISAM, the data file structure is a simple enumeration of table rows, while the InnoDB storage is structured and supports multiversion of data. Therefore, InnoDB wins in applications in which there is a multithreaded change of data in the same table, despite the necessary maintenance losses of more complex storage.
')
There are two types of locks: read and write.

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

The lock can be imposed explicitly or implicitly.

1. If the client does not explicitly assign the lock, the MySQL server implicitly sets the required lock type at the time the expression or transaction is executed. For example, in the case of a SELECT statement, the server will set READ LOCK, and in the case of UPDATE, WRITE LOCK. For implicit locking, the lock level depends on the type of data storage: for MyISAM, MEMORY and MERGE, the entire table is locked, for InnoDB, only the lines used in the expression (if the set of these lines can be uniquely determined by the primary key values, otherwise, the entire table is blocked ).
2. Often there is a need to perform several requests in a row without the intervention of other customers at this time. Implicit blocking is not suitable for these purposes, as it is set only for the duration of the execution 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 the storage mechanism.

Using explicit locks

In the case of explicit blocking, you can gain performance gains by assigning the blocking once and delaying the recording of the updated indexes until the blocking time. When assigning an explicit lock, the table name and type of lock are specified: LOCK TABLES Country READ, City WRITE; The UNLOCK TABLES operator has no arguments and removes all locks that are explicitly set during the current session.

Note the following features of explicit locks:

• As long as the client holds an explicit lock, he cannot use other tables, so you need to lock everything you need right away (with one expression), since reusing the LOCK TABLES operator cancels the locks that were previously made.
• The lock can be set to view (VIEW) since version 5.0.6. For earlier versions, it is necessary to set a lock on all tables included in the view.
• Disconnecting the connection, or ending the session, automatically removes all locks set during the 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 the lock, you must have LOCK TABLES and SELECT privileges on each locked table.
• If one of the required tables is locked in another session, then 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 it, even the client who has set the lock.
• WRITE - locks the table for writing. Only the client who has set the lock can receive and modify data.
• READ LOCAL - locks the table for reading, but allows data insertion (INSERT). Applies only to MyISAM tables that do not have holes resulting from changing or deleting rows. In this case, new data is added to the end of the table. If the table has holes, then they can be eliminated using the OPTIMIZE TABLE statement.
• LOW_PRIORITY WRITE - locks the table for writing, but while waiting for a lock, skips those clients who are in the queue to receive a lock of type READ. While waiting for a lock, new incoming READ lock requests are also skipped ahead, which could potentially lead to a write never being made (if there are always clients in the read queue).

Note : The concurrent_inserts system variable defines the ability to add data to the end of the MyISAM table that is locked for reading. By default, this variable is 1, which means that you can add data if there are no holes in the table. Thus, in the case of implicit locking, READ LOCAL is set for MyISAM tables and not READ. A value of concurrent_inserts equal to 0 prohibits adding data in parallel with reading, and equal to 2 allows inserting data at the end of the table even if there are holes in the table.

Conclusion

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

Crosspost Lock in MySQL with Webew.