WL#4689: Deadlock Monitor

Affects: Server-9.x   —   Status: Un-Assigned   —   Priority: Very High

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.


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