Hello! I'm having trouble making MySQL transactions work with App Engine. I'm posting it to this list since I imagine more people here are using App Engine than people on the App Engine list are using Cloud SQL, but please let me know if I should post elsewhere.
-- TL;DR of what I think its happening: When two App Engine requests in parallel use MySQLdb to connect to Google Cloud SQL (or another MySQL server), if the requests are coming from the same machine then they are not dealt with independently. In particular, locking a row in one request does not prevent the other request from modifying it.
Gist to reproduce:
1. Make a GAE app that connects to Cloud SQL.
2. Write code in two separate admin consoles of the app:
- One starts a transaction, locks and prints a row, then waits 10 seconds, then prints out the row before ending the transaction.
- The other updates the row that the first one locked.
3. Run the first console's code, then wait a few seconds and run the second console's code.
Expected result:
The second console waits for several seconds, then terminates (having updated the name).
The first console prints out the row with its original name, twice.
Actual result:
The second console terminates immediately, and updates the row.
The first console prints out the row, first with its original name and second with its updated name.
More detailed steps to reproduce:
1. Create a Google App Engine app that connects to a Google Cloud SQL database. Make sure the app has MySQLdb and an admin console[1] in its app.yaml.
2. Make a 'Products' table with a 'name' column and an 'id' column, and insert something with name 'BoringName' and id 1. [2]
3. Open up two separate browser tabs to the interactive console of the admin console, and enter the following code:
[Console 1]
import time
import my_db_lib
db = my_db_lib.GetConnection() # [3]
c = my_db_lib.GetCursor(db)
c.execute('START TRANSACTION')
c.execute('SELECT Name FROM Products WHERE ProductId=1 FOR UPDATE')
print c.fetchall()
time.sleep(10.0)
c.execute('SELECT Name FROM Products WHERE ProductId=1')
print c.fetchall()
db.commit()
[Console 2]
import my_db_lib
db = my_db_lib.GetConnection() # [3]
c = my_db_lib.GetCursor(db)
c.execute('UPDATE Products SET Name="ExcitingName" WHERE ProductId=1')
db.commit()
3. Run the first console's code, then wait a few seconds and run the second console's code.
Expected result:
The second console waits for several seconds, then terminates (having updated the name).
The first console prints out the row with its original name, twice.
Actual result:
The second console terminates immediately, having updated the row.
The first console prints:
({'Name': u'BoringName'},)
({'Name': u'ExcitingName'},)
Other things I've tried
1. The same thing, but on dev_appserver.py (the offline test server for App Engine). Result: The same unexpected result as occurs on the live App Engine servers. [4]
2. Running console 1 from App Engine, and the UPDATE statement from console 2 in Cloud Shell. Result: As expected, the UPDATE statement hangs for several seconds before completing, and console 1 prints 'BoringName' twice.
3. Running the statements from both consoles in separate Cloud Shells. Result: As expected, the UPDATE statement hangs until I commit the transaction.
4. Running the two consoles on different machines [5]. Result: Expected. Console 2 waits for console 1 to complete. Console 1 prints:
({'Name': u'BoringName'},)
({'Name': u'BoringName'},)
Hypothesis
Both live and offline App Engine have issues with transactions when both requests are coming from the same machine.
Live App Engine has no issues with transactions when the requests are coming from different machines.
I hypothesize that when two MySQLdb connections come in to Google Cloud SQL from the same machine in App Engine, they aren't treated as distinct connections by the database. This seems like a bug to me, but I'd love to hear if other people think my hypothesis is correct and if it's indeed a bug.
Thanks!
Bartholomew
Footnotes
[1] Admin console requires the following in app.yaml:
# Admin console.
- url: /some_url
script: google.appengine.ext.admin.application
login: admin
secure: always
[2] Some technical details: I'm using a first-generation CloudSQL instance, and the default InnoDB storage engine.
[3] My db lib just connects as follows:
return MySQLdb.connect(unix_socket='/cloudsql/my-project-name:db', user='root', db=my_db_name, charset='utf8')
And gets a cursor via db.cursor(MySQLdb.cursors.DictCursor).
[4] When on dev_appserver, my db lib connects as follows:
return MySQLdb.connect(host='localhost', user=user_name, passwd=super_secure_password, db=my_db_name, charset='utf8')
It connects to a standard MySQL server.
[5] I uploaded a different version of the app. It had no code differences, but App Engine runs different versions on different machines. So I ran my two consoles on:
https://version1-dot-my-test-app.appspot.com/a/db/interactive
https://version2-dot-my-test-app.appspot.com/a/db/interactive
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/eb09b7bb-e903-46f7-b0c0-ba76401f0992%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
No comments:
Post a Comment