Best Practices: Database Transactions - Page 2

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:

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.