And if you're updating a row of data, you should first get a lock on that row so it doesn't change while you're in the middle of a transaction.

A very simple example follows:

BEGIN;

SELECT * FROM users WHERE user_id=1 FOR UPDATE;

--
--do some time-consuming work in here
--
--set up $calculation variable based on work
--

UPDATE users SET field1='$calcultation' WHERE user_id=1;

--send email update to user

COMMIT;

At first glance, you might be tempted to simply wrap the calculations and update inside a transaction, but to be 100% kosher, you should also include the email sending and any other page display code within your transaction.

Why? Well, what if another transaction ran after your update, but before you sent out the email? The email you send could be tainted by the other transaction and the recipient would receive 2 emails reflecting the second transaction.

Sound petty? Well, it might very well be, but we're talking about best practices here and you might as well know the best possible way to do it. Is the world going to end in the extremely rare occasion where the inaccurate email was sent? Probably not, unless you're talking about a financial application and/or a very high-traffic web site.

Here's a piece of code from the Linux Journal article that shows how to handle a transaction in the event it fails:

Here's some basic pseudocode for what's happening there:

1. BEGIN transaction;

2. Do first insert

	--check for errors and rollback if necessary

3. Do second insert

	--check for errors and rollback if necessary

4. Display results after update

5. COMMIT;

I haven't figured out the perfect solution yet to a problem that I've been running into lately. How do you handle the situation where you are inside of a transaction and you want to call a function that has a transaction coded in of its own? Postgres chokes when trying to do a transaction within a transaction, and it's a vexing problem really. Do you just avoid coding begin/commit blocks inside of function calls, or do you pass extra params to the function to skip the transaction code? Or am I missing something obvious?

Also, what do you do when you are mixing database code with file i/o or other code that cannot be rolled back? On SourceForge, we are using LDAP modules and LDAP does not have a rollback feature. So if something screws up, you may be able to rollback changes to the database, but not your LDAP directory, causing inconsistencies. Thoughts and opinions are welcome.

Now you should have the fundamental building blocks for working with transactions. Let me know if you have questions or comments - I'd be interested in knowing how you make use of transactional databases in your PHP applications.

--Tim