title: different mysql select statement but sleep long time
body: <p>I used flask+SQLAlchemy, and in teardown_appcontext I have called db.session.remove().But there are different select statement which slept long time until wait_timeout closed it, and connection pool can not recycle it with error 'sqlalchemy.exc.TimeoutError: QueuePool limit of size 1 overflow 0 reached, connection timed out'(For test I set pool_size 1).This problem is not definite, even to same sql.</p>
  <pre><code>Sql like:
  
  select * from some_table where type = 1 and online = 1;
  I use sqlalchemy orm, so the code like SomeTable.query.filter(...).first()
  </code></pre>
  <pre><code>mysql config:
  
  innodb
  REPEATABLE-READ
  wait_timeout=7200
  max_connection=500
  </code></pre>
  <pre><code>I have tried:
  
  1. I make a breakpoint in teardown_appcontext, and the dirty, deleted, new of db.session is null, which means I don't update anything.
  2. I don't think the cause is deadlock.Sometime just one select statement is sleep.Maybe other lock?
  </code></pre>
  <pre><code>More information:
  
  mysql&gt; SELECT * FROM information_schema.INNODB_TRX\G;
  *************************** 1. row ***************************
                      trx_id: 328279913521420
                   trx_state: RUNNING
                 trx_started: 2020-07-17 01:06:34
       trx_requested_lock_id: NULL
            trx_wait_started: NULL
                  trx_weight: 0
         trx_mysql_thread_id: 2230
                   trx_query: NULL
         trx_operation_state: NULL
           trx_tables_in_use: 0
           trx_tables_locked: 0
            trx_lock_structs: 0
       trx_lock_memory_bytes: 1136
             trx_rows_locked: 0
           trx_rows_modified: 0
     trx_concurrency_tickets: 0
         trx_isolation_level: REPEATABLE READ
           trx_unique_checks: 1
      trx_foreign_key_checks: 1
  trx_last_foreign_key_error: NULL
   trx_adaptive_hash_latched: 0
   trx_adaptive_hash_timeout: 0
            trx_is_read_only: 0
  trx_autocommit_non_locking: 0
  
  
  mysql&gt; show engine innodb status;
  | InnoDB |      |
  =====================================
  2020-07-17 11:58:02 0x2af011ecd700 INNODB MONITOR OUTPUT
  =====================================
  Per second averages calculated from the last 43 seconds
  -----------------
  BACKGROUND THREAD
  -----------------
  srv_master_thread loops: 6510 srv_active, 0 srv_shutdown, 3181472 srv_idle
  srv_master_thread log flush and writes: 3187982
  ----------
  SEMAPHORES
  ----------
  OS WAIT ARRAY INFO: reservation count 7090
  OS WAIT ARRAY INFO: signal count 6906
  RW-shared spins 0, rounds 13702, OS waits 6750
  RW-excl spins 0, rounds 9089, OS waits 86
  RW-sx spins 425, rounds 8010, OS waits 102
  Spin rounds per wait: 13702.00 RW-shared, 9089.00 RW-excl, 18.85 RW-sx
  ------------
  TRANSACTIONS
  ------------
  Trx id counter 284153
  Purge done for trx's n:o &lt; 284127 undo n:o &lt; 0 state: running but idle
  History list length 20
  LIST OF TRANSACTIONS FOR EACH SESSION:
  ---TRANSACTION 328679933520608, not started
  0 lock struct(s), heap size 1136, 0 row lock(s)
  ---TRANSACTION 328679933519696, not started
  0 lock struct(s), heap size 1136, 0 row lock(s)
  --------
  FILE I/O
  --------
  I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
  I/O thread 1 state: waiting for completed aio requests (log thread)
  I/O thread 2 state: waiting for completed aio requests (read thread)
  I/O thread 3 state: waiting for completed aio requests (read thread)
  I/O thread 4 state: waiting for completed aio requests (read thread)
  I/O thread 5 state: waiting for completed aio requests (read thread)
  I/O thread 6 state: waiting for completed aio requests (read thread)
  I/O thread 7 state: waiting for completed aio requests (read thread)
  I/O thread 8 state: waiting for completed aio requests (read thread)
  I/O thread 9 state: waiting for completed aio requests (read thread)
  I/O thread 10 state: waiting for completed aio requests (read thread)
  I/O thread 11 state: waiting for completed aio requests (read thread)
  I/O thread 12 state: waiting for completed aio requests (read thread)
  I/O thread 13 state: waiting for completed aio requests (read thread)
  I/O thread 14 state: waiting for completed aio requests (read thread)
  I/O thread 15 state: waiting for completed aio requests (read thread)
  I/O thread 16 state: waiting for completed aio requests (read thread)
  I/O thread 17 state: waiting for completed aio requests (read thread)
  I/O thread 18 state: waiting for completed aio requests (read thread)
  I/O thread 19 state: waiting for completed aio requests (read thread)
  I/O thread 20 state: waiting for completed aio requests (read thread)
  I/O thread 21 state: waiting for completed aio requests (read thread)
  I/O thread 22 state: waiting for completed aio requests (read thread)
  I/O thread 23 state: waiting for completed aio requests (read thread)
  I/O thread 24 state: waiting for completed aio requests (read thread)
  I/O thread 25 state: waiting for completed aio requests (read thread)
  I/O thread 26 state: waiting for completed aio requests (read thread)
  I/O thread 27 state: waiting for completed aio requests (read thread)
  I/O thread 28 state: waiting for completed aio requests (read thread)
  I/O thread 29 state: waiting for completed aio requests (read thread)
  I/O thread 30 state: waiting for completed aio requests (read thread)
  I/O thread 31 state: waiting for completed aio requests (read thread)
  I/O thread 32 state: waiting for completed aio requests (read thread)
  I/O thread 33 state: waiting for completed aio requests (read thread)
  I/O thread 34 state: waiting for completed aio requests (read thread)
  I/O thread 35 state: waiting for completed aio requests (read thread)
  I/O thread 36 state: waiting for completed aio requests (read thread)
  I/O thread 37 state: waiting for completed aio requests (read thread)
  I/O thread 38 state: waiting for completed aio requests (read thread)
  I/O thread 39 state: waiting for completed aio requests (read thread)
  I/O thread 40 state: waiting for completed aio requests (read thread)
  I/O thread 41 state: waiting for completed aio requests (read thread)
  I/O thread 42 state: waiting for completed aio requests (write thread)
  I/O thread 43 state: waiting for completed aio requests (write thread)
  I/O thread 44 state: waiting for completed aio requests (write thread)
  I/O thread 45 state: waiting for completed aio requests (write thread)
  I/O thread 46 state: waiting for completed aio requests (write thread)
  I/O thread 47 state: waiting for completed aio requests (write thread)
  I/O thread 48 state: waiting for completed aio requests (write thread)
  I/O thread 49 state: waiting for completed aio requests (write thread)
  I/O thread 50 state: waiting for completed aio requests (write thread)
  I/O thread 51 state: waiting for completed aio requests (write thread)
  I/O thread 52 state: waiting for completed aio requests (write thread)
  I/O thread 53 state: waiting for completed aio requests (write thread)
  I/O thread 54 state: waiting for completed aio requests (write thread)
  I/O thread 55 state: waiting for completed aio requests (write thread)
  I/O thread 56 state: waiting for completed aio requests (write thread)
  I/O thread 57 state: waiting for completed aio requests (write thread)
  I/O thread 58 state: waiting for completed aio requests (write thread)
  I/O thread 59 state: waiting for completed aio requests (write thread)
  I/O thread 60 state: waiting for completed aio requests (write thread)
  I/O thread 61 state: waiting for completed aio requests (write thread)
  I/O thread 62 state: waiting for completed aio requests (write thread)
  I/O thread 63 state: waiting for completed aio requests (write thread)
  I/O thread 64 state: waiting for completed aio requests (write thread)
  I/O thread 65 state: waiting for completed aio requests (write thread)
  I/O thread 66 state: waiting for completed aio requests (write thread)
  I/O thread 67 state: waiting for completed aio requests (write thread)
  I/O thread 68 state: waiting for completed aio requests (write thread)
  I/O thread 69 state: waiting for completed aio requests (write thread)
  I/O thread 70 state: waiting for completed aio requests (write thread)
  I/O thread 71 state: waiting for completed aio requests (write thread)
  I/O thread 72 state: waiting for completed aio requests (write thread)
  I/O thread 73 state: waiting for completed aio requests (write thread)
  I/O thread 74 state: waiting for completed aio requests (write thread)
  I/O thread 75 state: waiting for completed aio requests (write thread)
  I/O thread 76 state: waiting for completed aio requests (write thread)
  I/O thread 77 state: waiting for completed aio requests (write thread)
  I/O thread 78 state: waiting for completed aio requests (write thread)
  I/O thread 79 state: waiting for completed aio requests (write thread)
  I/O thread 80 state: waiting for completed aio requests (write thread)
  I/O thread 81 state: waiting for completed aio requests (write thread)
  Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,
   ibuf aio reads:, log i/o's:, sync i/o's:
  Pending flushes (fsync) log: 0; buffer pool: 0
  1375 OS file reads, 71241 OS file writes, 50288 OS fsyncs
  0.00 reads/s, 0 avg bytes/read, 0.07 writes/s, 0.00 fsyncs/s
  -------------------------------------
  INSERT BUFFER AND ADAPTIVE HASH INDEX
  -------------------------------------
  Ibuf: size 1, free list len 0, seg size 2, 6 merges
  merged operations:
   insert 0, delete mark 0, delete 0
  discarded operations:
   insert 0, delete mark 0, delete 0
  Hash table size 1182691, node heap has 1 buffer(s)
  Hash table size 1182691, node heap has 1 buffer(s)
  Hash table size 1182691, node heap has 1 buffer(s)
  Hash table size 1182691, node heap has 2 buffer(s)
  Hash table size 1182691, node heap has 2 buffer(s)
  Hash table size 1182691, node heap has 1 buffer(s)
  Hash table size 1182691, node heap has 2 buffer(s)
  Hash table size 1182691, node heap has 2 buffer(s)
  0.00 hash searches/s, 0.00 non-hash searches/s
  ---
  LOG
  ---
  Log sequence number 120244771
  Log flushed up to   120244771
  Pages flushed up to 120244771
  Last checkpoint at  120244762
  0 pending log flushes, 0 pending chkp writes
  22110 log i/o's done, 0.00 log i/o's/second
  ----------------------
  BUFFER POOL AND MEMORY
  ----------------------
  Total large memory allocated 4397727744
  Dictionary memory allocated 1539743
  Buffer pool size   262112
  Free buffers       259117
  Database pages     2983
  Old database pages 0
  Modified db pages  0
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 0, not young 0
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 1149, created 1844, written 35803
  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: 2983, unzip_LRU len: 0
  I/O sum[24]:cur[0], unzip sum[0]:cur[0]
  ----------------------
  INDIVIDUAL BUFFER POOL INFO
  ----------------------
  ---BUFFER POOL 0
  Buffer pool size   32764
  Free buffers       32474
  Database pages     288
  Old database pages 0
  Modified db pages  0
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 0, not young 0
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 124, created 164, written 6054
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  No buffer pool page gets since the last printout
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 288, unzip_LRU len: 0
  I/O sum[3]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 1
  Buffer pool size   32764
  Free buffers       32361
  Database pages     402
  Old database pages 0
  Modified db pages  0
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 0, not young 0
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 111, created 291, written 3812
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  No buffer pool page gets since the last printout
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 402, unzip_LRU len: 0
  I/O sum[3]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 2
  Buffer pool size   32764
  Free buffers       32422
  Database pages     340
  Old database pages 0
  Modified db pages  0
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 0, not young 0
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 87, created 253, written 640
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  No buffer pool page gets since the last printout
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 340, unzip_LRU len: 0
  I/O sum[3]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 3
  Buffer pool size   32764
  Free buffers       32367
  Database pages     396
  Old database pages 0
  Modified db pages  0
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 0, not young 0
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 204, created 192, written 7007
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  No buffer pool page gets since the last printout
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 396, unzip_LRU len: 0
  I/O sum[3]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 4
  Buffer pool size   32764
  Free buffers       32353
  Database pages     409
  Old database pages 0
  Modified db pages  0
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 0, not young 0
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 203, created 206, written 3725
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  No buffer pool page gets since the last printout
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 409, unzip_LRU len: 0
  I/O sum[3]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 5
  Buffer pool size   32764
  Free buffers       32319
  Database pages     444
  Old database pages 0
  Modified db pages  0
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 0, not young 0
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 161, created 283, written 2956
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  No buffer pool page gets since the last printout
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 444, unzip_LRU len: 0
  I/O sum[3]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 6
  Buffer pool size   32764
  Free buffers       32358
  Database pages     404
  Old database pages 0
  Modified db pages  0
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 0, not young 0
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 155, created 259, written 7777
  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: 404, unzip_LRU len: 0
  I/O sum[3]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 7
  Buffer pool size   32764
  Free buffers       32463
  Database pages     300
  Old database pages 0
  Modified db pages  0
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 0, not young 0
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 104, created 196, written 3832
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  No buffer pool page gets since the last printout
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 300, unzip_LRU len: 0
  I/O sum[3]:cur[0], unzip sum[0]:cur[0]
  --------------
  ROW OPERATIONS
  --------------
  0 queries inside InnoDB, 0 queries in queue
  1 read views open inside InnoDB
  Process ID=2960, Main thread ID=47210533119744, state: sleeping
  Number of rows inserted 40379, updated 4331, deleted 1131, read 2800340
  0.12 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.12 reads/s
  ----------------------------
  END OF INNODB MONITOR OUTPUT
  ============================
  
  </code></pre>