MySQL InnoDB and MyISAM locks

Lecture



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.

Bannerokrutilki on MySQL

The title "banner banner on MySQL" is entitled a note on examples of incorrect and inefficient use of the MySQL server. A typical example of a system that often works inefficiently with MySQL is the banner ad manager.

As an example, the PHP Ad Manager program was analyzed. The program was chosen randomly from dozens like her on sourceforge.net.

The main features of the program are listed below:

  • Manage banner ads on multiple domains
  • Counting the number of hits (hits) and clicks
  • MySQL statistics storage
  • Online statistics update

Banner Twist Requests

Below is an abbreviated and simplified list of requests that is performed by the PHP Ad Manager system at each banner display.

Get the domain for which the banner is scrolling

select * from domains where ...

Getting a list of ad units that a user can show on this domain

 select * from ads where active = 'Y' and expiredate > 'ТЕКУЩЕЕ-ВРЕМЯ' and domains LIKE '%ДОМЕН%' order by lastdisplay;

Updating the last time this ad was shown:

 update ads set lastdisplay = 'ТЕКУЩЕЕ-ВРЕМЯ', hits='КОЛИЧЕСТВО ХИТОВ' WHERE adid = 'ИДЕНТИФИКАТОР'

Logging information

 insert into adlog SET adid = 'ИД-БАННЕРА', type = 'hit', remotehost = '....', remoteaddr = '....', site = '.....', entrydate = '....';

MySQL table locks

In order to deal with the causes of possible problems, it is necessary to shed some more detail on the issue of working with table locks in MySQL.

According to the MySQL documentation, MySQL uses table-level locks for MyISAM, and row-level locks for InnoDB tables. MySQL supports two types of locks: write and read. Write locks take precedence over read locks. This, on the one hand, leads to the fact that data insertion / update requests do not “hang” with a large number of read requests. On the other hand, with a large number of data update requests, read requests can wait their turn for a very long time.

To demonstrate the problem, consider two examples. The first example is shown in the figure below. In this case, four queries per sample (SELECT) come to the MySQL server almost simultaneously. It can be seen that since SELECT queries have the ability to execute simultaneously, the wait time for the result of each query depends only on the execution time of the query itself.

MySQL InnoDB and MyISAM locks

In the second case, a situation is schematically presented, in which the same MySQL table is almost simultaneously addressed by 4 queries, of which two queries are sampling requests, and the other two are data refresh requests. Due to the fact that update requests are queued for execution before sampling requests, and also because they cannot be executed simultaneously with other requests in the MyISAM table, the waiting time for the SELECT result is significantly increased.

MySQL InnoDB and MyISAM locks

Additionally, it should be noted that if the first request turned out to be “heavy” (with a long execution time), this would further aggravate the situation of the second case. If neither UPDATE, all SELECTs would be executed at the same time, however, the presence of one UPDATE causes it to split the request queue into two: before and after, and the SELECTs in the third stage will not be executed until the first two stages are completed.

An estimate of the number of blocked requests can be performed as follows:

 mysql> SHOW STATUS LIKE 'Table%'; +-----------------------+---------+ | Variable_name | Value | +-----------------------+---------+ | Table_locks_immediate | 1151552 | | Table_locks_waited | 15324 | +-----------------------+---------+

The first row of the table displays the number of lock requests that were satisfied immediately, and the second row shows the number of queries that had to be expected before obtaining the required access to the table.

Performance Testing Utility

For performance testing, you can use the program mysqlslap, which is part of MySQL 5.1.4 and higher (for lenny is available in the dotdeb repository).

Ways to optimize performance

UPDATE priority down

It can be seen from the above description that problems arise, among other things, because the priority of UPDATE operations is higher than the priority of operations on data sampling. A simple solution that is likely to temporarily help alleviate the problem is to use the UPDATE LOW_PRIORITY query instead of an UPDATE query. A detailed description of the command syntax is available in the official documentation.

Using a temporary table

In the situation of a large number of INSERT queries that can block SELECT queries, MySQL developers recommend using a temporary table, the data from which can be transferred to the main table with a certain periodicity:

 mysql> LOCK TABLES real_table WRITE, temp_table WRITE; mysql> INSERT INTO real_table SELECT * FROM temp_table; mysql> DELETE FROM temp_table; mysql> UNLOCK TABLES;

Using non-relational databases

Non-relational databases (at least according to the statements of their developers) are more suitable for working with information in the "number of updates is comparable to the number of samples" than MySQL. As an example, mongodb may be suitable for such use.

mongodb is able to perform cheap "in-place" update operations (incrementing counters, etc.) without actually transferring data over the network, and also has an insert mode "upsert" (update an object, or create it if such an object is not found yet). For details on the use of these commands, it is best to refer to the documentation.

Below are two links to articles on the MongoDB blog (in English):

  • fast updates with mongodb update in place
  • mongodb is fantastic for logging

When developing new systems

When developing new systems, try to avoid an architecture in which the number of data update requests is comparable to the number of SELECT requests. If this is not possible, try as much as possible to spread these operations over time, or use other techniques described above.

mysql

Comments

To leave a comment
If you have any suggestion, idea, thanks or comment, feel free to write. We really value feedback and are glad to hear your opinion.
To reply

Databases, knowledge and data warehousing. Big data, DBMS and SQL and noSQL

Terms: Databases, knowledge and data warehousing. Big data, DBMS and SQL and noSQL