From: Baron Schwartz
Date: November 6 2007 10:10pm
Subject: Changing lock output in InnoDB status (bug #29126)
List-Archive: http://lists.mysql.com/internals/35159
Message-Id: <4730E652.4050406@xaprb.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

One of my feature requests for InnoDB 
(http://bugs.mysql.com/bug.php?id=29126) is to disable the verbose lock 
output in SHOW INNODB STATUS.  It looks to me like it can be done very 
easily in innobase/lock/lock0lock.c by changing this code:

4172    for (i = 0; i < lock_rec_get_n_bits(lock); i++) {
4173
4174       if (lock_rec_get_nth_bit(lock, i)) {
4175
4176          fprintf(file, "Record lock, heap no %lu ", (ulong) i);
4177
4178          if (page) {
4179             rec_t*   rec
4180                = page_find_rec_with_heap_no(page, i);
4181             offsets = rec_get_offsets(rec, lock->index,
4182                offsets, ULINT_UNDEFINED, &heap);
4183             rec_print_new(file, rec, offsets);
4184          }
4185
4186          putc('\n', file);
4187       }
4188    }

I could easily comment that out, but it'd be nice to make a patch others 
could use.  I think all I need to do is add a #ifdef around that.  But 
what should I #ifdef with?  I see UNIV_SYNC_DEBUG in lots of places in 
the source, but maybe there's something better.

The other part of that feature request is to always print the locks for 
each transaction, as is done when the innodb_lock_monitor is enabled. 
This way I can see locks held, without filling the error log with them. 
  I think the best way to do that is to add a server variable that will 
allow that to be configurable, but I doubt that's as easy as the 
following patch against the 5.0.45 source, which accomplishes both of my 
desired changes, plus changes the number of locks printed from 10 to 50:

baron@tigger mysql-5.0.45 $ diff -ur innobase/lock/lock0lock.c{.orig,}
--- innobase/lock/lock0lock.c.orig      2007-11-06 16:50:26.000000000 -0500
+++ innobase/lock/lock0lock.c   2007-11-06 17:07:23.000000000 -0500
@@ -4169,6 +4169,7 @@
  #endif /* UNIV_SYNC_DEBUG */
         }

+#ifdef UNIV_SYNC_DEBUG
         for (i = 0; i < lock_rec_get_n_bits(lock); i++) {

                 if (lock_rec_get_nth_bit(lock, i)) {
@@ -4186,6 +4187,7 @@
                         putc('\n', file);
                 }
         }
+#endif /* UNIV_SYNC_DEBUG */

         mtr_commit(&mtr);
         if (UNIV_LIKELY_NULL(heap)) {
@@ -4357,11 +4359,6 @@
                 }
         }

-       if (!srv_print_innodb_lock_monitor) {
-               nth_trx++;
-               goto loop;
-       }
-
         i = 0;

         /* Look at the note about the trx loop above why we loop here:
@@ -4414,9 +4411,9 @@

         nth_lock++;

-       if (nth_lock >= 10) {
+       if (nth_lock >= 50) {
                 fputs(
-               "10 LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER 
PRINTS\n",
+               "50 LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER 
PRINTS\n",
                         file);

                 nth_trx++;


Advice is appreciated.  I'll do this one way or another, as I really 
need to see the locks and not the locked records for day-to-day 
troubleshooting and admin jobs.  But doing it "the right way" would be 
nice :-)

Thanks
Baron