multithreaded db access

[dale patterson]

I have a multithreaded python app where multiple threads access a db through the MySQLdb API. (All the tables are InnoDB if that helps). While reading the MySQLdb docs, I saw that it is not threadsafe in terms of sharing a common connection in different threads. So, when each thread starts, it make a connection object and when it exits it closes the connection. The problem is that changes made by one thread are not seen by the others. For example, I have one thread that will delete all records in all tables associated with a common job (I am using commits). However, a second thread will still "see" the deleted job and can retrieve all related data from every table. Now at the same time, I can run mysql from the command line and verify that the deleted data is in fact deleted. I am wondering how this is possible and if it is related to the db API, the db itself or to python. At present, I have a workaround where each time a thread needs to perform a query it makes a new connection and this works but I don't like having to make a new connection all the time. Any ideas?