MySQL transaction in many PHP queries

Question

I would like to create an interface for manipulating accounts in a transactional way.

The database consists of an invoice table that contains billing information and an invoice_lines table that contains items for invoices. A website is a set of scripts that allow you to add, modify and delete invoices and their corresponding lines.

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

• Atomic When the user clicks "Save", either the entire invoice changes or the entire invoice does not change at all.
• Agreed . Application code already ensures consistency; lines cannot be added to non-existent accounts. Account IDs cannot be duplicated.
• Isolated . If the user is in the middle of the change set for the invoice, I would like to hide these changes from other users until the user types save.
• Durable . If the website dies, the data should be safe. This is already working.

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

From what I understand, you cannot START TRANSFER on one page of PHP and COMMIT on another page, because the connection is closed between the pages.

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

Answer 1

you do not want to have long transactions because it will limit concurrency. http://en.wikipedia.org/wiki/Command_pattern

Answer 2

An Internet translation for this type of processing is the use of session data or data stored on the page itself. Usually, it is done that after the completion of each web page, the data is saved in the session (or on the page itself) and in the place where all the pages were filled (via data entry) and "Process" (or "Save"), the data is converted into and the database form is saved - even with the relational aspect of the data, as you mentioned. There are many ways to do this, but I would say that most developers have an architecture similar to the one I mentioned (using session data or state on the page) to satisfy what you are talking about.

Here you will find a lot of advice on various 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 task easy. since Zend provides most of the MVC architecture and session management, and Doctrine provides the basic CRUD (create, retrieve, update, delete) that you are looking for, plus all other aspects (uniqueness, commit, rollback, etc.). Saving a connection to mysql can lead to timeouts and the lack of available connections.

Answer 3

Database operations are not really designed for this purpose - if you used them, you are likely to run into other problems.

But you also cannot use them, since each page request uses its own connection (potentially), therefore it cannot share a transaction with any others.

Save changes to the account in another place while the user edits them, then apply them when she clicks save; you can take this final step in the transaction (albeit fairly short-lived).

Long-term transactions are usually bad.

Answer 4

The solution should not open the transaction during the GET phase. Carry out all aspects of the transaction - START TRANSFER, processing and COMMIT - all during POST caused by the "Save" button.

Answer 5

A permanent connection can help you: http://php.net/manual/en/features.persistent-connections.php

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

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

Answer 6

Although there are good answers, I think I found good answers to your question, that I'm stuck too. I believe the best approach is to use a framework like Doctrine (O / R mapping) that somehow implements this approach. Here you have a link to what I'm talking about.