Database transactions

Smartys · 2009-01-16 17:56:25

I have a question about transactions that has always bugged me and that I've never been able to find a good answer for.

Lets say I'm writing an application for a bank.

I'll focus on one page: an account summary/transfer page. At the top of the page, certain information is displayed about the user (name, current balance in all accounts, last login, etc). The main body of the page displays a breakdown of all the accounts controlled by the user. At the bottom of the page, you can choose to transfer money between accounts.

Now, lets assume that before any database activity happens, a transaction starts. At the end of the page, the transaction is committed. The data for the user that's displayed at the top is fetched before any transfer activity. Here's some pseudo code:

$db->start_transaction();

// get user data
$db->query(...);

if (isset($_POST['transfer']))
{
// validate transfer
}

// get individual account data
$db->query();

$db->commit();

So, how should I handle a user who, for instance, tries to transfer too much money (given that I want to display the error within the context of the existing page rather than on a separate error page)? Should I roll back the transaction? If so, what do I do with the rest of the page? Should I instead be doing as much validation as I can within the PHP so as to avoid rollbacks (ie: I make sure everything is valid using SELECTs before I use UPDATEs, which avoids having to use ROLLBACK)?

elbekko · 2009-01-16 18:04:12

The main point of transactions is to assure that there is never any incomplete data in the database. If there is an error in any of the queries, every data-changing query gets rolled back.

So yes, you should be doing as much validation as you'd do without transactions, they're merely a way of ensuring that either all or none of the data is there.

Dave · 2009-01-16 18:23:30

The encapsulation of a transaction should only need to be done, in this particular case, when the "do the transfer" button is pressed. By that time you should have validated all of the data and amounts and the only thing remaining would be to actually complete the transfer. Since the transfer itself is an atomic event if it fails for some reason it's very simple to roll back. "Industrial strength" SQL engines allow transactions to be encapsulated, atomic, for this very reason.

Smartys · 2009-01-16 19:18:13

Alright, I understand now: thanks guys

Forums

#1 2009-01-16 17:56:25

Database transactions

#2 2009-01-16 18:04:12

Re: Database transactions

#3 2009-01-16 18:23:30

Re: Database transactions

#4 2009-01-16 19:18:13

Re: Database transactions

Board footer