*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 4951009 page no 4 n bits 384 index `option_name` of table `wordpress_work`.`wp_options` trx id 9FBA0995 lock_mode X locks rec but not gap TRANSACTION 9FBA0995, ACTIVE 0 sec, process no 14207, OS thread id 1230031168 starting index readģ lock struct(s), heap size 1248, 2 row lock(s) RECORD LOCKS space id 4951009 page no 4 n bits 384 index `option_name` of table `wordpress_work`.`wp_options` trx id 9FBA099E lock_mode X waiting *** (1) WAITING FOR THIS LOCK TO BE GRANTED: MySQL thread id 12505112, query id 909492800 129.54 wordpress_user updatingĭELETE FROM wp_options WHERE option_name = ''_site_transient_timeout_theme_roots'' LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) TRANSACTION 9FBA099E, ACTIVE 0 sec, process no 14207, OS thread id 1228433728 starting index read I can see thread ids, query ids, etc but nothing that I can use to stop either job.ĮDIT: Here's the (relevant?) portion of the status:. I'd like to clear these up but I don't see an active process for either of these cmds (IE something to 'kill' and hopefully force a rollback). Note: Stopping or rolling back a long-running operation can be time-consuming and I/O intensive.Using 'show engine innodb status' I see that wordpress has two deadlocks.
Note: Before you run this procedure, evaluate whether you need the transaction or if it is safe to stop. After you identify the blocking session or thread-ID, stop the transaction by running this procedure: SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = THREAD_ID ĥ. Note: Make sure to substitute THREAD_ID with value returned in Step 3. This determines the last query run by the thread. Use the THREAD_ID to query the performance schema events_statements_current table. For MySQL 5.7 or later, run this query to determine the THREAD_ID of the blocking transaction by the substituting blocking_thread processlist ID: SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = blocking_thread Ĥ. In this case, use the query in Step 2 to find the blocking_thread processlist ID.ģ. When identifying blocking transactions, a NULL value is reported for the blocking query if the session that issued the query has become idle.
Note: The blocked transaction can't proceed until the other transaction commits or rolls back. ON r.trx_id = w.requesting_engine_transaction_id ON b.trx_id = w.blocking_engine_transaction_id INNER JOIN information_schema.innodb_trx rįROM performance_schema.data_lock_waits w INNER JOIN information_schema.innodb_trx b Run this query to see which transactions are waiting and which transactions are blocking them.įROM information_schema.innodb_lock_waits w
View currently running transactions by running this query against the INNODB_TRX table: select * from information_schema.innodb_trx\GĢ. Resolution Identify uncommitted transactionsġ. Follow these steps to identify the query or session that might be blocking your query. Queries can also be blocked because of uncommitted transactions. Blocked queries can occur because a transaction in InnoDB is waiting for another transaction to release a lock.
0 kommentar(er)
