Introduction to MySQL database lock mechanism principles

Different from row-level or page-level locking options:
· Version (for example, the technology used to insert data in MySQL in parallel), which can be a write operation with many read operations at the same time. This indicates that the database or table supports different views of Data Dependencies, depending on the Access time. Other common terms are "time tracking", "Write replication", or "On-Demand replication ".
· On-Demand replication takes precedence over page-level or row-level locking in many cases. However, in the worst case, it may use more memory than using regular locking.
· In addition to row-level locking, you can use application-level locking, for example, using GET_LOCK () and RELEASE_LOCK () in MySQL (). These are creation locks, and they can only work in well-running applications.
To achieve the maximum locking 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 use lock tables to explicitly lock tables, 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, table locking is better for most applications than row locking, but there are some defects. Table locking allows many threads to read data from a table at the same time. However, if a thread wants to write data to a table, it must first obtain 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 to be more important than table searches, so they are given a higher priority. This should ensure that the activity of updating a table cannot be "starved to death", even if the table has a heavy SELECT activity.
Table locking may cause problems in this case. For example, 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 problematic table are also set to wait until more hard disk space is available.
The table is locked in the following situations:
· A customer sends a long-running query.
· Then, another customer updates the same table. The customer must wait until the SELECT operation is complete.
· Another customer sends another SELECT statement to the same table. Because UPDATE has a higher priority than SELECT, the SELECT statement waits for UPDATE to complete and waits for 1st SELECT statements to complete.
The following describes some methods to avoid or reduce the competition caused by table locking:
· Try to make the SELECT statement run faster. You may have to create some summary tables to do this.
· Start mysqld with-low-priority-updates. This gives all statements that update (modify) a table with a lower priority than the SELECT statement. In this case, the 2nd SELECT statements in the previous case will be executed before the UPDATE statement, without waiting for 1st SELECT statements to complete.
· You can use the SET LOW_PRIORITY_UPDATES = 1 Statement to specify that all updates in a specific connection should have a low priority.
· The LOW_PRIORITY attribute can be used to give a specific INSERT, UPDATE, or DELETE statement with a lower priority.
· The HIGH_PRIORITY attribute can be used to give a specific SELECT statement with a higher priority.
· Specify a low value for the max_write_lock_count system variable to start mysqld to force MySQL to temporarily increase the priority of all SELECT statements waiting for a table after a specific number of inserts are completed. In this way, the READ lock is allowed after a certain number of WRITE locks.
· If you have questions about INSERT and SELECT, switch to the new MyISAM table because they support concurrent SELECT and INSERT.
· If you INSERT or delete a table in a hybrid manner, insert delayed will be of great help.
· If you have a problem using the SELECT and DELETE statements in combination with the same table, the delete limit option can be helpful.
· SQL _BUFFER_RESULT can be used for SELECT statements 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 have the same priority and will be helpful to some applications.
Here are some techniques related to table locking in MySQL:
· You can perform parallel operations if you do not need to check the selection of many rows in the same table and do not need to perform hybrid updates.
· You can use lock tables to increase the speed, because many updates in a LOCK are much faster than those that are not locked. It is also helpful to split the table content into several tables.
· If you encounter speed problems during table lock in MySQL, you can convert the table to InnoDB or BDB tables to improve performance.