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

Question

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()

can you show your python query? – Busturdust Nov 12 '15 at 14:57 — Busturdust, Nov 12 '15 at 14:57
I added an example query – user2694306 Nov 12 '15 at 15:09 — user2694306, Nov 12 '15 at 15:09
Do you work with multiple threads.? – Sony Mathew Nov 18 '15 at 15:14 — Sony Mathew, Nov 18 '15 at 15:14
Nope, it's single threaded. – user2694306 Nov 18 '15 at 17:22 — user2694306, Nov 18 '15 at 17:22

score 5 · Answer 1 · answered Nov 12 '15 at 16:38

5

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

answered Nov 12 '15 at 16:38

user2694306

3,832
10
47
95

score 0 · Answer 2 · edited Jan 06 '18 at 10:00

0

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/

edited Jan 06 '18 at 10:00

Vlad Mihalcea

142,745
71
566
911

answered Nov 12 '15 at 15:01

Busturdust

2,447
24
41

I tried adding this before my actual insert statement, but the problem still persists. – user2694306 Nov 12 '15 at 15:09
did you commit the change before inserting? Also, are you commiting your insert before continuing? – Busturdust Nov 12 '15 at 15:11
Yea, every insert is followed by a commit and I also tried committing the statement before my insert. I added an example to my original question – user2694306 Nov 12 '15 at 15:18
take a gander over here, maybe it can help http://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im – Busturdust Nov 12 '15 at 15:23
I noticed an error in my code, Try using commited instead of uncommited – Busturdust Nov 12 '15 at 15:24

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

2 Answers2