gevent + sqlalchemy + pymysql/mysqlconnector

db.sql

mysql> SELECT * FROM sa_test.table_b WHERE a_id not in (SELECT id FROM table_a) ORDER BY a_id DESC;
+-----+------+------+
| id  | a_id | data |
+-----+------+------+
| 418 |  870 | b    |
| 416 |  862 | b    |
| 412 |  857 | b    |
| 410 |  848 | b    |
| 406 |  847 | b    |
| 403 |  835 | b    |
| 391 |  810 | b    |
| 387 |  800 | b    |
| 381 |  791 | b    |
| 376 |  782 | b    |
| 370 |  773 | b    |
| 365 |  766 | b    |
| 367 |  762 | b    |
| 361 |  757 | b    |
| 355 |  749 | b    |
| 350 |  740 | b    |
| 347 |  733 | b    |
| 343 |  723 | b    |
| 338 |  718 | b    |
| 334 |  710 | b    |
| 328 |  699 | b    |
| 325 |  694 | b    |
| 322 |  687 | b    |
| 321 |  682 | b    |
| 316 |  672 | b    |
| 310 |  663 | b    |
| 303 |  651 | b    |
| 302 |  642 | b    |
| 298 |  641 | b    |
| 295 |  637 | b    |
| 291 |  622 | b    |
| 286 |  612 | b    |
| 287 |  611 | b    |
| 278 |  599 | b    |
| 274 |  591 | b    |
| 271 |  584 | b    |
| 265 |  575 | b    |
| 266 |  574 | b    |
| 257 |  558 | b    |
| 251 |  539 | b    |
| 243 |  533 | b    |
| 239 |  519 | b    |
| 238 |  516 | b    |
| 231 |  508 | b    |
| 229 |  502 | b    |
| 228 |  495 | b    |
| 226 |  492 | b    |
| 224 |  482 | b    |
| 220 |  481 | b    |
| 219 |  480 | b    |
| 218 |  472 | b    |
| 217 |  466 | b    |
| 215 |  455 | b    |
| 209 |  451 | b    |
| 208 |  448 | b    |
| 206 |  442 | b    |
| 199 |  430 | b    |
| 200 |  429 | b    |
| 202 |  423 | b    |
| 197 |  421 | b    |
| 195 |  420 | b    |
| 187 |  400 | b    |
| 184 |  397 | b    |
| 384 |  383 | b    |
| 178 |  383 | b    |
| 383 |  380 | b    |
| 180 |  380 | b    |
| 380 |  378 | b    |
| 378 |  375 | b    |
| 375 |  374 | b    |
| 374 |  373 | b    |
| 170 |  358 | b    |
| 169 |  357 | b    |
| 166 |  351 | b    |
| 161 |  341 | b    |
| 341 |  340 | b    |
| 159 |  339 | b    |
| 340 |  339 | b    |
| 339 |  336 | b    |
| 155 |  328 | b    |
| 153 |  323 | b    |
| 232 |  230 | b    |
| 107 |  230 | b    |
| 223 |  222 | b    |
| 102 |  216 | b    |
| 100 |  213 | b    |
|  95 |  202 | b    |
|  91 |  198 | b    |
|  86 |  193 | b    |
| 193 |  192 | b    |
|  87 |  192 | b    |
|  85 |  189 | b    |
|  82 |  184 | b    |
|  83 |  183 | b    |
|  79 |  168 | b    |
|  80 |  167 | b    |
|  70 |  160 | b    |
|  67 |  146 | b    |
|  63 |  143 | b    |
|  62 |  137 | b    |
|  61 |  127 | b    |
|  53 |  125 | b    |
|  54 |  124 | b    |
| 121 |  120 | b    |
|  51 |  113 | b    |
|  42 |   96 | b    |
|  98 |   96 | b    |
|  41 |   92 | b    |
|  40 |   88 | b    |
|  36 |   87 | b    |
|  35 |   86 | b    |
|  77 |   75 | b    |
|  33 |   75 | b    |
|  75 |   74 | b    |
|  74 |   72 | b    |
|  71 |   68 | b    |
|  29 |   63 | b    |
|  60 |   59 | b    |
|  25 |   55 | b    |
|  26 |   54 | b    |
|  24 |   49 | b    |
|  22 |   41 | b    |
|  10 |   20 | b    |
|  17 |   16 | b    |                                                                                                                                                                             [834/1304]
|   6 |   16 | b    |
|  15 |   14 | b    |
|   5 |   14 | b    |
|  20 |    9 | b    |
|   9 |    8 | b    |
|   8 |    7 | b    |
|   7 |    6 | b    |
| 320 |    0 | b    |
| 333 |    0 | b    |
| 323 |    0 | b    |
| 324 |    0 | b    |
| 332 |    0 | b    |
| 331 |    0 | b    |
| 330 |    0 | b    |
|  37 |    0 | b    |
| 326 |    0 | b    |
|  38 |    0 | b    |
| 327 |    0 | b    |
| 314 |    0 | b    |
|  43 |    0 | b    |
|  48 |    0 | b    |
| 299 |    0 | b    |
|  49 |    0 | b    |
| 296 |    0 | b    |
|  52 |    0 | b    |
| 294 |    0 | b    |
|  55 |    0 | b    |
| 290 |    0 | b    |
| 289 |    0 | b    |
| 301 |    0 | b    |
|  47 |    0 | b    |
|  46 |    0 | b    |
| 312 |    0 | b    |
| 311 |    0 | b    |
|  44 |    0 | b    |
| 309 |    0 | b    |
| 308 |    0 | b    |
| 307 |    0 | b    |
|  45 |    0 | b    |
| 305 |    0 | b    |
| 304 |    0 | b    |
|  56 |    0 | b    |
|   2 |    0 | b    |
| 389 |    0 | b    |
| 386 |    0 | b    |
| 385 |    0 | b    |
|  11 |    0 | b    |
|   1 |    0 | b    |
| 227 |    0 | b    |
| 103 |    0 | b    |
| 105 |    0 | b    |
| 106 |    0 | b    |
| 222 |    0 | b    |
|  92 |    0 | b    |
|  90 |    0 | b    |
| 236 |    0 | b    |
| 247 |    0 | b    |
|  81 |    0 | b    |
| 244 |    0 | b    |
|  84 |    0 | b    |
| 242 |    0 | b    |
| 241 |    0 | b    |
| 240 |    0 | b    |
|  88 |    0 | b    |
|  89 |    0 | b    |
| 237 |    0 | b    |
| 221 |    0 | b    |
+-----+------+------+

poc.py

# -*- coding: utf-8 -*-

import gevent.monkey
gevent.monkey.patch_all()

import logging
from meepo.logutils import setup_logger
setup_logger("DEBUG")

logger = logging.getLogger("gevent_fuck")

import random
import sys

import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base

engine = sa.create_engine('mysql+pymysql://root@vm.lxyu.net/')
PREPARE_SQL = """
DROP DATABASE IF EXISTS sa_test;
CREATE DATABASE sa_test;
USE sa_test;

DROP TABLE IF EXISTS sa_test.table_a;
CREATE TABLE sa_test.table_a (
    id INT NOT NULL AUTO_INCREMENT,
    data VARCHAR (256) NOT NULL,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS sa_test.table_b;
CREATE TABLE sa_test.table_b (
    id INT NOT NULL AUTO_INCREMENT,
    a_id INT NOT NULL,
    data VARCHAR (256) NOT NULL,
    PRIMARY KEY (id)
);
"""
engine.execute(PREPARE_SQL)
engine.dispose()
del engine

print("Table prepared...")

engine = sa.create_engine('mysql+pymysql://root@vm.lxyu.net/sa_test', pool_size=50, max_overflow=0)  # noqa
# engine = sa.create_engine('mysql+mysqlconnector://root@vm.lxyu.net/sa_test', pool_size=50, max_overflow=0)  # noqa

Base = declarative_base()
Session = scoped_session(sessionmaker(bind=engine))


class TableA(Base):
    __tablename__ = "table_a"

    id = sa.Column(sa.Integer, primary_key=True)
    data = sa.Column(sa.String)


class TableB(Base):
    __tablename__ = "table_b"

    id = sa.Column(sa.Integer, primary_key=True)
    a_id = sa.Column(sa.Integer)
    data = sa.Column(sa.String)


def transaction_kill_worker():
    while True:
        try:
            with gevent.Timeout(0.1):
                session = Session()
                s_a = TableA(data="a")
                session.add(s_a)
                session.flush()
                s_b = TableB(a_id=s_a.id, data="b")
                session.add(s_b)
                gevent.sleep(random.random() * 0.2)

                try:
                    session.commit()
                except Exception:
                    # logger.exception(e)
                    session.rollback()
                    sys.stdout.write("$")
        except gevent.Timeout:
            sys.stdout.write("#")
        except Exception:
            # logger.exception(e)
            sys.stdout.write("@")
        else:
            sys.stdout.write(".")
        finally:
            try:
                Session().close()
            except:
                pass


def main():
    for i in range(50):
        gevent.spawn(transaction_kill_worker)

    gevent.sleep(3)

    while True:
        gevent.sleep(5)
        print("\n" + engine.pool.status() + '\n')


if __name__ == "__main__":
    main()

感觉还是由连接共享造成的，加入下面这段代码后，没有那种现象

@event.listens_for(Pool, "checkout")
def ping_connection(dbapi_conn, conn_record, conn_proxy):
    cursor = dbapi_conn.cursor()
    try:
        cursor.execute("SELECT 1")
    except:
        raise exc.DisconnectionError()
    cursor.close()