Does a commit on a Python MySQLdb cursor affect transactions in progress in another cursor?

Question

If I have multiple cursors (each in a different thread, for example) generated from the same DB connection, does a commit on one cursor (e.g. in Python: cursor.execute('COMMIT')) affect transactions in progress on another cursor within the same connection? If so, should I always only have one cursor per DB connection, in order to avoid cross-cursor issues like this? (Also, if you know, does this also apply to Google Cloud SQL?)

Thanks in advance for any advice.

score 4 · Accepted Answer · answered May 09 '12 at 08:45

4

probably. MySQLdb specifies a threadsafety- attribute of 1, which means you shouldn't share connections (and therefore cursors) on multiple threads without manual synchronisazion.

you should better use some form of connection pooling.

answered May 09 '12 at 08:45

mata

67,110
10
163
162

1

trying it out also confirmed it. – mata May 09 '12 at 08:54

score 1 · Answer 2 · edited May 23 '17 at 11:48

1

In Google Cloud SQL, you should have only one cursor at a time per DB connection. On the server side, there is a single datastructure per connection that keeps track of the current transaction/query. If you have multiple cursors, they will step on each other.

I suggest opening a connection at the beginning of every http request and closing it at the end. Here is some sample code for that.

edited May 23 '17 at 11:48

Community

1
1

answered May 14 '12 at 16:26

Ken Ashcraft

559
3
8

Thanks Ken. Your code example is helpful, but if sharing connections is dangerous across threads, should the wrapper even be reusing the same connection? – JJC May 16 '12 at 15:42

Does a commit on a Python MySQLdb cursor affect transactions in progress in another cursor?

2 Answers2