title: MYSQL slow performance because of Locking
body: <p>I have Mysql5.5 on windows system. Since past 1 month it is running slow. </p>
  
  <p>Here is the query which are running the most:</p>
  
  <pre><code>   begin
       update  roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1
        where  roomlogs.netId = pnetid
          and  roomlogs.`status` = 0
          and  roomlogs.enterTime&gt;=DATE(ptime)
          and  roomlogs.enterTime&lt;DATE(ptime) + interval 1 day;
       INSERT
         into  roomlogs (roomlogs.roomId, roomlogs.netId, roomlogs.enterTime,
                         roomlogs.exitTime, roomlogs.hidden, roomlogs.checkinId)
              value
                  (pRoomId, pnetid,ptime, pexit, phidden, pcheckid);
       select  *
         from  roomlogs
         where  roomlogs.roomlogId = LAST_INSERT_ID();
     end
  </code></pre>
  
  <p>AND:</p>
  
  <pre><code>begin
      select  count(*) as total
          from  roomlogs
          where  roomId = proomId
            and  roomlogs.`status` = 0
            AND  roomlogs.enterTime &gt;= CURDATE()
            AND  roomlogs.enterTime  &lt; CURDATE() + INTERVAL 1 DAY
            and  roomlogs.hidden!=1;
  end
  </code></pre>
  
  <p>Here index of roomlogs are primary(roomlogId), NETID(netid,status,enterTime), ROOMID(roomID,status,enterTime)</p>
  
  <p>Here is my InnoDB status when MySQL has more number of queries (around 100) running simultaneously in the server.</p>
  
  <pre><code> =====================================
   2018-10-25 17:42:01 1728 INNODB MONITOR OUTPUT
   =====================================
   Per second averages calculated from the last 20 seconds
   -----------------
   BACKGROUND THREAD
   -----------------
   srv_master_thread loops: 148860 srv_active, 0 srv_shutdown, 271514 srv_idle
   srv_master_thread log flush and writes: 420374
   ----------
   SEMAPHORES
   ----------
   OS WAIT ARRAY INFO: reservation count 80056
   OS WAIT ARRAY INFO: signal count 80039
   Mutex spin waits 6920930, rounds 7674964, OS waits 52940
   RW-shared spins 27042, rounds 803211, OS waits 26514
   RW-excl spins 1173, rounds 9109, OS waits 211
   Spin rounds per wait: 1.11 mutex, 29.70 RW-shared, 7.77 RW-excl
   ------------
   TRANSACTIONS
   ------------
   Trx id counter 78706084
   Purge done for trx's n:o &lt; 78706079 undo n:o &lt; 0 state: running but idle
   History list length 2281
   LIST OF TRANSACTIONS FOR EACH SESSION:
   ---TRANSACTION 78705887, not started
   MySQL thread id 3000, OS thread handle 0x8e0, query id 4915274 localhost 127.0.0.1 root cleaning up
  **------------ SKIPPING SOME DATA HERE--------------**
   ---TRANSACTION 78701492, not started
   MySQL thread id 15, OS thread handle 0x12c4, query id 4889345 localhost 127.0.0.1 root cleaning up
   ---TRANSACTION 78697207, not started
   MySQL thread id 16, OS thread handle 0x408, query id 4889123 localhost 127.0.0.1 root cleaning up
   ---TRANSACTION 78701491, not started
   MySQL thread id 14, OS thread handle 0x11c8, query id 4889320 localhost 127.0.0.1 root cleaning up
   ---TRANSACTION 78697202, not started
   MySQL thread id 13, OS thread handle 0x10dc, query id 4889098 localhost 127.0.0.1 root cleaning up
   ---TRANSACTION 78706073, ACTIVE 1 sec starting index read
   mysql tables in use 1, locked 1
   LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
   MySQL thread id 2989, OS thread handle 0x157c, query id 4916082 localhost 127.0.0.1 root updating
   update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
         roomlogs.enterTime&gt;=DATE(ptime) and 
         roomlogs.enterTime&lt;DATE(ptime) + interval 1 day
   ------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
   RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706073 lock_mode X waiting
   Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
    0: len 4; hex 80000011; asc     ;;
    1: len 6; hex 0000028a4fc7; asc     O ;;
    2: len 7; hex c3000001ed0110; asc        ;;
    3: len 7; hex 726f6f6d313038; asc room108;;
    4: len 7; hex 7973756e313032; asc ysun102;;
    5: len 5; hex 999d1f25db; asc    % ;;
    6: len 5; hex 999d1d2a13; asc    * ;;
    7: len 4; hex 80000001; asc     ;;
    8: len 4; hex 80000000; asc     ;;
    9: len 4; hex 7fffffff; asc     ;;
  
   ------------------
   ---TRANSACTION 78706056, ACTIVE 4 sec starting index read
   mysql tables in use 1, locked 1
   LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
   MySQL thread id 2942, OS thread handle 0x15e0, query id 4916017 localhost 127.0.0.1 root updating
   update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
         roomlogs.enterTime&gt;=DATE(ptime) and 
         roomlogs.enterTime&lt;DATE(ptime) + interval 1 day
   ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
   RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706056 lock_mode X waiting
   Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
    0: len 4; hex 80000011; asc     ;;
    1: len 6; hex 0000028a4fc7; asc     O ;;
    2: len 7; hex c3000001ed0110; asc        ;;
    3: len 7; hex 726f6f6d313038; asc room108;;
    4: len 7; hex 7973756e313032; asc ysun102;;
    5: len 5; hex 999d1f25db; asc    % ;;
    6: len 5; hex 999d1d2a13; asc    * ;;
    7: len 4; hex 80000001; asc     ;;
    8: len 4; hex 80000000; asc     ;;
    9: len 4; hex 7fffffff; asc     ;;
  
   ------------------
   ---TRANSACTION 78706045, ACTIVE 5 sec starting index read
   mysql tables in use 1, locked 1
   LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
   MySQL thread id 2976, OS thread handle 0x125c, query id 4915976 localhost 127.0.0.1 root updating
   update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
         roomlogs.enterTime&gt;=DATE(ptime) and 
         roomlogs.enterTime&lt;DATE(ptime) + interval 1 day
   ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
   RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706045 lock_mode X waiting
   Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
    0: len 4; hex 80000011; asc     ;;
    1: len 6; hex 0000028a4fc7; asc     O ;;
    2: len 7; hex c3000001ed0110; asc        ;;
    3: len 7; hex 726f6f6d313038; asc room108;;
    4: len 7; hex 7973756e313032; asc ysun102;;
    5: len 5; hex 999d1f25db; asc    % ;;
    6: len 5; hex 999d1d2a13; asc    * ;;
    7: len 4; hex 80000001; asc     ;;
    8: len 4; hex 80000000; asc     ;;
    9: len 4; hex 7fffffff; asc     ;;
  
   ------------------
   ---TRANSACTION 78706009, ACTIVE 9 sec starting index read
   mysql tables in use 1, locked 1
   LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
   MySQL thread id 2946, OS thread handle 0xb98, query id 4915824 localhost 127.0.0.1 root updating
   update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
         roomlogs.enterTime&gt;=DATE(ptime) and 
         roomlogs.enterTime&lt;DATE(ptime) + interval 1 day
   ------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
   RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706009 lock_mode X waiting
   Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
    0: len 4; hex 80000011; asc     ;;
    1: len 6; hex 0000028a4fc7; asc     O ;;
    2: len 7; hex c3000001ed0110; asc        ;;
    3: len 7; hex 726f6f6d313038; asc room108;;
    4: len 7; hex 7973756e313032; asc ysun102;;
    5: len 5; hex 999d1f25db; asc    % ;;
    6: len 5; hex 999d1d2a13; asc    * ;;
    7: len 4; hex 80000001; asc     ;;
    8: len 4; hex 80000000; asc     ;;
    9: len 4; hex 7fffffff; asc     ;;
  
   ------------------
   ---TRANSACTION 78706004, ACTIVE 10 sec starting index read
   mysql tables in use 1, locked 1
   LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
   MySQL thread id 2995, OS thread handle 0x1234, query id 4915806 localhost 127.0.0.1 root updating
   update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
         roomlogs.enterTime&gt;=DATE(ptime) and 
         roomlogs.enterTime&lt;DATE(ptime) + interval 1 day
   ------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
   RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706004 lock_mode X waiting
   Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
    0: len 4; hex 80000011; asc     ;;
    1: len 6; hex 0000028a4fc7; asc     O ;;
    2: len 7; hex c3000001ed0110; asc        ;;
    3: len 7; hex 726f6f6d313038; asc room108;;
    4: len 7; hex 7973756e313032; asc ysun102;;
    5: len 5; hex 999d1f25db; asc    % ;;
    6: len 5; hex 999d1d2a13; asc    * ;;
    7: len 4; hex 80000001; asc     ;;
    8: len 4; hex 80000000; asc     ;;
    9: len 4; hex 7fffffff; asc     ;;
  
   ------------------
   ---TRANSACTION 78705997, ACTIVE 12 sec starting index read
   mysql tables in use 1, locked 1
   LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
   MySQL thread id 2948, OS thread handle 0x1708, query id 4915783 localhost 127.0.0.1 root updating
   update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
         roomlogs.enterTime&gt;=DATE(ptime) and 
         roomlogs.enterTime&lt;DATE(ptime) + interval 1 day
   ------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
   RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78705997 lock_mode X waiting
   Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
    0: len 4; hex 80000011; asc     ;;
    1: len 6; hex 0000028a4fc7; asc     O ;;
    2: len 7; hex c3000001ed0110; asc        ;;
    3: len 7; hex 726f6f6d313038; asc room108;;
    4: len 7; hex 7973756e313032; asc ysun102;;
    5: len 5; hex 999d1f25db; asc    % ;;
    6: len 5; hex 999d1d2a13; asc    * ;;
    7: len 4; hex 80000001; asc     ;;
    8: len 4; hex 80000000; asc     ;;
    9: len 4; hex 7fffffff; asc     ;;
  
   ------------------
   ---TRANSACTION 78705985, ACTIVE 14 sec starting index read
   mysql tables in use 1, locked 1
   LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
   MySQL thread id 2986, OS thread handle 0x1640, query id 4915747 localhost 127.0.0.1 root updating
   update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
         roomlogs.enterTime&gt;=DATE(ptime) and 
         roomlogs.enterTime&lt;DATE(ptime) + interval 1 day
   ------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:
   RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78705985 lock_mode X waiting
   Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
    0: len 4; hex 80000011; asc     ;;
    1: len 6; hex 0000028a4fc7; asc     O ;;
    2: len 7; hex c3000001ed0110; asc        ;;
    3: len 7; hex 726f6f6d313038; asc room108;;
    4: len 7; hex 7973756e313032; asc ysun102;;
    5: len 5; hex 999d1f25db; asc    % ;;
    6: len 5; hex 999d1d2a13; asc    * ;;
    7: len 4; hex 80000001; asc     ;;
    8: len 4; hex 80000000; asc     ;;
    9: len 4; hex 7fffffff; asc     ;;
  
   ------------------
   ---TRANSACTION 78705977, ACTIVE 16 sec fetching rows
   mysql tables in use 1, locked 1
   4610 lock struct(s), heap size 539064, 1001945 row lock(s)
   MySQL thread id 2938, OS thread handle 0x10bc, query id 4915725 localhost 127.0.0.1 root updating
   update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
         roomlogs.enterTime&gt;=DATE(ptime) and 
         roomlogs.enterTime&lt;DATE(ptime) + interval 1 day
   --------
   FILE I/O
   --------
   I/O thread 0 state: wait Windows aio (insert buffer thread)
   I/O thread 1 state: wait Windows aio (log thread)
   I/O thread 2 state: wait Windows aio (read thread)
   I/O thread 3 state: wait Windows aio (read thread)
   I/O thread 4 state: wait Windows aio (read thread)
   I/O thread 5 state: wait Windows aio (read thread)
   I/O thread 6 state: wait Windows aio (write thread)
   I/O thread 7 state: wait Windows aio (write thread)
   I/O thread 8 state: wait Windows aio (write thread)
   I/O thread 9 state: wait Windows aio (write thread)
   Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
    ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
   Pending flushes (fsync) log: 0; buffer pool: 0
   23467 OS file reads, 931766 OS file writes, 524775 OS fsyncs
   0.00 reads/s, 0 avg bytes/read, 1.00 writes/s, 1.00 fsyncs/s
   -------------------------------------
   INSERT BUFFER AND ADAPTIVE HASH INDEX
   -------------------------------------
   Ibuf: size 1, free list len 39, seg size 41, 61 merges
   merged operations:
    insert 57, delete mark 5, delete 0
   discarded operations:
    insert 0, delete mark 0, delete 0
   Hash table size 4425293, node heap has 2003 buffer(s)
   30.00 hash searches/s, 19.95 non-hash searches/s
   ---
   LOG
   ---
   Log sequence number 7396884835
   Log flushed up to   7396884835
   Pages flushed up to 7396717673
   Last checkpoint at  7396717673
   0 pending log writes, 0 pending chkp writes
   164342 log i/o's done, 1.00 log i/o's/second
   ----------------------
   BUFFER POOL AND MEMORY
   ----------------------
   Total memory allocated 2197815296; in additional pool allocated 0
   Dictionary memory allocated 2793588
   Buffer pool size   131072
   Free buffers       101935
   Database pages     27102
   Old database pages 9967
   Modified db pages  377
   Pending reads 0
   Pending writes: LRU 0, flush list 0, single page 0
   Pages made young 498, not young 0
   0.00 youngs/s, 0.00 non-youngs/s
   Pages read 22500, created 4602, written 613885
   0.00 reads/s, 0.10 creates/s, 0.00 writes/s
   Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
   Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
   LRU len: 27102, unzip_LRU len: 0
   I/O sum[0]:cur[0], unzip sum[0]:cur[0]
   ----------------------
   INDIVIDUAL BUFFER POOL INFO
   ----------------------
   ---BUFFER POOL 0
   Buffer pool size   16384
   Free buffers       12818
   Database pages     3312
   Old database pages 1240
   Modified db pages  63
   Pending reads 0
   Pending writes: LRU 0, flush list 0, single page 0
   Pages made young 72, not young 0
   0.00 youngs/s, 0.00 non-youngs/s
   Pages read 2713, created 599, written 81358
   0.00 reads/s, 0.00 creates/s, 0.00 writes/s
   Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
   Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
   LRU len: 3312, unzip_LRU len: 0
   I/O sum[0]:cur[0], unzip sum[0]:cur[0]
   ---BUFFER POOL 1
   Buffer pool size   16384
   Free buffers       12809
   Database pages     3319
   Old database pages 1215
   Modified db pages  27
   Pending reads 0
   Pending writes: LRU 0, flush list 0, single page 0
   Pages made young 15, not young 0
   0.00 youngs/s, 0.00 non-youngs/s
   Pages read 2770, created 549, written 78718
   0.00 reads/s, 0.00 creates/s, 0.00 writes/s
   Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
   Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
   LRU len: 3319, unzip_LRU len: 0
   I/O sum[0]:cur[0], unzip sum[0]:cur[0]
   ---BUFFER POOL 2
   Buffer pool size   16384
   Free buffers       12717
   Database pages     3411
   Old database pages 1256
   Modified db pages  28
   Pending reads 0
   Pending writes: LRU 0, flush list 0, single page 0
   Pages made young 77, not young 0
   0.00 youngs/s, 0.00 non-youngs/s
   Pages read 2795, created 616, written 49290
   0.00 reads/s, 0.00 creates/s, 0.00 writes/s
   Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
   Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
   LRU len: 3411, unzip_LRU len: 0
   I/O sum[0]:cur[0], unzip sum[0]:cur[0]
   ---BUFFER POOL 3
   Buffer pool size   16384
   Free buffers       12733
   Database pages     3395
   Old database pages 1246
   Modified db pages  51
   Pending reads 0
   Pending writes: LRU 0, flush list 0, single page 0
   Pages made young 45, not young 0
   0.00 youngs/s, 0.00 non-youngs/s
   Pages read 2780, created 615, written 91680
   0.00 reads/s, 0.00 creates/s, 0.00 writes/s
   Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
   Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
   LRU len: 3395, unzip_LRU len: 0
   I/O sum[0]:cur[0], unzip sum[0]:cur[0]
   ---BUFFER POOL 4
   Buffer pool size   16384
   Free buffers       12812
   Database pages     3318
   Old database pages 1217
   Modified db pages  47
   Pending reads 0
   Pending writes: LRU 0, flush list 0, single page 0
   Pages made young 108, not young 0
   0.00 youngs/s, 0.00 non-youngs/s
   Pages read 2645, created 673, written 95064
   0.00 reads/s, 0.00 creates/s, 0.00 writes/s
   Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
   Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
   LRU len: 3318, unzip_LRU len: 0
   I/O sum[0]:cur[0], unzip sum[0]:cur[0]
   ---BUFFER POOL 5
   Buffer pool size   16384
   Free buffers       12590
   Database pages     3538
   Old database pages 1294
   Modified db pages  51
   Pending reads 0
   Pending writes: LRU 0, flush list 0, single page 0
   Pages made young 35, not young 0
   0.00 youngs/s, 0.00 non-youngs/s
   Pages read 2905, created 633, written 89554
   0.00 reads/s, 0.00 creates/s, 0.00 writes/s
   Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
   Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
   LRU len: 3538, unzip_LRU len: 0
   I/O sum[0]:cur[0], unzip sum[0]:cur[0]
   ---BUFFER POOL 6
   Buffer pool size   16384
   Free buffers       12563
   Database pages     3567
   Old database pages 1307
   Modified db pages  52
   Pending reads 0
   Pending writes: LRU 0, flush list 0, single page 0
   Pages made young 32, not young 0
   0.00 youngs/s, 0.00 non-youngs/s
   Pages read 3127, created 440, written 56311
   0.00 reads/s, 0.00 creates/s, 0.00 writes/s
   Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
   Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
   LRU len: 3567, unzip_LRU len: 0
   I/O sum[0]:cur[0], unzip sum[0]:cur[0]
   ---BUFFER POOL 7
   Buffer pool size   16384
   Free buffers       12893
   Database pages     3242
   Old database pages 1192
   Modified db pages  58
   Pending reads 0
   Pending writes: LRU 0, flush list 0, single page 0
   Pages made young 114, not young 0
   0.00 youngs/s, 0.00 non-youngs/s
   Pages read 2765, created 477, written 71910
   0.00 reads/s, 0.10 creates/s, 0.00 writes/s
   Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
   Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
   LRU len: 3242, unzip_LRU len: 0
   I/O sum[0]:cur[0], unzip sum[0]:cur[0]
   --------------
   ROW OPERATIONS
   --------------
   0 queries inside InnoDB, 0 queries in queue
   1 read views open inside InnoDB
   Main thread id 5080, state: sleeping
   Number of rows inserted 97780, updated 862074, deleted 219, read 468159566176
   1.55 inserts/s, 1.20 updates/s, 0.00 deletes/s, 3363648.47 reads/s
   ----------------------------
   END OF INNODB MONITOR OUTPUT
   ============================
  </code></pre>
  
  <p>So, Here you can see that transaction was waiting for a record lock, Also the last transaction with ID 78705977 is locking 1001945 rows which it should be as it is not updating that much rows. 
  Also Why it is taking time to fetch the rows like 16 seconds?</p>
  
  <p>Here is create Table of roomlogs:</p>
  
  <pre><code>CREATE TABLE `roomlogs` (
     `roomId` varchar(200) CHARACTER SET latin1 DEFAULT NULL,
     `netId` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
     `enterTime` datetime DEFAULT NULL,
     `exitTime` datetime DEFAULT NULL,
     `roomlogId` int(11) NOT NULL AUTO_INCREMENT,
     `status` int(11) DEFAULT '0',
     `hidden` int(11) DEFAULT '0',
     `checkinId` int(11) DEFAULT '-1',
     PRIMARY KEY (`roomlogId`),
     KEY `NETID` (`netId`,`status`,`enterTime`),
     KEY `RoomLogIndex` (`roomId`,`status`,`enterTime`)
   ) ENGINE=InnoDB AUTO_INCREMENT=1315726 DEFAULT CHARSET=utf8
  </code></pre>