MySQL uses essay one

Source: Internet
Author: User
Tags mysql command line

In the practice project, MySQL row-level locks are not used. Instead, the use of inventory increases and decreases through RABBITMQ queues. Two tables update data by displaying the Create transaction implementation. During the testing process, multiple people operate the order, resulting in a transaction lock timeout, which is used on the MySQL command line

Show full processlist;

There are many update processes in sleep that are found in MySQL. It is possible to determine that the MySQL connection handle could not be closed due to a logical interruption in the transaction or a code error, that is, the transaction lock is not closed.

Related information:

 full processlist;

Used before InnoDB plugin, it is difficult to find the locked line to record the problem, Shou engine InnoDB status can only view the current database requests, as well as the current transaction in the case of locks.

There are currently three tables in the INFORMATION_SCHEMA library available from the MySQL database to solve the problem: Innodb_trx, Innodb_locks, Innodb_lock_waits.

Comparison of commonly used columns:

Trx_id:innodb The only thing ID inside the storage engine
Trx_status: Status of the current transaction
Trx_status: Start time of transaction
TRX_REQUESTED_LOCK_ID: The lock ID of the waiting transaction
Trx_wait_started: The start time of the transaction wait
Trx_weight: The weight of the transaction, which reflects the number of rows modified and locked by a transaction, the smaller the weight value is rolled back when a deadlock is found to be rolled back
The process ID in Trx_mysql_thread_id:mysql, corresponding to the ID value in show processlist
Trx_query: SQL statement run by transaction

Kill process ID; The job encountered a query record, select * from the car for update or to modify a field value, Error: Lock wait timeout exceeded; Try restarting transaction solution, in addition to fundamentally from the Business logic code optimization of the operation of the database, have encountered such a situation before, such as just modified this record, and then modify again, will report this error, from the code and business level to avoid.

MySQL uses essay one

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.