MySQL Transaction across many PHP Requests

I would like to create an interface for manipulating invoices in a transaction-like manner.

The database consists of an invoices table, which holds billing information, and an invoice_lines table, which holds line items for the invoices. The website is a set of scripts which allow the addition, modification, and removal of invoices and their corresponding lines.

The problem I have is this, I would like the ACID properties of the database to be reflected in the web application.

• Atomic: When the user hits save, either the entire invoice is modified or the entire invoice is not changed at all.
• Consistent: The application code already ensures consistency, lines cannot be added to non-existent invoices. Invoice IDs cannot be duplicated.
• Isolated: If a user is in the middle of a set of changes to an invoice, I would like to hide those changes from other users until the user clicks save.
• Durable: If the web site dies, the data should be safe. This already works.

If I were writing a desktop application, it would maintain a connection to the MySQL database at all times, allowing me to simply use the BEGIN TRANSACTION and COMMIT at the beginning and end of the edit.

From what I understand you cannot BEGIN TRANSACTION on one PHP page and COMMIT on a different page because the connection is closed between pages.

Is there a way to make this possible without extensions? From what I have found, only SQL Relay does this (but it is an extension).

you don't want to have long running transactions, because that'll limit concurrency. http://en.wikipedia.org/wiki/Command_pattern

The translation on the web for this type of processing is the use of session data or data stored in the page itself. Typically what is done is that after each web page is completed the data is stored in the session (or in the page itself) and at the point in which all of the pages have been completed (via data entry) and a "Process" (or "Save") button is hit, the data is converted into the database form and saved - even with the relational aspect of data like you mentioned. There are many ways to do this but I would say that most developers have an architecture similar to what I mentioned (using session data or state within the page) to satisfy what you are talking about.

You'll get much advice here on different architectures but I can say that the Zend Framework (http://framework.zend.com) and the use of Doctrine (http://www.doctrine-project.org/) make this fairy easy since Zend provides much of the MVC architecture and session management and Doctrine provides the basic CRUD (create, retrieve, update, delete) you are looking for - plus all of the other aspects (uniqueness, commit, rollback, etc). Keeping the connection open to mysql may cause timeouts and lack of available connections.

Database transactions aren't really intended for this purpose - if you did use them, you'd probably run into other problems.

But also you can't use them as each page request uses its own connection (potentially) so cannot share a transaction with any others.

Keep the modifications to the invoice somewhere else while the user is editing them, then apply them when she hits save; you can do this final apply step in a transaction (albeit quite a short-lived one).

Long-lived transactions are usually bad.

The solution is not to open the transaction during the GET phase. Do all aspects of the transaction—BEGIN TRANSACTION, processing, and COMMIT—all during the POST triggered by the "save" button.

Persistent connections may help you: http://php.net/manual/en/features.persistent-connections.php

Another is that when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does.

But I recommend you to find another approach to the problem. For example: create a cache table. When you need to "commit", transfer the records from the cache table to the "real" tables.

Altough there are some good answers, I think that found some good responses to your question, that I was stuck with also. I think the best approach is using a framework like Doctrine (O/R mapping) that has this kind of approach somehow implemented. Here you have a link to what I'm talking about.