MySQL Transaction across many PHP Requests
mysql, php, transactions
Solution
you don't want to have long running transactions, because that'll limit concurrency. http://en.wikipedia.org/wiki/Command_pattern
Problem
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).