innodb_deadlock_count.patch

   1 # name       : innodb_deadlock_count.patch
   2 # introduced : 11 or before
   3 # maintainer : Yasufumi
   4 #
   5 #!!! notice !!!
   6 # Any small change to this file in the main branch
   7 # should be done or reviewed by the maintainer!
   8 --- a/storage/innobase/handler/ha_innodb.cc
   9 +++ b/storage/innobase/handler/ha_innodb.cc
  10 @@ -691,6 +691,8 @@
  11    (char*) &export_vars.innodb_dblwr_pages_written,       SHOW_LONG},
  12    {"dblwr_writes",
  13    (char*) &export_vars.innodb_dblwr_writes,              SHOW_LONG},
  14 +  {"deadlocks",
  15 +  (char*) &export_vars.innodb_deadlocks,                 SHOW_LONG},
  16    {"dict_tables",
  17    (char*) &export_vars.innodb_dict_tables,               SHOW_LONG},
  18    {"have_atomic_builtins",
  19 --- a/storage/innobase/include/lock0lock.h
  20 +++ b/storage/innobase/include/lock0lock.h
  21 @@ -43,6 +43,7 @@
  22  #endif /* UNIV_DEBUG */
  23  /* Buffer for storing information about the most recent deadlock error */
  24  extern FILE*   lock_latest_err_file;
  25 +extern ulint   srv_n_lock_deadlock_count;
  26
  27  /*********************************************************************//**
  28  Gets the size of a lock struct.
  29 --- a/storage/innobase/include/srv0srv.h
  30 +++ b/storage/innobase/include/srv0srv.h
  31 @@ -770,6 +770,7 @@
  32         ulint innodb_buffer_pool_read_ahead_evicted;/*!< srv_read_ahead evicted*/
  33         ulint innodb_dblwr_pages_written;       /*!< srv_dblwr_pages_written */
  34         ulint innodb_dblwr_writes;              /*!< srv_dblwr_writes */
  35 +       ulint innodb_deadlocks;
  36         ibool innodb_have_atomic_builtins;      /*!< HAVE_ATOMIC_BUILTINS */
  37         ulint innodb_log_waits;                 /*!< srv_log_waits */
  38         ulint innodb_log_write_requests;        /*!< srv_log_write_requests */
  39 --- a/storage/innobase/lock/lock0lock.c
  40 +++ b/storage/innobase/lock/lock0lock.c
  41 @@ -3330,6 +3330,7 @@
  42                 break;
  43
  44         case LOCK_VICTIM_IS_START:
  45 +               srv_n_lock_deadlock_count++;
  46                 fputs("*** WE ROLL BACK TRANSACTION (2)\n",
  47                       lock_latest_err_file);
  48                 break;
  49 --- a/storage/innobase/srv/srv0srv.c
  50 +++ b/storage/innobase/srv/srv0srv.c
  51 @@ -477,6 +477,7 @@
  52  static ulint   srv_n_rows_deleted_old          = 0;
  53  static ulint   srv_n_rows_read_old             = 0;
  54
  55 +UNIV_INTERN ulint              srv_n_lock_deadlock_count       = 0;
  56  UNIV_INTERN ulint              srv_n_lock_wait_count           = 0;
  57  UNIV_INTERN ulint              srv_n_lock_wait_current_count   = 0;
  58  UNIV_INTERN ib_int64_t srv_n_lock_wait_time            = 0;
  59 @@ -2293,6 +2294,7 @@
  60         export_vars.innodb_buffer_pool_pages_data = LRU_len;
  61         export_vars.innodb_buffer_pool_pages_dirty = flush_list_len;
  62         export_vars.innodb_buffer_pool_pages_free = free_len;
  63 +       export_vars.innodb_deadlocks = srv_n_lock_deadlock_count;
  64  #ifdef UNIV_DEBUG
  65         export_vars.innodb_buffer_pool_pages_latched
  66                 = buf_get_latched_pages_number();
  67 --- /dev/null
  68 +++ b/mysql-test/r/percona_innodb_deadlock_count.result
  69 @@ -0,0 +1,28 @@
  70 +# Establish connection con1 (user=root)
  71 +# Establish connection con2 (user=root)
  72 +# Establish connection con3 (user=root)
  73 +# Drop test table
  74 +drop table if exists t;
  75 +# Create test table
  76 +create table t(a INT PRIMARY KEY, b INT) engine=InnoDB;
  77 +# Insert two rows to test table
  78 +insert into t values(2,1);
  79 +insert into t values(1,2);
  80 +# Switch to connection con1
  81 +BEGIN;
  82 +SELECT b FROM t WHERE a=1 FOR UPDATE;
  83 +# Switch to connection con2
  84 +BEGIN;
  85 +SELECT b FROM t WHERE a=2 FOR UPDATE;
  86 +# Switch to connection con1
  87 +SELECT b FROM t WHERE a=2 FOR UPDATE;
  88 +# Switch to connection con2
  89 +SELECT b FROM t WHERE a=1 FOR UPDATE;
  90 +# Switch to connection con1
  91 +ROLLBACK;
  92 +# Switch to connection con2
  93 +ROLLBACK;
  94 +# Switch to connection con3
  95 +Deadlocks: 1
  96 +# Drop test table
  97 +drop table t;
  98 --- /dev/null
  99 +++ b/mysql-test/t/percona_innodb_deadlock_count.test
 100 @@ -0,0 +1,61 @@
 101 +--source include/have_innodb.inc
 102 +--echo # Establish connection con1 (user=root)
 103 +connect (con1,localhost,root,,);
 104 +--echo # Establish connection con2 (user=root)
 105 +connect (con2,localhost,root,,);
 106 +--echo # Establish connection con3 (user=root)
 107 +connect (con3,localhost,root,,);
 108 +--echo # Drop test table
 109 +--disable_warnings
 110 +drop table if exists t;
 111 +--enable_warnings
 112 +
 113 +--echo # Create test table
 114 +create table t(a INT PRIMARY KEY, b INT) engine=InnoDB;
 115 +--echo # Insert two rows to test table
 116 +insert into t values(2,1);
 117 +insert into t values(1,2);
 118 +
 119 +#--echo # Save current deadlock count
 120 +let $current = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_deadlocks'`;
 121 +
 122 +--disable_result_log
 123 +
 124 +--echo # Switch to connection con1
 125 +connection con1;
 126 +BEGIN; SELECT b FROM t WHERE a=1 FOR UPDATE;
 127 +
 128 +--echo # Switch to connection con2
 129 +connection con2;
 130 +BEGIN; SELECT b FROM t WHERE a=2 FOR UPDATE;
 131 +
 132 +--echo # Switch to connection con1
 133 +connection con1;
 134 +SEND SELECT b FROM t WHERE a=2 FOR UPDATE;
 135 +
 136 +--echo # Switch to connection con2
 137 +connection con2;
 138 +SEND SELECT b FROM t WHERE a=1 FOR UPDATE;
 139 +
 140 +--echo # Switch to connection con1
 141 +connection con1;
 142 +--error 0, ER_LOCK_DEADLOCK
 143 +reap;
 144 +ROLLBACK;
 145 +
 146 +--echo # Switch to connection con2
 147 +connection con2;
 148 +--error 0, ER_LOCK_DEADLOCK
 149 +reap;
 150 +ROLLBACK;
 151 +
 152 +--echo # Switch to connection con3
 153 +connection con3;
 154 +let $result = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_deadlocks'`;
 155 +
 156 +--enable_result_log
 157 +
 158 +let $diff = `SELECT $result - $current`;
 159 +echo Deadlocks: $diff;
 160 +--echo # Drop test table
 161 +drop table t;