The correlation principle of MySQL database lock mechanism

In a table with frequent update and insert operations, a small number of data tests run well, in the actual operation, due to the large amount of data (210,000 records), there will be deadlock phenomenon, with show processlist view, you can see an UPDATE statement state is Locked, 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.