Shulou(Shulou.com)05/31 Report--

This article mainly explains the "introduction of the relevant principles of the MySQL database locking mechanism". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the introduction of the relevant principles of the MySQL database locking mechanism".

Options different from row-level or page-level locking:

Version (for example, the technology used in MySQL for parallel inserts), in which there can be one write operation and many read operations. This table supports different views of data dependencies, depending on when the access begins. Other common terms are "time tracking", "write replication", or "copy on demand".

On-demand replication takes precedence over page-or row-level locking in many cases. In the worst case, however, it may use more memory than regular locking.

In addition to row-level locking, you can use application-level locking, such as using GET_LOCK () and RELEASE_LOCK () in MySQL. These are recommended locks, and they can only work in well-functioning applications.

To achieve maximum locking speed, MySQL uses table locking (rather than page, row, or column locking) for all storage engines except InnoDB and BDB. For InnoDB and BDB tables, if you use LOCK TABLES to explicitly lock the table, MySQL only uses table locking; if you do not use LOCK TABLES, because InnoDB uses automatic row-level locking and BDB uses page-level locking to ensure transaction isolation.

But for large tables, for most applications, table locking is better than row locking, but there are some drawbacks. Table locking allows many threads to read from a table at the same time, but if a thread wants to write to the table, it must first get exclusive access. During the update, all other threads that want to access the table must wait until the update is complete.

Table updates are generally considered more important than table retrieval, so they are given higher priority. This should ensure that the activity of updating a table does not "starve to death", even if there is heavy SELECT activity on the table.

Table locking can cause problems in this case, such as when the thread is waiting, because the hard disk is full and there must be free space before the thread can process it. In this case, all threads that want to access the table in question are also set to wait until more hard disk space is available.

There is also a problem with table locking in the following situations:

A customer issues a long-running query.

Then another customer updates the same table. The customer must wait until the SELECT is complete.

Another customer issues another SELECT statement on the same table. Because UPDATE has higher priority than SELECT, the SELECT statement waits for UPDATE to complete and the first SELECT to complete.

Here are some ways to avoid or reduce competition caused by table locking:

An attempt was made to make SELECT statements run faster. You may have to create some summary tables to do this.

Start d with-- low-priority-updates. This will give all statements that update (modify) a table a lower priority than the SELECT statement. In this case, the second SELECT statement in the previous case will be executed before the UPDATE statement without waiting for the first SELECT to complete.

You can use the SET LOW_PRIORITY_UPDATES=1 statement to specify that all updates in a specific connection should use a low priority.

You can use the LOW_PRIORITY attribute to give a particular INSERT, UPDATE, or DELETE statement a lower priority.

You can use the HIGH_PRIORITY attribute to give a particular SELECT statement a higher priority.

Specify a low value for the max_write_lock_count system variable to start mysqld to force MySQL to temporarily raise the priority of all SELECT statements waiting for a table after a specific number of inserts are completed. This allows READ locks to be given after a certain number of WRITE locks.

If you have questions about combining INSERT with SELECT, switch to using the new MyISAM tables because they support concurrent SELECT and INSERT.

If you mix inserts and deletes on the same table, INSERT DELAYED will help a lot.

If you have problems mixing SELECT and DELETE statements on the same table, the LIMIT option of DELETE can be helpful.

Using SQL_BUFFER_RESULT on the SELECT statement can help reduce the table lock time.

You can change the lock code in mysys/thr_lock.c to use a single queue. In this case, write locking and read locking will have the same priority, which will be helpful for some applications.

Here are some tips on table locking in MySQL:

If you do not mix updates with selections that need to be checked for many rows in the same table, you can do so in parallel.

You can use LOCK TABLES to speed up, because many updates in a lock are much faster than updates without a lock. It can also be helpful to split the contents of a table into several tables.

If you encounter speed problems with table lock timing in MySQL, you can convert the table to an InnoDB or BDB table to improve performance.

Thank you for your reading, the above is the content of "introduction to the relevant principles of MySQL database locking mechanism". After the study of this article, I believe you have a deeper understanding of the introduction of the relevant principles of MySQL database locking mechanism, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.