Transaction Processing Using PHP and MySQL

Question

Hi, I'm trying to implement two-phase commit using PHP and MySQL, and coming up short.

The main block I've found is that I'm unable to store the MySQL connection resource in a place where I can find it again for the second phase. Is it possible to serialize a database handle?

Here's the case I'm trying to code for:

1. User sends data
2. Server starts a MySQL transaction and executes some queries based on the data it has received.
3. Server sends a file back to the user
4. When the user has successfully received the file, the server commits its transaction. Otherwise it rolls it back.

This seems to require two HTTP Request/Response cycles, so I need to be able to re-connect to the same database handle in the second request in order to commit the transaction. I've been failing at this part.

Any advice is welcome, even if it's "this is not possible in PHP"

Answer 1

Since php is Request / Response based the implementation of a persistent DB connection is not possbile, AFAIK.

You could try to work around this limitation using sort of a ticketing mechanism. Your steps would be:

1. User sends data
2. Server starts a MySQL transaction and executes some queries based on the data it has received, assigning a 'unique' ticket to that transaction.
3. Server sends a file and the ticket back to the user
4. When the user has successfully received the file and sent another request containing that ticket, the server commits its transaction. Otherwise it rolls it back.
5. refering to Cassy's comment: after a certain period of time all not commited TAs should be rolled back in order to prevent your db from beeing 'flooded' with old transactions

HTH

Answer 2

to answer KB22 and rojoca, the reason I need to do it this way is that the 'file' i'm referring to is actually a sqlite database that ends up as a data store on a mobile device.

The first request posts the updated sqlite database to the server, which attempts to merge in data from the sqlite tables; problems arise when the mobile device doesn't successfully receive a new sqlite database (one which reflects the mobile device's changes and any other new stuff from the web application), because it will then attempt to send the same (old) sqlite database to the web a second time, resulting in duplicate entries in the web tables for anything which was created on the mobile device.

So, the web needs to be sure that the device has the new database before committing the merge changes. Given the vagaries of networks, this only seems feasible if the device can send an explicit ACK after receiving the new sqlite database. And this is only possible if we make two requests (1. The sqlite database to merge; 2. the ACK of receipt of the new sqlite database on the device).

A thorny problem indeed, and it's useful information to find out that PHP can't manipulate database handles down to the necessary level.

[I also don't think I can use a transaction table because I need to return data to the device based on the 'real' web database tables. I think i'd run into issues with auto_increment fields if I didn't use the real tables]

Thanks for all your comments.