Python3 - lock timeout has been exceeded; try restarting transaction "and only process in database

Question

Using Python I keep getting

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

when I try to insert into a specific table. However, when I try to paste directly from the MySQL console, it works fine. Looking in

 SHOW FULL PROCESSLIST 

No other active queries were found in the database. Since this is a dev database without connected applications, I cannot imagine any problems. However, it is serviced on the server hosting our production databases, so I prefer not to perform a reset if possible. Any tips on how to debug this?

Note If I view a query executed with a database using SHOW FULL PROCESSLIST, this ultimately fails with the above message, and then inserts it manually from the MySQL console, it works as expected.

EDIT Here is an example 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 of using a sentence 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() 

Answer 1

It turns out that another component created the connection and the commit () operator was missing.

Answer 2

I believe MySql uses Repeatable Read Isolation, which will hold the lock, for the ENTIRE transaction (otherwise, before committing, I believe). try using a different isolation value, such as "Read Committed"

To set a reading written in python, mysql is something like

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

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