CODEWITHSUNDEEP
pythonmysql
Rui Martins
Rui Martins

Reputation: 3868

MySQL deadlock in python fetchall()

I have this code in PyCharm. I click 'play' to run the code and it works fine, but if I click twice or more, sometimes does not work, the program enter in deadlock at line:

f=cnx_cursor1.fetchall()

Code:

#!/usr/bin/env python2.7
# -*- coding: utf-8 -*-

import mysql.connector
import time
tik = time.time()

configDB = {
        'user': '****',
        'password': '****',
        'host': '****',
        'database': '****',
        'raise_on_warnings': True,
        'charset': 'utf8',
        'use_unicode': True,
        'collation': 'utf8_general_ci'
    }

cnx = mysql.connector.connect(**configDB)
cnx_cursor1 = cnx.cursor(dictionary=True)
sql1 = "SELECT E.*, " + \
                   "    ((E.statusNumber & 4278190080) >> 24) as s1, " + \
                   "    ((E.statusNumber & 0016711680) >> 16) as s2, " + \
                   "    ((E.statusNumber & 0000065280) >> 08) as s3, " + \
                   "    ((E.statusNumber & 0000000255) >> 00) as s4 " + \
                   " from EUser as E order by E.user_id, E.project;"
cnx_cursor1.execute(sql1)
print "After Execute:", "{:3.10f}".format(time.time()-tik), "s"
f=cnx_cursor1.fetchall()
print "After Fetch:", "{:3.10f}".format(time.time()-tik), "s"
ret = []
for l in f:
    print "user_id:", l["user_id"], "s1:", l["s1"], "s2:", l["s2"], "s3:", l["s3"], "s4:", l["s4"]
    if l["statusPhoto"] == 0:
        pass
    time.sleep(1)
cnx_cursor1.close()
cnx.close()
print "END:", "{:3.10f}".format(time.time()-tik), "s"

Anyone can help me?

This problem is the MySQL connection?

Thanks

Upvotes: 1

Views: 445

Answers (1)

Jason De Arte
Jason De Arte

Reputation: 555

There are several problems with FetchAll strategies (that I've run across)

  1. You have to wait for all the results from the query before it is sent across the wire to the client
  2. It can use a lot of memory. On both the server and the client
  3. There is little insight on the client into why things suddenly slowed down

Here's what I would do to troubleshoot this

  1. When the problem occurs, access your MySQL instance with your admin tools and look for active requests. If you see your request, it should provide insight into why it's taking so long. Maybe there's a row lock? Maybe you're out of memory? Maybe your client stopped responding? You'll never know until you look.
  2. Use FetchOne or FetchMany to get the rows in smaller batches that are easier to troubleshoot
    https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-fetchone.html
    https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-fetchmany.html
  3. Investigate setting a timeout on your request. Maybe it's set to infinite (or something more than you want to humanly wait for), maybe you can set it to a lower number to force a timeout and get better error information.

I hope this helps

Upvotes: 1

Related Questions