mysql locked solution

2010-10-14  来源:本站原创  分类:Database  人气:105 

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

相关文章
  • mysql locked solution 2010-10-14

    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

  • gem install mysql error solution 2010-06-02

    gem install mysql error solution gem install mysql error when: checking for mysql_query () in-lmysqlclient ... no checking for main () in-lm ... yes checking for mysql_query () in-lmysqlclient ... no checking for main () in-lz ... yes checking for my

  • Transfer: remote connection mysql slow solution 2010-08-14

    http://blog.163.com/wxfsnow @ 126/blog/static/1049536620081151026192 / Mysql remote connection slow solution PHP MYSQL slow remote connection, sometimes used when a remote connection to MYSQL ranging from 4-20 seconds, the local connection MYSQL norm

  • NO.1130 ERROR MYSQL error solution 2010-10-20

    If you want to connect to your mysql when this error occurs: ERROR 1130: Host '10 .10.27.44 'is not allowed to connect to this MySQL server Solution: 1. Change the table method. Your account may be allowed from a remote landing, only localhost. This

  • MySQL database solution method does not allow remote access from 2010-03-03

    Solution: 1, changing table method. Your account may be allowed from a remote landing, only localhost. This time as long as the localhost of that computer, log in MySQL , change " MySQL " database in the "user" table of "host"

  • the account is locked solution 2010-03-16

    In accordance with oracle 10g, the log appears the account is locked This error almost all beginners encounter. In fact, the solution was simple. Please enter a user name: SYSTEM Enter password: admin (Note that this password when you first install t

  • MySQL slow solution (change) 2010-04-09

    MySQL slow connection solution. PHP MYSQL slow remote connection, sometimes used when a remote connection to MYSQL ranging from 4-20 seconds, the local connection MYSQL normal, the problem occurs mainly because the default installation of MYSQL opene

  • Insert data to MySQL garbled solution 2010-08-02

    Phenomenon: the use of JDBC to MySQL database into the Chinese field, and then open the table found that those fields are "??", or garbled. Reason: 1. Create a database default encoding is not specified, leading to a MySql database using the def

  • oracle user locked solution 2010-08-17

    Inform the user when the login is locked. The solution: the role of the user with dba landing, unlock, unlock sql statement is: alter user username (locked user) account unlock;

  • mysql simple solution for the Chinese garbled 2010-09-07

    Use mysql the people know that the Chinese garbled Yi Zhi is not a developer will always avoid the Wen Ti Kai, no way, who told people that things Bushiwomen development of it, Qi Shi later in the 5.1 release, we can at install time Select the databa

  • Mysql limit solution _ paging slow turn around 2010-12-19

    MySQL slow limit paging solution (Mysql limit optimization, one million to ten million record for fast page) MySql performance in the end to how high? Php spent more than six months, the real depth to think about this issue or from the day before yes

  • MYSQL installation solution to enter the current root password method 2011-03-02

    MYSQL, it was found in the installation to enter the current root password Do not remember previously had installed in the computer (your system has been installed MYSQL in the heavy equipment will be asked to enter the original password set, if it i

  • the account is locked solution _oracle10g_ own summary 2011-03-17

    Install oracle 10g, log isqlplus - " http://localhost:5560/isqlplus , log in using the scott. Appear: the account is locked This error is almost all beginners encounter. Oracle default user scott closed Solution is as follows: Run - cmd -> cd \ -&

  • mysql master-slave solution to data inconsistencies 2011-05-12

    Reprinted from http://hi.baidu.com/thinkinginlamp/blog/item/84f30cf4470979e77609d79a.html When using the MySQL master server time. One must consider is the need to detect whether there have been inconsistencies in the data, but fortunately mk-table-c

  • linux tar package install mysql error solution 2011-09-06

    tar package to install mysql, execution of. / configure run make after the error is as follows: make: *** No targets specified and no makefile found. Stop. Cause: The system is not installed ncurse Solution: You can find ncurse installed after the

  • Linux, Mysql garbled solution 2011-08-19

    Recent changes in the source code to do ProjectForge, of course, use the MySQL database, but to turn over after a very nasty problem, of course, is garbled. PF side using UTF-8 encoding, UTF-8 should be envisaged is all normal ah, the original still

  • MySQL errno solution encountered 2011-09-05

    MySQL ERROR: Can't create table (errno: 150) solution: Create a referential constraint in mysql when there will be MySQL ERROR 1005: Can't create table (errno: 150) error message results referential constraint can not be established. General situatio

  • windows to forget root password mysql super-administrator solution 2009-12-25

    Reprinted from: http://www.kuqin.com/database/20080306/4249.html Today, to help a friend retrieve a MYSQL ROOT super administrator password, and start typing the command when the little punched a "point" of harm to toss a few hours, and finally

  • Install mysql failure solution 2010-03-03

    Have installed prior to mysql, for some reason could not complete unloading, leading to re-install mysql failed solutions: Install mysql service failed: First uninstall the windows service: Uninstall MYSQL is normally cmd window, enter X: \ mysql ins

  • MySQL slow solution into 2010-03-04

    MySQL export import SQL statement may be very, very slowly, going through to import only 450,000 records, even took nearly 3 hours. When you export the rational use of several parameters, we can greatly accelerate the rate of import. -E use the VALUE