CODEWITHSUNDEEP
pythonmysqlcursormigration
Dap
Dap

Reputation: 2359

Why does sql hang up when opening multiple cursors in python?

Im trying to take date from one database and insert into another. My function hangs up after attempting to update and results in an error 'Lock wait timeout exceeded; try restarting transaction'. Is this because of multiple cursors being open and how might I work around it. 

soc = MySQLdb.connect(foobarparams)

db = MySQLdb.connect(foobarparams)

def getallinfo(self):
    cursor = soc.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute('SELECT * FROM firm_contributor')
    result = cursor.fetchall()

    cursor2 = db.cursor(MySQLdb.cursors.DictCursor)


    for i in result:
        user = i['author_id']

        query = 'SELECT * FROM ed_users WHERE id =' + str(user)
        cursor2.execute(query)
        result = cursor2.fetchall()

        display_name = result[0]['display_name']
        email_address = result[0]['user_email']
        registered_date = result[0]['user_registered']

        update = "UPDATE firm_contributor SET display_name='%s', email_address='%s', registered_date='%s' WHERE author_id=%s" % (display_name, email_address, registered_date, user)
        print update
        cursor.execute(update)
        cursor.commit()

    cursor.close()
    cursor2.close()

Upvotes: 0

Views: 1973

Answers (1)

Simon Callan
Simon Callan

Reputation: 3130

Looking at the code related to cursor, I notice that you are doing something very similar to:

cursor = soc.cursor(MySQLdb.cursors.DictCursor)
cursor.execute('SELECT * FROM firm_contributor')
result = cursor.fetchall()

update = "UPDATE firm_contributor SET display_name='...'"
cursor.execute(update)
cursor.commit()

It seems possible that the select statement is getting a read lock on the firm_contributor table, and then the update is attempting to get a write-lock on the table, and hitting problems because it already has a read-lock, so the write-lock times out.

What happens if you add either:

cursor.commit()

or

cursor.close()
cursor = soc.cursor(MySQLdb.cursors.DictCursor)

after the call to fetchall()?

This may result in the read-lock being released, and the write-lock working.

Upvotes: 1

Related Questions