CODEWITHSUNDEEP
pythonmysqlpython-3.xpymysql
user2694306
user2694306

Reputation: 4050

Python3 - 'Lock wait timeout exceeded; try restarting transaction' and only process on the database

Using Python I am consistently getting an

(1205, 'Lock wait timeout exceeded; try restarting transaction')

error whenever I try to insert into a particular table. However, when I try to insert directly from the MySQL console, it works just fine. Looking into the 

SHOW FULL PROCESSLIST

on the database reveals no other active queries. As this is a dev database with no live applications attached I can't envision any problem. However, it is being served on a server which hosts our production databases, so I strongly prefer not to do resets if possible. Any advice on how to debug this?

Note If I view the query being run against the database using SHOW FULL PROCESSLIST, that eventually fails with the above message, and then insert it manually from the MySQL console, it works as expected.

EDIT Here is an example of the query:

INSERT INTO deals (user_ID, store_ID, storeChain_ID, title, dealSaving, 
dealDisclaimer, restriction, dealImage, dealURL, submit_ID, userProvider_ID, 
createDate, createDateTime, expirationDate, expirationDateTime, ZIP, STATE, 
city, businessType, DealType_ID, IP, rating, popular, dealSearchData, tag, 
submitName, provider_dealID)
VALUES (NULL,
        2651049,
        NULL,
        'Toronto East Community Arts Program',
        'Three-Week Photography Workshop',
        NULL,
        NULL,
        'https://a1.image.net/imgs/8423535b-bd3b-4e1e-afee-ab2869970a4c/700_q60.jpg',
        'https://www.exmples.com/deals/1336374',
        111,
        1,
        '2015-11-12',
        '2015-11-12 10:01:58.282826',
        '2015-11-17 09:59:59',
        '2015-11-17 23:59:00',
        'M4M 1K7',
        'ON',
        'Toronto',
        NULL,
        '1',
        '127.0.0.1',
        0,
        144565,
        'Three-Week Photography Workshop',
        'Photography Class',
        'Partner',
        1336374)

Edit An example using the suggestion is as follows:

self.DB['master']['cursor'].execute("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
self.DB['master']['con'].commit()
self.DB['master']['cursor'].execute(dealsquery,data)
self.DB['master']['con'].commit()

Upvotes: 6

Views: 4401

Answers (2)

Busturdust
Busturdust

Reputation: 2485

I Believe MySql uses "Repeatable Read" isolation, which will hold a lock, for the ENTIRE transaction (aka until commit, i believe). try using a different isolation setting like "Read Committed"

To set read commited in python mysql is something like

cur = conn.cursor() cur.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED")

edit: a good introductory resource for isolation levels http://vladmihalcea.com/a-beginners-guide-to-database-locking-and-the-lost-update-phenomena/

Upvotes: 0

user2694306
user2694306

Reputation: 4050

It turns out, a different component was making a connection and missing the commit() statement.

Upvotes: 5

Related Questions