How can I show mysql locks?

See Marko’s link for InnoDB tables and the caveats.

For MyISAM, there isn’t a dead easy “this is the offending query” solution. You should always start with a processlist. But be sure to include the full keyword so that the printed queries aren’t truncated:

SHOW FULL PROCESSLIST;

This will show you a list of all current processes, their SQL query and state. Now usually if a single query is causing many others to lock then it should be easy to identify. The affected queries will have a status of Locked and the offending query will be sitting out by itself, possibly waiting for something intensive, like a temporary table.

If it’s not obvious then you will have to use your powers of SQL deduction to determine which piece of offending SQL may be the cause of your woes.

Related Posts:

  1. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  2. How to use mysqli_query() in PHP?
  3. Access Denied for User ‘root’@’localhost’ (using password: YES) – No Privileges?
  4. MySQL Error “Operand should contain 1 column”
  5. How to use MySQL DECIMAL?
  6. MySQL ERROR 1290 (HY000) –secure-file-priv option
  7. MySQL server startup error ‘The server quit without updating PID file’
  8. What does SQL Select symbol || mean?
  9. PHP Connection failed: SQLSTATE[HY000] [2002] Connection refused
  10. Mysql: Setup the format of DATETIME to ‘DD-MM-YYYY HH:MM:SS’ when creating a table
  11. MySQL DROP all tables, ignoring foreign keys
  12. Duplicating a MySQL table, indices, and data
  13. mysql Foreign key constraint is incorrectly formed error
  14. Access Denied for User ‘root’@’localhost’ (using password: YES) – No Privileges?
  15. ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
  16. mysql is not recognised as an internal or external command,operable program or batch
  17. SELECT * FROM multiple tables. MySQL
  18. mysql: SOURCE error 2?
  19. java.sql.SQLException: Access denied for user ‘root’@’localhost’ (using password: YES)
  20. How to avoid MySQL ‘Deadlock found when trying to get lock; try restarting transaction’
  21. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  22. Data truncated for column?
  23. MySQL foreach loop
  24. MySQL IF NOT NULL, then display 1, else display 0
  25. MySQL date format DD/MM/YYYY select query?
  26. MySQL Calculate Percentage
  27. Repair all tables in one go
  28. How can I output MySQL query results in CSV format?
  29. Error: select command denied to user ‘‘@’‘ for table ‘‘
  30. Get the new record primary key ID from MySQL insert query?
  31. How to change collation of database, table, column?
  32. Failed building wheel for mysql-python
  33. MySQL Error 1215: Cannot add foreign key constraint
  34. ERROR 1115 (42000): Unknown character set: ‘utf8mb4’
  35. Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement
  36. Remove Primary Key in MySQL
  37. How to get the max of two values in MySQL?
  38. mysql: connection refused when trying to connect to localhost using remote IP
  39. Error: Duplicate entry ‘0’ for key ‘PRIMARY’
  40. Why are simple updates to wp_postmeta’s “_edit_lock” so slow?
  41. Complex meta query with 3 keys
  42. How can I find the source of slow queries in WordPress?
  43. How to log mysql errors from wordpress core?
  44. $wpdb and MySQL Create Trigger
  45. wpdb Custom Meta Data with 2 conditions
  46. query to create woocommerce products from images
  47. Error: MySQL shutdown unexpectedly. XAMPP local conf running WordPress
  48. Configure WordPress to connect to Mysql DB using SSH tunneling
  49. How to Create a Admin User for A WordPress Site via MySQL (PHPMyAdmin)?
  50. Rewrite database urls
  51. SQL error #1062 – Duplicate entry ‘1’ for key ‘PRIMARY’ [closed]
  52. Error while importing database
  53. Migrating from Existing MySQL Setup to WordPress
  54. How to split the a table in database?
  55. Why my wordpress loopback traffic while socket is setup?
  56. How do I join a $wpdb query with user meta using default value if not exist?
  57. mySQL statment count of post in each category and sub category
  58. Paypal Post IPN handeling nightmare
  59. Defining the same price to all WooCommerce Products
  60. Database Structure on Different Servers
  61. Is there a page length limit?
  62. WordPress tables and aliases?
  63. What’s the sql for this condition?
  64. Create a form with custom autocomplete address field and CSV import values
  65. WordPress insert query is not working : Showing no Error
  66. Copy usermeta value where ID matches in the same table
  67. Export Yoast Keywords from MySQL/phpMyAdmin
  68. Same database; different WordPress
  69. Some Post Content Does Not Show Up In DB Search
  70. How can I write a formal sql query to get data from a custom table
  71. How to fetch all meta_key and meta_value using post_id wihout duplication of post meta using sql query
  72. Upgrade DB Loop – WordPress DB Version Conflict
  73. How can I view my site directly from WordPress dashboard?
  74. (WP List Table) How to sort by SKU and Custom Fields?
  75. Word Press and database from another server sync
  76. User set date and time from frontend doesn’t get stored into database
  77. get posts whether meta_key exists or not
  78. How to run SQL query from WordPress ‘WP Crontrol’ plugin
  79. How to bulk delete a certain part of all wordpress posts
  80. When I add metadata, metavalue length is added in to the value in some cases
  81. Changes not being reflected on new server
  82. How to show result of sql query in a page?
  83. update_option returns false in AJAX, how to debug?
  84. Why can’t i come back to older working version of php after ugrade with error log on php 7.2 and 7.3
  85. WordPress database error Duplicate entry for key ‘slug’
  86. Query does not filter duplicate _sku numbers
  87. MySQL – How to combine data from two tables into one?
  88. MySQL Select within WP Page template
  89. Stop my loop to register data twice
  90. `#1215 – Cannot add foreign key constraint
  91. How to restore: Database intact but folders deleted
  92. wp_postmeta – lot of meta fields
  93. XML RPC post produces immediate revision with odd post_date_gmt
  94. Woocommerce: Grab total revenue of a product over all orders
  95. Delete posts with word count less than x number of words
  96. MySQL: get post_tag items that are tags (not SEO keywords)
  97. $wpdb->get_var returns 0
  98. SQL Database Lost
  99. Need SQL query to find&replace image URL
  100. Postgres equivalent to MySQL’s \G?

Leave a Comment

error code: 523