Check out Scrivo

Do you want to try out Scrivo? Then here's a demo for you that does not just get your feet wet but lets you plunge right in.

Contact us

For more information, please contact us. We're happy to help you out!

Next Sep 11 Previous

Item 547198

In the stand-up the architecture for a new Web shop is discussed. There are some arguments given for and against a transactional database:

A: When transactions are used two clients cannot access the database at the same time.
B: By using transactions we can ensure the integrity of our database.
C: The PHP code becomes much more complex because we'll need to take failed transactions into account.
D: It may be some extra work but we don't have to pay for a payment provider.

Which of the given arguments do you need to correct.


Well, you need to do some work here. Luckily the single argument for using transactions was given: they help you to maintain the integrity of the database.

When you write some PHP script that manipulates data in the database you'll often need more than one INSERT, UPDATE or DELETE query to get the job done. And most of the time it is important that these statements are all executed otherwise the database will be left in some sort of inconsistent state: imagine an order with no account referencing it or a missing double entry in an accounting system. Transactions give you the means to group these transaction and execute them as if they were one statement. And if one of the statements in the transaction fails the transaction can be rolled back to restore the original state of the database.

You can expect to write much simpler code, not more complex code. Sure, you have to take failed transactions into account, but the reasons for failing transactions are not the transactions themselves. The same things (and even more) can go wrong if you are not using transaction and you'll have to deal with those exceptions anyway. And speaking of exceptions, what could be more simple than a big try catch block that encloses your update procedure and executes a COMMIT at the end or a ROLLBACK when an exception occurs. Try to work a out a rollback procedure yourself and the contribution of transactions to the clarity of the code will become evident (and consider that your rollback procedure can fail too).

Besides data integrity the other big database issue is concurrency. Databases are designed to handle many multiple requests at the same time and by using transactions you are not going to 'turn it off'.

And concerning answer D: I sincerely hope for you not a fellow developper but some account manager has contributed this to the discussion.