Recently found that the running speed was slow, and often more than 90% CPU!
Actually detected by the bottleneck in mysql, you can see by running the SHOW PROCESSLIST query many locked, then after an hour or so the locked situation gone. Thus locked is not a deadlock.
Why will appear locked it?
In MySQL, the table-level lock for the storage engine, lock the table will not deadlock. This is always a query by the beginning of all the necessary lock request immediately and always in the same order to manage the lock table.
I MyISAM table format
For WRITE, MySQL uses table locking method works as follows:
◆ If there are no locks on the table, put it above a write lock.
◆ Otherwise, the lock request in the write lock queue.
For READ, MySQL uses locking method works as follows:
◆ If there is no write lock on the table, put a read lock on the top of it.
◆ Otherwise, the lock request in the read lock queue.
When a lock is released, the lock can be written in the queue thread gets locked, then the read lock queue thread.
This means that if you have many updates on a table, SELECT statements will wait until no more updates.
If the INSERT statement does not conflict, for the MyISAM tables can be freely mixed parallel INSERT and SELECT statements without the need for locking.
So MyISAM is the table lock, InnoDB row lock is
Row-level locking advantages:
• When the number of threads access different rows there is only a small amount of lock conflict.
* Rollback only a few changes.
• You can lock a single long line.
Row-level locking shortcomings:
* Than the page level or table-level locking uses more memory.
• When used in the table most of the time, than the page level or table level locking is slow, because you have to get more locks.
If you are in the majority of data on the regular GROUP BY operation or must always scan the entire table, significantly slower than other locks.
* High-level locking, by supporting different types of locks, you can easily adjust the application, because it locks cost less than the row-level locking.
In these cases, the table takes precedence over page-level locking or row-level locking:
* Most of the table statement to read.
* The strict keyword to read and update, you can update or delete can be read with a single keyword to extract the line:
• UPDATE tbl_name SET column = value WHERE unique_key_col = key_value;
• DELETE FROM tbl_name WHERE unique_key_col = key_value;
· SELECT combined with concurrent INSERT statements, and only a few UPDATE or DELETE statement.
* There are many in the whole table scans or GROUP BY operations, no write.
Unlike the row level or page level locking options:
* Version (for example, for the parallel insert in MySQL using the technology), which can be a write operation, while many read operations. This is that database or table supports different views of data dependent, depending on when the visit began. Other common term is "time tracking", "copy on write" or "copy on demand."
* In many cases, on-demand replication priority page level or row-level locking. However, in the worst case, it may use more than the use of conventional locking memory.
* In addition to row-level locking, but you can use application-level locking, for example, in MySQL GET_LOCK () and RELEASE_LOCK (). These are advisory locks, they can only run applications that work well.
And I use the solution is to use single-threaded, with a query service for all database operations are performed with single-threaded