Transaction processing using PHP and MySQL
I am trying to implement two phase commit using PHP and MySQL, and it doesn't fit.
The main block I found is that I cannot save the MySQL connection resource in a location where I can find it again for the second step. Can a database handle be serialized?
In this case, I am trying to generate code for:
- User submits data
- The server starts a MySQL transaction and executes some queries based on the received data.
- The server sends the file back to the user
- When the user successfully received the file, the server commits a transaction. Otherwise, it rolls back.
It seems like two HTTP request / response cycles are required, so I need to be able to reconnect to one database descriptor in the second request in order to commit the transaction. I have failed in this part.
Any advice is appreciated, even if it's "not possible in PHP"
source to share
Since php is a request / response, implementing an uninterrupted DB connection is not difficult, AFAIK.
You can try to get around this limitation by using some sort of ticketing mechanism. Your steps:
- User submits data
- The server starts a MySQL transaction and executes some queries based on the received data, assigning a "unique" ticket for that transaction.
- The server sends the file and ticket back to the user
- When the user has successfully received the file and sent another request containing this ticket, the server commits a transaction. Otherwise, it rolls back.
- referring to Cassy's comment: After a certain period of time, all non-working TAs should be rolled back so that your db is not "flooded" with old transactions.
NTN
source to share
to answer KB22 and rojoca, the reason I need to do this is because the "file" I am linking to is actually a sqlite database that ends up as a data store on a mobile device.
The first query sends an updated sqlite database to the server, which tries to merge data from the sqlite tables; problems occur when the mobile device does not receive a new sqlite database (which reflects the mobile device changes and any other new stuff from the web app) as it will then try to send the same (old) sqlite database to the web a second time, resulting in to duplicate records in web tables for everything that was created on a mobile device.
Thus, the website must make sure the device has a new database before committing to the merge changes. Given the vagaries of networking, this only seems possible if the device can send an explicit ACK after receiving a new sqlite database. And this is only possible if we make two queries (1. sqlite database to join, 2. ACK to get a new sqlite database on the device).
This is actually a problem, and the useful information is that PHP cannot manipulate database descriptors to the required level.
[I also don't think I can use a transaction table because I need to return data to the device based on "real" web database tables. I think I am having problems with auto_increment fields if I weren't using real tables]
Thanks for your comments.
source to share