The related principle of MySQL database lock mechanism easy to master

In MySQL, table locks are not deadlocked for storage engines that use table-level locking. This is managed by always requesting all necessary locks at the start of a query and always locking the table in the same order.

In a table with frequent update and insert operations, a small number of data tests run well, in actual operation, due to large amount of data (210,000 records), there will be deadlock phenomenon, with show processlist view, You can see that an UPDATE statement state is locked, and a DELETE statement state is sending data. Take a look at the reference manual and sort out the data to keep track of the problem:

MySQL 5.1 supports table-level locking of MyISAM and Memory tables, page-level locking of BDB tables, row-level locking of InnoDB tables. In many cases, you can guess what type of lock the application uses best, based on training, but it is generally difficult to say that a given lock type is better than the other. Depending on the application, different parts of the application may require different lock types. To determine if you want to use a row-level locked storage engine, you should look at what the application does and what selection and UPDATE statements are mixed with. For example, most Web applications perform a number of choices, with few deletions, only updates to the value of the keyword, and only a small number of specific tables are inserted. Basic MySQL MyISAM settings have been adjusted very well.

In MySQL, table locks are not deadlocked for storage engines that use table-level locking. This is managed by always requesting all necessary locks at the start of a query and always locking the table in the same order.

The principle of the table locking method used for Write,mysql is as follows:

If there is no lock on the table, put a write lock on it.

Otherwise, the lock request is placed in the write lock queue.

The locking method used for Read,mysql is as follows:

If there is no write lock on the table, put a read lock on it.

Otherwise, the lock request is placed in the read lock queue.

When a lock is freed, the lock can be obtained by the thread in the write-locked queue, followed by the thread in the read-locked queue.

This means that if you have a lot of updates on a table, the SELECT statement waits until there are no more updates.

If the INSERT statement does not conflict, you can freely mix parallel inserts and SELECT statements for the MyISAM table without locking.

InnoDB use row locking, BDB use page locking. For both types of storage engines, deadlocks may exist. This is because, during the processing of the SQL statement, InnoDB automatically obtains row locking, and BDB gets the page lock, not when the transaction is started.

The advantages of row-level locking:

· Only a small number of lock conflicts exist when different rows are accessed in many threads.

· There are only a few changes when rolling back.

· You can lock a single row for a long time.

The disadvantage of row-level locking:

· Consumes more memory than page-level or table-level locks.

· When used in most of the table, it is slower than page-level or table-level locking because you have to get more locks.

· If you frequently perform GROUP by operations on most data or have to scan the entire table frequently, it is significantly slower than other locks.

· With high-level locking, you can easily adjust your application by supporting different types of locks, because the lock cost is less than row-level locking.

Table locking takes precedence over page-level or row-level locking in the following situations:

· Most statements for a table are used for reading.

· To read and update strict keywords, you can update or delete a line that can be extracted with a single read keyword:

? UPDATE tbl_name SET column = value WHERE unique_key_col = Key_value;

? DELETE from tbl_name WHERE unique_key_col = key_value;

· SELECT combines parallel INSERT statements and has few update or DELETE statements.

· There are many scans or GROUP by operations on the entire table, and there are no writes.

Different from row-level or page-level locking options:

· Version (for example, the technique used in MySQL for parallel inserts), which can be a write operation with many read operations. This specifies that the database or table supports different views of data dependency, depending on when the access starts. Other common terms are "time tracking," "Write Replication," or "copy on Demand."

· On-demand replication takes precedence over page-level or row-level locking in many cases. However, in the worst case scenario, it might be more memory than using a regular lock.

· In addition to row-level locking, you can use application-level locking, such as the use of Get_lock () and Release_lock () in MySQL. These are the recommended locks that can only work in a well-run application.

To achieve the highest lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB and BDB. For InnoDB and BDB tables, if you explicitly lock the table with lock tables, MySQL uses only table locks, and if you do not use lock tables, because InnoDB uses automatic row-level locking, BDB uses page-level locking to guarantee transaction isolation.

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

Table updates are generally considered more important than table retrievals and therefore give them a higher priority. This should ensure that activities that update a table cannot be "starved to death", even if there is a heavy select activity on the table.

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

There are also problems with table locking in the following situations:

· A client sends a long-running query.

· Then another customer updates the same table. The customer must wait until the select completes.

· Another customer issued another SELECT statement on the same table. Because the update is higher than the select priority, the SELECT statement waits for the update to complete and waits for the 1th SELECT to complete.

Some methods are described below to avoid or reduce the competition caused by table locking:

· An attempt was made to run the SELECT statement faster. You may have to create some summary (summary) tables to do this.

· Start mysqld with--low-priority-updates. This will give all updated (modified) Statements of one table a lower priority than the SELECT statement. In this case, the 2nd SELECT statement in the previous case will be executed before the UPDATE statement, rather than waiting for the 1th select to complete.

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

· You can give a specific insert, UPDATE, or DELETE statement a lower priority with the Low_priority property.

· You can use the High_priority property to give a specific SELECT statement a higher priority.

· Specify a low value for the MAX_WRITE_LOCK_COUNT system variable to start the mysqld to force MySQL to temporarily increase the priority of all SELECT statements waiting for a table after a specific number of inserts have been completed. This allows a read lock to be given after a certain number of write locks.

· If you have questions about insert combination Select, switch to use the new MyISAM table because they support concurrent select and insert.

· If you mix inserts and deletes on the same table, insert delayed will help a lot.

· The limit option for delete can be helpful if you are having problems mixing the SELECT and DELETE statements with the same table.

· Using Sql_buffer_result with the SELECT statement can help to shorten the table lock time.

· You can change the lock code in MYSYS/THR_LOCK.C to use a single queue. In this case, write locks and read locks will have the same priority and will be helpful for some applications.

Here are some techniques for table locking in MySQL:

· If you do not mix updates with a selection that requires many rows to be checked in the same table, you can do so in parallel.

· You can use lock TABLES to improve speed, because many updates in one lock are much faster than updates that are not locked. It is also helpful to cut the contents of a table into several tables.

· If you experience a speed problem with table locking in MySQL, you can convert the table to a InnoDB or BDB table to improve performance