sqlalchemy + mysql deadlocks

Question

I insert rows with random data into mysql database. After inserting some rows, greenlet hangs on connecting. Statistics printing greenlet keeps running

This happens with any number of workers (including one), both with mysql-connector and mysqldb drivers. sqlite works fine.

This has no effect (as i understand it is already fixed in new gevent)

def patch():
    from gevent import monkey
    monkey.patch_all()

    # fix https://bugs.launchpad.net/myconnpy/+bug/712037
    from mysql.connector.connection import MySQLConnection
    MySQLConnection.get_characterset_info = MySQLConnection.get_charset
patch()

from sqlalchemy import MetaData, Table, Column, Integer, String, create_engine
from gevent import spawn, sleep
from random import randrange
from time import time

class Stats(object):
    def __init__(self):
        self.inserts, self.faults = 0, 0

    def run(self):
        while True:
            sleep(1)
            print "%d %d %d" % (time(), self.inserts, self.faults)
            self.inserts, self.faults = 0, 0

class Victim(object):
    metadata = MetaData()
    Entry = Table(
        'entry', metadata,
        Column('id', Integer, primary_key=True),
        Column('junk', String(128), unique=True)
    )

    def __init__(self, cs, stats):
        self.e = create_engine(cs)
        self.metadata.drop_all(self.e)
        self.metadata.create_all(self.e)
        self.stats = stats

    def add(self, junk, i):
        print i, 'connecting'
        c = self.e.connect()
        print i, 'connected'
        t = c.begin()
        try:
            q = self.Entry.insert().values(junk=junk)
            c.execute(q)
            t.commit()
            self.stats.inserts += 1
        except Exception as e:
            print i, 'EXCEPTION: ', e
            t.rollback()
            self.stats.faults += 1
        print i, 'done'

def flood(victim, i):
    a, z, l = ord('a'), ord('z')+1, 100
    while True:
        victim.add(''.join(chr(randrange(a, z)) for _ in xrange(l)), i)
        sleep(0)

def main(n_threads, cs):
    stats = Stats()
    victim = Victim(cs, stats)
    threads = [ spawn(flood, victim, i) for i in xrange(n_threads) ]
    threads.append(spawn(stats.run))
    [t.join() for t in threads]

#main(2, 'mysql://root:root@localhost/junk')
main(1, 'mysql+mysqlconnector://root:root@localhost/junk')

What is happening?

---

Retested, error persists without gevent, probably something with server configuration

Answer 1

I just forgot to release used connections, so connections where never checked-in back into pool. ...

def add(self, junk, i):
    print i, 'connecting'
    c = self.e.connect()
    ...
    try:
        ...
    except Exception as e:
        ...
    finally:
        c.close() # <-- this returns conenction into pool
    print i, 'done'