WL#4689: Deadlock Monitor
Affects: Server-9.x — Status: Un-Assigned
We have no reliable consistent way to monitor deadlocks for all storage engines. Some engines might not even have deadlock graphs (they can rely on a timeout). Some engines will have idiosyncratic ways to declare victims or to recover. Server "lock" monitoring (WL#2333) can only supply background information. We must ask the storage engine. What information ---------------- The end user probably won't care about deadlock details; this is more likely to be of interest to a DBA. Looking at feature requests, and at what other DBMSs supply, we see a big list of desirables: * timestamp when the deadlock was detected * something that identifies the victim (thread, user, host, txn-id) * something that identifies the winner ("", "", "", "") * something that identifies the object (table name, rowid) * SQL statements in play for both victim and winner * what else was locked at the same time * why the victim was picked, e.g. "it was started later" * settings of @@tx_isolation_level and @@autocommit at time of error * column or index-key values Dumping the information might take time, or might be sensitive. So perhaps it should be dependent on a global/session variable Where information should go --------------------------- These suggestions are not necessarily mutually exclusive. It could go to a trace file. This is the usual request. It's what some other DBMSs do. A variation of this request is that the trace could be readable as an SQL table. It could go to mysqld error. It could go to the end of an error message for the client. This allows the receiving application to decide whether or not to log, and of course MySQL utilities can decide whether to log any errors (see also WL#2878 "Simple data auditing"). It could go to diagnostics area fields outside the error message. This would mean we don't have to parse the error text. See WL#2111 "Stored Procedures: Implement GET DIAGNOSTICS". It could go to storage-engine-specific internal areas. This would mean we have to request and parse whatever the storage engine dumps, as with SHOW ENGINE INNODB STATUS. It could go to PERFORMANCE_SCHEMA.STATEMENTS. This would mean the information is integrated with performance diagnostics. See WL#2515 "Performance statements". Other DBMSs ----------- Description of other products can be seen by clicking 'Progress' and looking at what was deleted from this section on 2009-04-13. References ---------- BUG#1784 ability to log details on all deadlocks BUG#17572 Deadlock log BUG#31071 Counter of deadlocks BUG#32422 deadlocks with 'closing tables' and 'Opening tables' impossible to debug email thread "Deadlock information from InnoDB for [name suppressed]" [mysql internal address]/secure/mailarchive/mail.php?folder=100&mail=31 dev-public email thread "Re: Deadlock reporting in Maria and Falcon" [mysql internal address]/secure/mailarchive/mail.php?folder=5&mail=69829 WL#3064 Deadlock Handler for Maria
Copyright (c) 2000, 2022, Oracle Corporation and/or its affiliates. All rights reserved.