Show information about InnoDB row locks in slow.log

Registered by Vadim Tkachenko

I'd like to see more information from InnoDB in slow.log
1. InnoDB transaction ID
2. How many rows query locked during execution ( shared and exclusive separately)
3. How long it hold locks ( timing can be turn on/ off )
4. How many waits on lock query had ( and what transaction ID hold locks)
5. How long it waited on locks ( timing can be turn on/ off )

All this information (2-4) should be shown in slow.log if verbosity="innodb"
InnoDB transaction ID should be shown always

variable for timing locks is
innodb_lock_timing=0|1 (0 default, global dynamic variable, changing from 0 to 1 affects all connections immediately, no
re-connection needed)

(wrote by Yasufumi)
most of the specification seems ambiguous for me.
1. I understand and may be possible

2. I understand and will investigate possibility. But what you need is "per query" or "per transaction" ?
And ... I think it is same value to rows read...

How about to implement ha_xxxx counts of handler method? It may be easy and universal to all engine.

3. I don't understand at all... if 100 locks obtained. all of the start time should be record? most of end time will be commit of the transaction. The slowlog is for each query, I think. All locks are not released at the end of query. What do you want to know?

4. Scan lock array and pick up all the locks valid?
All lock the query get and scan all waiter of the other threads?
It sounds indeed crazy for me. Some sql will became much slower by the function.
Or do you mean print thread ID of the lock holder which the query wait for?
But it also crazy. log all lock waits and print all log of the query?
Where should we stock the huge-able log?

5. Again.... "Total lock waits" may be possible (but sum should be not exact). Do you mean... if obtain lock_1 ~ lock_100
lock_1: 1ms
lock_2: 3ms
lock_99: 1ms
lock_100: 0ms

Do you understand what is the information which fixed at the end of query?
(And the time is during transaction and not committed yet.)

Blueprint information

Not started
Vadim Tkachenko
Needs approval
Series goal:
Milestone target:

Related branches




Work Items

This blueprint contains Public information 
Everyone can see this information.